Sw4   >   Misc   >   Refs


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:

  1. Edit helper droplists or combo-box lists.
  2. Counters - For keeping track of the LastPONumber, LastInvoiceNumber, ...
  3. Storing preference settings which apply to all of the users of a database. (System Preferences, Report Properties, ...)
  4. Business rules that are soft coded.

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 SQL Meta-Data Editor 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:

  1. Create a subclass of oRefs in your main library if it doesn't already exist and point the refs main library Startup_Task task variable to the oRefs subclass in your main library.
  2. Override and modify or add your own custom methods to the oRefs subclass in your main library.

Refs Table Columns

The sRefs schema class lists all of the columns in the Refs table.

The Refs columns of interest are:

  1. RefsType - This is set to lookup is you are working using the SQL meta-data Refs Lookups.
  2. RefsGroup/RefsSubGroup - For lookups these match the group/subgroup you enter in the SQL meta-data Refs Lookups.
  3. RefsDesc - The description to be displayed to the user.
  4. RefsDataType - Specifies the Refs column where the actual Refs record value is stored.

    Each Refs table record has columns for storing different data types. (RefsBinary, RefsBoolean, RefChar, RefsDate, RefsTime, RefsInteger, RefsNumber2dp, RefsNumber3dp, RefsNumberFloatdp, RefsPicture, RefsRow) By setting RefsDataType = char, the oRefs object knows to look in the RefsChar column for the actual stored value. This gives you the flexibility of storing virtually any data type in any refs record.
  5. RefsDisplayText - The actual Refs record value in the specified RefsDataType column is copied to this column, so that we have a common column for displaying the record's value. The table class tRefs automatically sets this value on insert or update. 

Refs Lookups

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.

  1. Open the SQL Meta-Data Editor, select the sPerson schema, select the PersonGender column.
  2. Enter Person in the Refs Group field.
  3. Enter Gender in the Refs Sub Group field.
  4. Check the Mandatory field to enforce matches with the Refs Lookup.
  5. Close the SQL Meta-Data Editor.
  6. Open the Refs window from the Main Window.
  7. Insert a New Refs record. Type=lookup, Group=Person, SubGroup=Gender, SortOrder=0, RefsDesc=Female, RefsDataType=Char, RefsChar=F. Save the record.
  8. Create a New Copy of the previous inserted Refs record. Set RefsDesc=Male, RefsChar=M. Save the record.
  9. Create a New Copy of the previous inserted Refs record. Set the RefsDesc=Unknown, RefsChar=M. Save the record.

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:

  1. If you want to store lookup 'keys' which you 'hide' from your user, go with a true lookup table and an fkey to the lookup table.
  2. If you want a flexible edit helper that suggests or limits the possible entries in a particular field, then use the Refs Lookups.

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.

Refs Counters

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 Default Value for a field in the SQL meta-data editor.

Refs System Preferences

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
Vencor Software