One common reason to create a subtable is when you have a "lookup" table containing descriptive names for code values. For example, as discussed in the Creating a Subtable topic, you may have a Customers table with a foreign key called CustomerType that contains the ID value for a record in the Lookups table representing the customer type, and another foreign key called LeadSource that contains the ID value for a record in Lookups representing how they became a customer. Thus, Lookups contains two different types of records: customer types and lead sources. Since you can't have two relationships between Customers and Lookups, you typically create one or more subtables of Lookups and use subtables for the relationships to Customers.

However, one downside of that approach is that you may end up with a lot of subtables. To avoid that, you can use another approach: instead of creating relationships between Customers and Lookups or its subtables, create calculated fields in Customers that use the built-in SQLookup function to display the desired values from Lookups. For example, create a calculated field in Customers named CustomerTypeDescription with the following expression:

SQLookup('Lookups', 'ID', CustomerType, 'Description')

This tells Stonefield Query to look for the current value of the CustomerType field in the Customers table in the ID field of Lookups and if it's found, return the contents of the Description field. You'd use a similar expression for another calculated field named LeadSourceDescription:

SQLookup('Lookups', 'ID', LeadSource, 'Description')

When you use either of these calculated fields in a report, they'll show the contents of the Description field for the appropriate record in the Lookups table.

SQLookup has the following syntax:

SQLookup( TableToLookIn, FieldToLookIn, ValueToSearchFor, ExpressionToReturn )

It retrieves the value for the ExpressionToReturn expression from the TableToLookIn table for the record where FieldToLookIn has ValueToSearchFor as its value. ExpressionToReturn can be a single field name to return the value in that field or can be an expression. Normally, you need to put quotes around TableToLookIn, FieldToLookIn, and ExpressionToReturn. If ValueToSearchFor is the name of a field from the current data set, don't put quotes around it.