Creating and Using a Lookup Table

A fast and easy way to store and retrieve frequently used information during a data entry session.

A Lookup Table is a table of values stored in memory as an array. It's like having a small database within your database. It can contain information that's frequently needed during a data entry session, such as a state sales tax rates, shipping zones, discounts rates for different order levels, or state or province abbreviations.

The example below shows one method. In this example, a few state names are stored in the table along with their abbreviations and (imaginary) tax rates.

Create your Lookup Table array in the Global Code section of the form as a stat (static) variable. This way, it is available to your form element programs during the entire data entry session:

/*
Declaring a "Lookup Table" array with 50
rows and 3 columns. The array can store 
50 x 3 or 150 values, populated below. The
table/array goes into the form's Global Code,
while the programs that access it are connected
to form elements and their events.
*/

stat LU1 as array{50, 3] of String

LU1[1, 1] = "AL"; LU1[1, 2] = "Alabama"; LU1{1, 3] = "5.67"
LU1[2, 1] = "AK"; LU1[2, 2] = "Alaska"; LU1[2, 3] = "0.00"
LU1[3, 1] = "AR"; LU1[3, 2] = "Arkansas"; LU1[3, 3] = "6.25"
LU1[4, 1] = "AZ"; LU1[4, 2] = "Arizona"; LU1[4, 3] = "6.50"
// and so forth for the rest of the states

With this array available to the form's elements, you can use programming to retrieve the information you need.

The following On Element Change program for the State Abbrev element looks for a match on the state abbreviation. If it finds it in the table, it fills the State Name and Tax Rate. The loop is stopped as soon as a match is found. If no match is found by the time the loop expires, the user is notified, the lookup elements are cleared, and the cursor is returned to State Abbrev:

Var i as Int
Var foundit as Int
/*
The "Lookup Table" array was created and populated in Global Code. Now run a loop routine in the array until it finds first match on the State Abbrev field and returns the corresponding values to the other fields.
*/

If State Abbrev <> "" Then
{
	Foundit = 0
	State Abbrev = ToUpper(State Abbrev)
	i = 1
	While (i <= 50) And (foundit = 0)
	{
		If LU1[i, 1] = State Abbrev Then
		{
			State Name = LU2[I, 2]
			Tax Rate = LU1[i, 3]
			foundit = 1
		}
		i = i + 1
	}
	If foundit = 0 Then
	{
		@Msgbox("'" + State Abbrev + " ' not found in table.", "", "")
		Clear(State Name, Tax Rate)
	}
}

Another way to populate (fill) a Lookup Table is to have your programming read in a delimited external text file, parse the values in it, and distribute those values to the array. This way, when you want to update the information in the table, you simply edit the text file - you don't have to update your programming unless the number of columns and/or rows exceeds the array declaration. See the Parsing a Delimited File and Importing Records with Programming Only examples elsewhere in the Knowledge Base or in Appendix 2 of the Sesame Programming Guide.

This, and other programming examples, can be found in Appendix 2 of the Sesame Programming Guide.