The Refs server table can be used for storing many different bits of information for use by your application.
Refs is an abbreviation for References. The swRefs4 library is where the various Refs related classes are contained.
Some of the possible uses of the Refs table are:
The primary use of Refs is for edit helper lists to suggest possible entries in a field (Mr., Mrs., Ms., Dr., Sir, Lord,...), or limit the possible entries in a field. (F - Female, M - Male, U - Unknown) In the these are called Refs Lookups.
The oRefs object class is the interface which you should use to access the Refs records. oRefs is instantiated by the main library Startup_Task task variable, refs. To get group/subgroup set of refs records from anywhere in your application you could do the following:
Do refs.$retLookupList(pRefsGroup,pRefsSubGroup) Returns LookupList
If you want to add your own custom methods to oRefs, or wish to modify the behavior of an existing oRef method:
The sRefs schema class lists all of the columns in the Refs table.
The Refs columns of interest are:
As stated previously, the most common use of the Refs table is for edit helper lists.
For example you might have a person table with as a PersonGender column. For consistent data entry you want to give the users 3 options (F - Female, M - Male, U - Unknown).
You don't want to create a special lookup table for Gender, which then requires you to join Person and Gender each time you want to include the person's gender in a list or report. You simply want to stamp M, F, or U in the PersonGender column.
You don't want to hard code the options of F,M,U because your application might be used in a company where English is not the primary language and F,M,U would not be the letters they would use for PersonGender.
The SQL Meta-Data Refs Lookup makes this very easy to implement.
In the auto configure edit window the PersonGender will appear as a lookup field and the user will be able to choose from the 3 options which you entered in the Refs table. If the user types ? in the field a droplist will appear with the following options:
F Female
M Male
U Unknown
Leave the SQL Meta-Data Mandatory checkbox unchecked if you only want to let users choose from a list of suggestions or type any value they want. For example in a NamePrefix field your might want to provide a lookup list that suggests (Mr. Ms. Mrs. Dr. Sir Lord,...) If Mandatory is not checked in the meta-data the user can pick one of the droplist name prefixes or enter their own value. When they leave the field the RefsDisplayText value they picked or other value they entered is what gets stored in the NamePrefix field.
The Refs Lookups are not meant to work as hidden foreign key child/parent table relationship. The Refs Lookups are data entry helpers. The column in the target table gets stamped with the actual refs value which the user can see and edit. Normally the actual Refs actual value is an abbreviation or ID which is meaningful to the users.
The problem with trying to make the refs lookups do "hidden things" behind the scenes is that without a true child/parent - fkey/pkey relationship something is sure to go wrong down the line. You will have a pile of records in a table that are loosely linked to a Refs record and with special code are depending on the loose connection to display the RefsDesc in your window and headed lists. Along comes an unsuspecting System Administrator and they delete the Refs record or change the 'Refs Value' and whamo your edit window, lookup list, and headed lists are dead in the water. The lose connection that was based on the Refs value is lost.
Refs Lookups vs. Child/Parent Table Relationships guidelines:
The alternate solution for #2 is a hard coded droplist or combo-box ... but it seems users always want to change the options, so the Refs lookups are much more flexible solution. They give you the options of droplist or combo-box style editing based on the meta-data Mandatory setting and you can give the user and/or sys admin access to adding/changing the lookup entries.
If it works for the situation - option #2 lets you reduce the number of tiny lookup tables in your database and the number of joins you have to do for lists and reports.Incremental counters are sometimes needed in your application. (eg. Purchase Order numbers, Invoice Numbers, Employee IDs, etc.)
The oRefs object supports incremental counters through the $retNextCounterNum method.
Let's say you have a PurchOrder table with a PONum field. In the method where the user starts a new purchase order you can get the next unique PO number from Refs using just one line of code:
Do refs.$retNextCounterNum('PurchOrder','PONum) Returns NextPONum
If a counter doesn't already exist for PurchOrder/PONum the oRefs object automatically inserts a refs counter record for PurchOrd/PONum with the default value of 1000 and then increments it to 1001. If the record exists, oRefs fetches the record, increments its counter value, updates the record in the database, and if the update succeeds returns the incremented value to the sender.
The oRefs object has its own autocommit session with the database to ensure that counters don't get rolled back. Once a counter has been incremented it can't be rolled back. To do so would cause problems in a multi-user environment.
StudioWorks will automatically set up a counter and use if for a field if you enter [refscounter] as the for a field in the SQL meta-data editor.I find the every time you turn around someone has the need for yet another system preference that needs to be store in the database. Having to add a column to an sSysPrefs schema and then to the table in the database adds to your work and makes it difficult to keep applications in sync with the database. Especially if you have multiple customers with multiple databases.
System preference data types vary. They could be character, date, time, boolean, integer, or other data types.
The Refs table can be use for storing your system preferences. You can insert Refs records with the RefType='syspref' and then set the RefsGroup, RefsSubGroup, RefsDesc, etc. to whatever values you like.
You can create a special qSysprefsList and qSysprefsEdit query class ($extraquerytext = WHERE RefsType='syspref') and declare window instances SysprefsList and SysprefEdit for viewing and editing your own System Preferences.
Any time you need another system preference, you simply add it to the Refs table with the RefType='syspref'.
You may want to create an oSysPrefs object with property methods that fetch the appropriate Refs record(s) through the refs task variable.
Do refs.$retSysPrefsList(TableName) Return SysPrefsList
These are just some ideas and suggestions on how you can use the Refs table for storing your System Preferences.The Refs library is the inspiration of Robert Mostyn. At OmniCamp UK 2004 Robert explained his technique of having a References table for storing all kinds of short lookup lists, user defineable business rules, preferences, etc.
Robert pressed me to implement it in StudioWorks. It took a while for the power of this technique to sink in but as I developed new applications it become clearer to me that a Refs table would be handy in many situations, and flexible.
The decision to put the Refs table into its own swRefs4 library was inspired by the concept used by many Unix programs where they create and use many small programs which have a limited well defined scope of functionality. By keeping the swRefs4 library's scope narrow, it can be more easily implemented in a wider range of StudioWorks applications. At least that's what we're hoping for.
I hope that the swRefs4 library will assist you in more quickly developing your applications and allow them to be more flexible.
Thanks for Robert Mostyn for sharing the Refs table strategy with us.
Doug Kuyvenhoven