Panorama > Formulas > Linking with Another Database
 
table(
table(
DATABASE
,
KEYFIELD
,
KEYDATA
,
DATAFIELD
,
DEFAULT
,
LEVEL
)

The table( function searches a database for a value, then returns other information from the same record. Unlike the lookup( function, the table( function does not require an exact match. If it does not find an exact match the table( function will use the closest match. For example, the table( function can look up a tax rate given an income amount, or look up a shipping price given a zip code and weight.

Parameters

This function has six parameters:

database – is the database that will be searched. This database must be currently open. If this parameter is "" then the current database will be scanned. Note: The table( function will search all records in the database, including records that are not currently selected.

keyfield – is the name of the field that you want to search in. For example if you want to look up a shipping price by weight, this should be the field that contains weights. The field must be in the database specified by the first parameter. (Note: For compatibility with older versions of Panorama, you are allowed to quote this parameter, however, quoting is not necessary.)

keydata – is the actual data that you want to search for. For example if you want to look up a shipping price by weight, this should be the actual weight of the package. This parameter is often a field in the current database, but it could also be a variable, a constant, or a general expression.

datafield – is the name of the field that you want to retrieve data from. For example if you want to retrieve a shipping price, this should be the name of the field that contains prices. This must be a field in the database specified by the first parameter.

default – is the value you want this function to return if it is unable to find the information specified by the keyField and keyData parameters. This will only happen if the keyData value is smaller than the smallest value in the keyField field. The data type of the default value should match the data type of the dataField. If the dataField is numeric, the default should usually be zero. If the dataField is text, the default should usually be "". Note: This parameter is optional. If it is omitted, the table( function will return an error if the search fails.

level – is the minimum summary level to be searched. Usually this parameter is zero so that the entire database will be searched. If the level is set to 1 through 7, only summary records will be searched. Note: This parameter is optional. If it is omitted, all records will be searched.

Description

This statement searches a database for a value, then returns other information from the same record. Unlike the lookup( function, the table( function does not require an exact match. If it does not find an exact match the table( function will use the closest match. For example, the table( function can look up a tax rate given an income amount, or look up a shipping price given a zip code and weight.

If the function is able to locate the information specified by the keyField and keyData parameters it returns the contents of the specified field in the specified database. If it cannot find an exact match, it finds the closest value (but not greater than the keyData value). If the keyData value is smaller than any value in the keyField the function returns the default value (or an error if no default value has been specified).

For example, suppose the key field contains the values 5, 25, 100, 250 and 1000. If the key value is 47, the table( function will match with the record containing 25 in the key field. If the key value is 4700, the table( function will match with the record containing 1000 in the key field. If the key value is 4, there is no match, because there is no value in the key field less than 4. In this case the default value will be used.

The table( function is designed to be used with rate lookup tables like tax tables, shipping tables, volume discount tables etc. Our example will calculate shipping prices. Suppose you have a database called Shipping Rates that contains the fields and values shown here.

The table( function interprets this table like this: From 0–49 pounds in Zone 1, the rate is $2.50 per pound. From 50–99 pounds the rate is $2.35/pound. From 100–249 the rate is $2.25 per pound, and so on. Items 2,000 pounds and over are shipped for $1.86 per pound. The other zones are similar.

The procedure below calculates the shipping charges for a package using the database shown above.

local PackageWeight,DestinationZone,ShippingCharge
PackageWeight="" DestinationZone=""
gettext "Package weight:",PackageWeight
PackageWeight=val(PackageWeight)
if PackageWeight<=0
    message "Sorry, anti-gravity option not available."
    stop
endif
gettext "Zone Number (1-3)",DestinationZone
if length(DestinationZone)<>1 or DestinationZone<"1" or DestinationZone>"3"
    message "Zone must be from 1 to 3"
    stop
endif
ShippingCharge=Weight*table("Shipping Rates",Weight,PackageWeight,
    "Zone"+DestinationZone,0,0)
 message "Shipping charge is: "+pattern(ShippingCharge,"$#,.##")

Notice that this example actually calculates the name of the data field on the fly: either Zone1, Zone2, or Zone3. The data field is still a single field (remember, only one item can be transferred at a time) but we are using a formula to calculate what the name of that field is.

In a real database you probably would not ask the user to enter the zone, but would have another database that would relate zones to zip codes. Here’s a simple Zone Chart database that divides the entire USA into three zones based on the first three digits of the zip code.

The last value in this table, 99:, is the smallest value that is greater than the last legal zip code (999) according to the ascii character order. This record can help catch illegal zip codes. For instance, ABC is greater than 99:, so the Zone will be 0 for this illegal zip code. The assignment below will turn a regular zip code (Zip) into a zone number according to the Zone Chart database.

DestinationZone=table("Zone Chart",Zip3,Zip[1,3],Zone,0,0)

This assignment can easily be plugged into the previous example to calculate the shipping charges given the weight and zip code.

See Also

History

VersionStatusNotes
10.0UpdatedCarried over from Panorama 6.0, but now the default and level parameters are optional. Also, you now are allowed to use "" to specify the current database.