Using Lookups in Scripts and Macros

Article
10/16/2013

You can use lookups in scripts or macros to get data from any Forms tool. You can either get field values from all of the records of a form tool, or values from records where another field matches a specified key value, either a constant value or the value of a field in the current record.

You can create a lookup by clicking on the Create Lookup String button when editing a script or macro. You fill out a dialog that identifies the Forms tool and details of the lookup, and then click the copy button. When you exit and return to editing the script or macro, you can paste the lookup string into your code. The pasted code assigns the values from the lookup to a variable. You can use these values in your script or macro.

In a Forms script, you can do a lookup in any Forms tool within the same workspace or in another workspace. In order for a cross-workspace lookup to work correctly, all members of the workspace in which the script is being defined must also be members of the specified lookup workspace. In a macro, you can do a lookup on any Forms tool in the same workspace, but you cannot do a cross-workspace lookup.

For lookups to work, and to be able to choose tools or views that you want to target in the lookup,you must first allow lookups in the design options for both the targeted tool and targeted view.

To create a lookup, do the following:

Click the Create Lookup String when editing a script or macro.
Select whether you want to lookup all records in a view or only those that match the specified key value.
If you are doing a cross-workspace lookup (in Form scripts only), select the "Allow lookup values in another workspace Check Box".
If you are doing a cross-workspace lookup, select the workspace in the Space Drop Down menu. If you have not selected the Check Box, "This workspace" is shown in this field, and you can go to the next step.
Select the Forms tool where you want to do the lookup. If you are doing a lookup in the same Forms tool as the one your script or macro is in, select "This tool".
Select the View where you want to do the lookup.
If you are doing a lookup by key value, you then select the key column and key value.
The key column can be any column that is sorted in the view. This key column is in the looked-up Forms tool and chosen view. The key value can be any constant value that you enter into the key value field or any field in the Forms tool containing the script or macro.
Select the lookup column in the view. The lookup returns the values from this column.
Select whether you want the lookup to return all values or unique values. If you select unique, the lookup filters out duplicate values.
If you want to improve efficiency, do not select the "No Cache" Check Box. With a cache, the Forms tool saves the values returned by lookups and reuses them if the lookup is done again while the cache is active. If you are looking up in a Forms tool where records are added or updated frequently and it is important that the lookup returns the latest values, you should select the "No cache" Check Box. The cache is valid for four hours before being refreshed.
If you want the lookup to return URLs so that you can use them to create a link in a rich text field (in Form scripts only), select the "Return URLs as well as values" Check Box.
Enter a comment into the text box. This comment is displayed if the lookup needs to be resolved at some future time.
Click the Copy button.
Click the Exit button.
The lookup code is now in your paste buffer, and you can paste it into your script or macro.

The Key Column field Drop Down lists the sorted columns in the looked-up Forms tool View. Note that you can only do keyed lookups on columns that are sorted. You can either enter a text constant in the Key Value field or select one of the fields in the current form from its Drop Down menu. The Key Value specifies the value that will be used to find matching records. The lookup string will search through the looked-up Forms tool View to find records where the Key Column matches this value.

The pasted code for a lookup that returns values but not URLS looks like the following:

var ValuesBSTREnum = GetApp().LookupValuesByStringForFormScript (int_GetLookupStringForObject("b698...f71d7"), document);

The pasted code for a lookup that returns values and URLs looks like the following:

var ValuesAndURLsStringStringEnum =  GetApp().LookupValuesWithURLsByStringForFormScript (int_GetLookupStringForObject("1195...7d973"), document);

You can optionally rename the variable on the left of the assignment once you have pasted the lookup into your code.

To access the returned values, you can use IGrooveBSTREnum for lookups without URLs or IGrooveStringStringEnum for lookups with URLs.

Other than renaming the variable or deleting the entire lookup, you should not make any other changes to the code on this line. Specifically, you should not make any changes to the parameters of the LookupValuesByStringForFormScript or LookupValuesByStringForMacro method.

The parameters for the lookup are stored in a lookup reference. You can view all of the lookup references on the Lookup References tab of the Settings and Options page in the designer.

Within the reference, the workspace and Forms tool are identified by internal IDs rather than by name, so the lookup execution is not affected if someone changes the workspace or Forms tool name.

However, if you save a Forms tool or the workspace containing it as a template, and then use the template to create a new Forms tool, all lookups in the tool except lookups to the same Forms tool (This tool) will be listed as unresolved, and the unresolved lookups will need to be resolved in order to ensure that the lookups continue to function properly. Forms automatically attempt to resolve lookups, and will warn the designer if there are unresolved lookups in the tool.

Share via

Using Lookups in Scripts and Macros

See Also

Concepts

Additional resources