globaldictionarybuild
globaldictionarybuild
DICTIONARY
,
DATABASE
,
KEY
,
VALUE
,
Subset

The globaldictionarybuild statement builds a global dictionary by scanning a database.

Parameters

This statement has five parameters:

dictionary – is the name of the dictionary to be created/modified. If the dictionary doesn’t already exist, it will be created. If the dictionary does already exist, it will be emptied (all values cleared) before the database information is added.

database – the database to be scanned.

key – is a formula used to calculate the keys in the global dictionary. Normally this formula will include one or more fields from the database being scanned. The result of this formula must be text. Note: This formula is NOT quoted.

value – is a formula used to calculate the values associated with each key in the global dictionary. Normally this formula will include one or more fields from the database being scanned. The result of this formula can be any data type. Note: This formula is NOT quoted.

subset – is a formula used to determine which records should be included in the global dictionary. Normally this formula will include one or more fields from the database being scanned. The result of this formula must be boolean (true/false). If the formula is omitted, all records will be included (including invisible records). Note: This formula is NOT quoted.

Description

This statement builds a global dictionary by scanning a database. It’s similar to the arraybuild statement, but builds a global dictionary instead of an array. See Global Dictionaries to learn the basics of creating and accessing a global dictionary.

The primary purpose of this statement is that once the global dictionary is built, you can look up values in it with the globaldictionaryvalue( function. Because global dictionaries use a structure that is very efficient for searching, lookups performed this way can be hundreds of times faster than looking up directly from the database with the lookup( function. However, this speed increase is only practical if you plan to do many lookups without ever modifying the data. If you just plan to do a single lookup, or if the data is modified frequently (every time the data is modified you would have to use the globaldictionarybuild statement again), just using a regular lookup( statement is better.

For example, suppose you have a database named Contacts that contains names and email addresses. You can build a global dictionary with this information with a single line of code:

    globaldictionarybuild "ContactEmails","Contacts",Name,Email

Now suppose you have another database that also contains names and email addresses, and you wish to update the email addresses with the email addresses from the Contacts database. This database has fields named FullName and E-mail. This code will fill the E-mail field with the latest email addresses gleaned from the Contacts database.

    field "E-mail"
    formulafill catcherror(«E-mail»,globaldictionaryvalue("ContactEmails",FullName))

We tested this code on two databases with 20,000 records of data, the entire process (first building the global dictionary and then doing the formula fill) took about 3 seconds. We then tried this same operation using the lookup( function, like this:

    field "E-mail"
    formulafill lookup("Contacts",Name,FullName,Email,«E-mail»)

With the same 20,000 record databases, this operation took almost 7 minutes! Using globaldictionarybuild was 134 times faster. In our tests, the speed difference was larger as the databases got larger.

If you are considering using globaldictionarybuild with formulafill, however, you should probably take a close look at the join statement. Using the join statement, you can update the email addresses with a single line of code.

    join "Database","Contacts","Key","Name","SourceKey","FullName","«E-mail»",{Email}

The join statement uses the same high performance internal structure used by global dictionaries, so the perfomance is also very fast – about 3 seconds for our 20,000 record test databases.

See Also

- fieldvalue( -- grabs the contents of a field in the current record of a database. You can grab data from the current database, or from another database.
- formulavalue( -- calculates the result of a formula based on data in the current record of any open database. This is similar to the fieldvalue( function, but allows any formula to be used, rather than just a single field.
- join -- joins data from another database into the current database.
- joinonerecord -- joins matching data from another database into the current record.
- lastlookupdatabase( -- returns the name of the database referenced in the most recent lookup.
- lookup( -- searches a database for a value, then returns other information from the same record. For example, the *lookup(* function can look up a phone number given a customer name, or look up a price given a part number.
- lookupall( -- 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.
- lookupalldouble( -- is similar to the lookupall( function, but it returns two fields from the target database instead of just one.
- lookupalloctet( -- is similar to the lookupall( function, but it returns eight fields from the target database instead of just one.
- lookupallquadruple( -- is similar to the lookupall( function, but it returns four fields from the target database instead of just one.
- lookupallquintuplet( -- is similar to the lookupall( function, but it returns five fields from the target database instead of just one.
- lookupallseptuplet( -- is similar to the lookupall( function, but it returns seven fields from the target database instead of just one.
- lookupallsextet( -- is similar to the lookupall( function, but it returns six fields from the target database instead of just one.
- lookupalltriple( -- is similar to the lookupall( function, but it returns three fields from the target database instead of just one.
- lookuplast( -- searches the selected records in a database for a value, then returns other information from the same record. For example, the *lookuplast(* function can look up a phone number given a customer name, or look up a price given a part number. Unlike the lookup( function which searches from the top of the database, the *lookuplast(* function searches backwards from the bottom.
- lookuplastselected( -- searches the selected records in a database for a value, then returns other information from the same record. For example, the *lookuplastselected(* function can look up a phone number given a customer name, or look up a price given a part number. Unlike the lookupselected( function which searches from the top of the database, the *lookuplastselected(* function searches backwards from the bottom.
- lookupmoredata( -- looks up an additional data field based on the previous lookup.
- lookupmoreformula( -- looks up additional data based on a previous lookup.
- lookupselected( -- searches the selected records in a database for a value, then returns other information from the same record. For example, the *lookupselected(* function can look up a phone number given a customer name, or look up a price given a part number.
- related( -- searches a database for a record in a related database that matches the current record in the current database based a relational specification (based on one or more key fields or formulas in each database), then uses a formula to return other information from the same record. This is similar to the lookup( function, but instead of using an individual field for the key, a relation is used (see Relational Database Management).
- relatedarray( -- builds an Text Array by scanning a database and creating an array element for every record that matches a relational specification (based on one or more key fields or formulas). This is similar to the arraybuild( function, but instead of using an individual field for the key, a relation is used (see Relational Database Management).
- relatedrecordid( -- returns the record id of a record in a related database that matches the current record in the current database based on a relational specification.
- Relational Workshop -- tool that assists in composing relational lookup(, superlookup(, lookupall( and arraybuild( functions.
- serverlookup( -- searches a shared database for a value, then returns other information from the same record. Similar to the lookup( function, but the search is performed on the server instead in the local computer's memory.
- superlookup( -- searches a database for a record that matches a query formula, then uses a second formula to return other information from the same record. This is similar to the lookup( function, but instead of using an individual field for the key and data, any valid true/false (Boolean) formula can be used.
- table( -- 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.

History

Version | Status | Notes
10.2 | New | New in this version.