How to Return Matched Values from Two-Dimensional Arrays

• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
• Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, 2.5b, 2.6
• Microsoft FoxPro for Macintosh, versions 2.5b, 2.5c

SUMMARY

Using the ASCAN() function to search an array poses no difficulty when you are performing simple lookups on one-dimensional arrays. When you are matching a value to an element in the first column of a two-dimensional array, another value within the same row can be returned easily with a statement using the same function.

MORE INFORMATION

In the following sample code, a fictional tax table array is referenced by matching the state abbreviation and returning its proper name as well as the sales-tax percentage for that particular state.

   * Returns full name of state and tax rate in formatted string
   DIMENSION a_Tax(4,3)
   a_Tax[1,1]='NC'
   a_Tax[1,2]='North Carolina'
   a_Tax[1,3]=6.75
   a_Tax[2,1]='TX'
   a_Tax[2,2]='Texas'
   a_Tax[2,3]=0.00
   a_Tax[3,1]='WA'
   a_Tax[3,2]='Washington'
   a_Tax[3,3]=5.00
   a_Tax[4,1]='WV'
   a_Tax[4,2]='West Virginia'
   a_Tax[4,3]=4.25
   * Alternatively, the state information could have been stored in a
   * database; USEing the STATE database and issuing either
   *   SELECT <field_list|*> FROM state INTO ARRAY a_Tax
   * or
   *   COPY [FIELDS <field_list>] TO ARRAY a_Tax

   state='WA'

   name =a_Tax[ASCAN(a_Tax,state)+1]

   tax  =ALLTRIM(STR(a_Tax[ASCAN(a_Tax,state)+2],5,2))
   * This could be done in three lines, but one is more efficient:
   * pos =ASCAN(a_Tax,state) && position of matching state abbreviation
   * ntax=a_Tax[pos+2] && tax for state (numeric value)
   * ctax=ALLTRIM(STR(a_Tax(ntax),5,2)) && converts numeric to character

   ? 'The sales tax for ' + name + ' is ' + tax + '%.'

   The sales tax for Washington is 5.00%.

How It Works

The ASCAN() function returns the relative position of the element that matches the value being searched for. Even though the array is dimensioned with three columns, it can be referenced as if it were a single column. This means the second column of each row can be found one position past the first column of each row, while the third column can be found two positions past the first column.

ASCAN(a_Tax,state):

In this example, the a_Tax array is first scanned to find "WA", and its position within the array is returned as the number 7. As can be seen graphically from the above array assignments, the elements can be thought of as existing in a linear format, allowing each to be referenced in sequential order.

ASCAN(a_Tax,state)+1:

By adding a numeric offset that corresponds to the position of each column past the first column, the values from those columns in the matching row can be extracted. Since the second column is one position past the column, adding 1 to the result of the previous ASCAN() now evaluates to 8, which is the position for the proper name of the array row.

a_Tax[ASCAN(a_Tax,state)+1]:

Referencing the array with this expression as the element number returns the proper name that matches the state abbreviation.

In the case of the third column, adding 2 to the offset returns the numeric value for the tax percentage. Using the same method, any number of columns can be addressed by adding the offset of the desired column.

a_Tax[ASCAN(a_Tax,state)+2]:

This expression references the tax rate found in the third column of the row in which the matching state abbreviation is found.

STR(a_Tax[ASCAN(a_Tax,state)+2],5,2):

Because the tax rate is numeric and the intent is to present it in a character string, the STR function converts it to character type having five positions with two decimals (including the decimal point).

ALLTRIM(STR(a_Tax[ASCAN(a_Tax,state)+2],5,2)):

The ALLTRIM() function ensures that all spaces that would be produced with single-digit rates are removed so that the string can be formatted to include one space before and after the rate.