The lookupall( function builds a text array containing one item for every record in the target database where the data in the keyField matches the keyData. Each item in the text array contains the value extracted from the dataField for that record. If the data field is a numeric or date field, it is converted to text using the default patterns for that field.
Parameters
This function has five parameters:database – is the database that will be scanned. This database must be currently open. If this parameter is "" then the current database will be scanned. Note: The lookupall( function will scan every record in the database, including records that are not currently selected. If you want to build an array from only selected records, use the arrayselectedbuild( function.
keyfield – is the name of the field that you want to search in. For example if you want to look up all checks written to a certain vendor, this should be the field that contains vendor names. 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 all checks written to a certain vendor, this should be the actual vendor name. 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 check numbers, this should be the name of the field that contains check numbers. This must be a field in the database specified by the first parameter. Note: This function can optionally handle multiple data fields, with a separator, see below for further information.
separator – is the separator character for the text array you are building (see Text Arrays).
Description
This function creates a Text Array by scanning the contents of a database looking for records where the keyField matches the specified keyValue. For each matching record, one or more dataFields are returned.
Note: To learn more about the theory and technical details about working with links between databases, see Linking with Another Database. The lookupall( function can also be constructed automatically for you with the Relational Workshop wizard.
To illustrate this function, we’ll assume that we have two databases, Vendors and Checkbook. The Vendors database contains a field named Company that lists each company. The Checkbook contains fields for CheckNumber, Date, Payee (the name of the company each record was written to, and Amount. Within the Vendors database, this formula could be used to display all the checks written to the currently selected company. (For example, you could use this formula in a Text Display Object).
"Checks written to "+Company+": "+lookupall("Checkbook",Payee,Company,CheckNumber,", ")
The output of this formula will be something like this:
Checks written to Acme Services: 384, 502, 615, 789
The lookupall( function allows you to add additional DataField parameters (as many as you like). If you do this, you must also add an additional Separator parameter at the end of the parameter list.
lookupall(database,keyfield,keyvalue,datafield1,datafield2, ... ,separator,subseparator)
The Separator parameter specifies what characters to put in between the returned data field values.
Here is an example that uses this option to create a more detailed check list.
"Checks written to "+Company+":"+cr()+
lookupall("Checkbook",Payee,Company,CheckNumber,Date,Amount,cr(),", ")
In this formula there are three data fields: CheckNumber, Date, and Amount. The separator is a carriage return, so that each check will be listed on a separate line, and the separator is a comma followed by a space. The output of this formula will look something like this:
Checks written to Acme Services:
384, 02/17/12, $4,198.37
502, 06/04/12, $3,845.71
615, 08/29/12, $6,198.38
789, 10/15/12, $1,912.00
Tip: The lookupall( function is limited in the ability to format the data from the second database – you can specify a separator but that’s about it. At the price of some additional complexity you can gain a lot more flexibility by using the arraybuild( function. Here’s an example:
"Checks written to "+Company+":"+cr()+
arraybuild(cr(),"Checkbook",
{" Check #"+str(CheckNumber)+
" written on "+datepattern(Date,"Mon dd, yyyy")+
" for "+pattern(Amount","$#,.##")},
{Payee=}+constantvalue(Company))
The output of this formula will be something like this:
Checks written to Acme Services:
Check #384 written on 02/17/12 for $4,198.37
Check #502 written on 06/04/12 for $3,845.71
Check #615 written on 08/29/12 for $6,198.38
Check #789 written on 10/15/12 for $1,912.00
The arraybuild( function also allows you to create a more complex relationship between the two databases, possibly based on multiple fields or on relationships other than straight equality. This revision of the previous example only lists checks written to the specified company in the past 90 days.
"Checks written to "+Company+":"+cr()+
arraybuild(cr(),"Checkbook",
{" Check #"+str(CheckNumber)+
" written on "+datepattern(Date,"Mon dd, yyyy")+
" for "+pattern(Amount","$#,.##")},
{Date>today()-90 and Payee=}+constantvalue(Company))
See Also
History
| Version | Status | Notes |
| 10.0 | Updated | Carried over from Panorama 6.0, but now optionally allows multiple data fields instead of just one. |