Mark R.D. Jones

Lookup categories in DabbleDB

As posted to the DabbleDB user forum, May, 2010 -

In DabbleDB terminology, a “category” is similar to a database table, with additional attributes unique to the DabbleDB database application.

A lookup category is a secondary linked category, connected to some primary category, constructed by the user. Using a lookup category can be a method to have data in a primary category entry’s field transformed, or translated into another value. A lookup category is DabbleDB’s version of a lookup table.

A lookup category turns out to be a frequent answer to a variety of questions appearing on this forum. There’s nothing particularly special about a lookup category, except that it is a required method to accomplish several kinds of active data mappings or translations within DabbleDB, for lack of another method to do so within the existing feature set. In a sense, every category linked to another category is a lookup category. As a linked category, using a link-to-entry type of field to make the connection, the desired field value is derived into the original primary category.

A lookup category permits the following:
• a text value to be interpreted as a number, perhaps for calculation elsewhere;
• or similarly, permits interpretation of numeric data as a text value, perhaps for concatentation elsewhere;
• enables deriving a second, third, or more values based on a single data entry value;
• permits revising a data conception using conditional-logic into a non-conditional conception, avoiding, for a fairly wide set of circumstances, DabbleDB’s lack of a conditional logic feature for data;
• conversion of a field of type “choice” into a choice-like field with the above translation abilities, while retaining most of the other attributes of the DabbleDB choice field, especially if using the “pages” data entry forms. A notable lack in such a conversion, is the inability to default a field to a particular value;
• and additional mappings of one value onto one or more other values.

A lookup category can be as small as one entry, and it can be large with hundreds or even thousands of entries.

Here’s a two-entry example, translating “yes” and “no” to numeric values. You could have your data entry form indicate “yes” or “no” for some easy data entry purpose, and behind the scenes, use the number in a formula, perhaps to multiply some number, where “yes” indicates no change in value, and “no” means zero out the value.

Text  Value
----  -----
No      0
Yes     1

Here’s a sketch describing how to convert from a checkbox field to a link to entry to lookup category.
Questions » Use Check Box or Choice as a number (March 2009) Http://

Here are a variety of examples that build on that illustrative sketch.

Questions » How can I deduct from a field depending on a status (December 2009)

Questions » Complex “IF” or “Case” formula? (February, 2010)

Questions » Force Invalid Entries (March 2010)

An example of a large lookup category relating to dates:
Creating an index of weeks based on a date field’s value (May 2010)

About these ads

Comments are closed.