Using Groove Lookups - Part 1 (draft)

Christian posted a entry in the Microsoft Groove 2000 forms Forum that caught my attention - I often review the forum the but just have not found the time to help - but, I will in future.

Some background to explain what lookups are and how they operate - then a simple workaround to fix the bug that Christian has identified.

Lookups provide the capability of getting data from any Forms tool and using the data in your script. For Text, Multi-line Text, Drop-down list, List Box, and Static Text fields, you can use lookup procedures to obtain values from other fields.

– Lookups are a mechanism by which you can get values of a field from the records of a form tool

– You can either get the values from all of the records, or values from records where another field matches a specified key value, defined either as a constant value or based on the value of a field in the current record

• The lookup procedure can find field values in Groove forms or InfoPath forms in either:
• The same Forms tool
• In a selected Forms tool in the same workspace
• In a selected Forms tool in another workspace.
• You can also add lookups to macros and form scripts.
• Field types that allow lookups will have the Lookup property.

I know of 10 ways to use the lookup functionality – so let us recap what you should know in the form of a primer.

Managing lookups

You can view and modify all existing lookup references in the Design Sandbox:

• Click Settings and Options
• Click the Lookup References tab

Lookup References shows a list of all existing lookups, their parameters, and their status.

Lookup properties

A lookup has many properties, as shown below:

• Select All or By Key
• Select All to create a lookup that returns all values in a selected view column,
• Select By Key to create a lookup that returns field values based on the value of another field.

• Space

• If the lookup values are stored in a Forms tool in another workspace, check “Allow looking up values in another workspace.” Then select the Workspace name from the drop-down menu.

• If you are referencing values in another workspace, all users of this Forms tool must also be members of the referenced workspace. Note also that this feature is not available for macros.

• Tool

• Select the name of the Forms tool that contains the values you are looking up, or select “This Tool” if appropriate.

• View

• Select the name of the view that contains the values you are looking up.

• Key Column. (For By Key lookups only)

• Select the column in the selected view which is read first before finding a value in the lookup column.

• Note: The Key Column must have a sort assigned to it in Column Properties in order to be displayed in the drop-down list.

• Key Value. (For By Key lookups only)

• Select the field value that is read first before finding a value in the lookup column. You can also select a field from which to obtain the value.

• Lookup Column

• Select the column that contains the lookup values.

• Return

• Select All to return all matching values in the lookup column, including duplicates, or select Unique to return all values except for duplicates.

• No Cache

• Check this option only if you want Groove to refresh the lookup results every time the form is loaded. This is suitable usually only in cases in which lookup values are constantly changing. If lookup values are generally static, it is best to keep this option unchecked.

• Return URLs as well as values. (Macro or form script lookups only.)

• Check this option if you want the lookup to return URLs so that you can use them to create a link in a rich text field.

• Comment.

• If you want, and you do!, enter a comment in the text box. The comment is displayed if the lookup needs to be resolved at some future time.

Note that only tools and views that are enabled as lookup targets are listed in the menu.

Resolving Lookups

Lookups reference precise workspace targets.

• For example, lookups commonly reference another tool either in the same or in another workspace.
• If you save a Forms tool that contains lookups as a new tool template, and then add the new tool to a workspace, lookup targets do not automatically update and may be unresolved.
• If you have unresolved lookups in a Forms tool, Groove attempts to resolve them when you first add the tool to a workspace.

• If unsuccessful, Groove prompts you to manually resolve the lookups.
• You can also attempt to resolve lookups on the Lookup References pane in the Forms designer.

Deleting lookups

You can delete lookups you no longer want from the Lookup References pane.

• For field definitions, deleting a lookup simply removes all settings from the Lookup property.
• For script lookups you will need to remove the references in your script files.

Enable a Custom Tool to be a Lookup Target

For security purposes, Groove tools are initially disabled as targets for lookups from other tools or workspaces.

The designer must enable this option to allow views in the tool to be the targets of lookups.

Once enabled for the tool, designers can then enable selected views as targets for a lookup.

Enable a View to be a Lookup Target

For security purposes, views in custom tools are initially disabled as targets for lookups from other tools or workspaces.

Note that in addition to enabling a selected view to be a lookup target, you must also enable the tool to be a lookup target (see previous slide).

• Open the Design Sandbox
• Select the view you want to enable as a lookup target.
• Click the Options tab.
• Check “Allow view to be a target for remote lookups”.
• Save your changes.

Creating Script Lookups

Create script lookups by clicking on the Create Lookup String button in the script editor

• Complete the dialog that identifies the Forms tool and details of the lookup
• Select the copy button.
• When you exit and return to editing the script or macro.
• Paste the lookup string into your code.
• The pasted code assigns the values from the lookup to a variable.

Tip – Script Lookups

As with Lookups completed within a field definition, script lookups are stored the Groove Lookup References in the Designer.

However, the Designer Object Mane is the form:

• 5b6d62f7g9a9e33bcb4bd8c… this can be very to understand many weeks after you have completed your design session.

o Tips

• Always add comments to your lookups
• When creating lookups in scripts – do the following
• Create a new script file called FormLookup.js
• Create ALL your lookups with the script file FormLookup.js
• Paste the lookup string into FormLookup.js, then rename the VAR to something more meaningful and over document what you are trying to achieve with the loolkup.
• Optionally, of you have a large number of lookups I would add a form type check and only load the lookup when load a particular form name.

Why? The lookup object is linked to the script in which it was created – delete the script you loose the lookup reference. If you know where all your lookups are you are not likely to delete it!

Deep Dive into how a Lookup works

As stated above the lookups provide the capability of getting data from any Forms tool and using the data in your script. For Text, Multi-line Text, Drop-down list, List Box, and Static Text fields, you can use lookup procedures to obtain values from other fields.

When a form is initialised - the field data is updated if the field is a lookup. Each field is tested for the attribute LOOKUP and if the attribure is present the lookup is populated.

var FieldHasLookup = false;
var LookupAttribute = i_Element.getAttribute(“LOOKUP”);
if (LookupAttribute != null) {
FieldHasLookup = true;

}

Where i_Element is the field object.

Then, when you create lookup with a script - the ENUM is populated during runtime - you must have created the lookup as part of the design process and the lookup must be resolved. As you have 8 different lookups available it is essential to understand these, I will list these later in this article.

The essential information that is need to programmtically use a lookup is provided via the designer/lookup paste function - but this does not provide insights into how the method works. This is my view on the method - the lookup requires the following information a TelespaceURL, a ToolName, a Number(ViewID), a KeyColumn, a KeyValue, a LookupColumn, to return a unique value or not, to cache or not.

TelespaceURL

ToolName

Number(ViewID)

KeyColumn

KeyValue

LookupColumn

bReturnUnique

bNoCache

These parameters are obtained via the method GetApp().LookupValuesByStringForFormScript(), but in reality the method (shown in bold) we are interested in is var ValuesBSTREnum = GetApp().LookupValuesByStringForFormScript(int_GetLookupStringForObject(“”), document) as this returns the lookup string from the database!

This returns a string an XML representation of the lookup in the form of TelespaceURL, ToolName, Number(ViewID), KeyColumn, KeyValue, LookupColumn, bReturnUnique, bNoCache.

So, you now know that int_GetLookupStringForObject(“1234781203471234”) returns an XML string of the lookup. We can now manipulate the lookup and we can fix the German bug and we can have one lookup perform a host of other lookups - essentially you have knowledge that can simplify using lookups.

More, see part 2