"Lookup/drop-down" is the most common design pattern in any mobile app that captures information and is used to help users pick values from a fixed list of options
Scenario 1: An app that is used by a sales rep to track potential leads. When entering data, some values like the Source of the lead need to be chosen from a fixed list of sources (Referral, Direct, Solution Partner, and so on). Other values are dependent on each other -- the Region should be chosen from a fixed list and the Country should be chosen from a sub-list for each Region.
Scenario 2: An app that is used to capture purchase orders. Each order is for a particular product, which is chosen from a list of available products.
Scenario 3: An app that inspects equipment in factory buildings. The user is an inspector who needs to choose a particular building from a fixed list of choices when entering data.
As you know from basic app design concepts, every app models its data in terms of entities (such as Equipment, Customer, Order) and each entity has property values (such as Cost, Country). In each of these scenarios, we need to consider: is the user choosing a property value (for example, a status for a Sales Lead) or is the user choosing an entity (for example, a Product for an Order)? We'll look at each of these cases.
Choose one value from a strict list of options
In Scenario 1, each sales lead needs to have a value that represents the source of the lead. The allowed values are Referral, Direct Sales, and Solution Partner. The Sales Lead table should have a column called Source which is of Enum type (Enum is an abbreviation of enumeration). As part of defining the type, you also enumerate its allowed values.
Enum column type for you.Choose one value from a (not-strict) list of suggested options
Perhaps the three options are not fully descriptive and occasionally the user needs to enter their own value (for example, Promo).
One way to do this is to use a Suggested Values expression. Any column can define a list of Suggested Values. These are presented to the user as a drop-down of choices, but the user can still explicitly specify a different value.
Another option exists for the Enum column type. While it specifies a specific set of enumerated values, the column definition can indicate that it allows other values. This has the effect of treating the enumerated values as suggestions.
Choose more than one value from a list of options
In some scenarios, the app may need to record multiple values from a list. For example, perhaps a sales lead was generated by a referral from a solution partner. To model this possibility, the column type would need to be an EnumList rather than an Enum.
Choose one value via "Lookup" from a strict computed list of options
When entering a new sales lead, the user has to choose a region (USA, Asia, EMEA) and a country from each region. However, these are not fixed lists. The company is expanding rapidly so the list of regions and countries is growing dynamically.
The design pattern for this scenario is to have a separate lookup table of values. The Source column type should simply be marked as Text. However, the column should be further constrained by a Valid_If constraint that indicates that the allowed values must come from a specific column of the lookup table.
In AppSheet, this constraint is expressed as: LookupTable[Region]
In general, this constraint can be more complex accessing multiple tables and involving filters and other logic. The important requirement is that it generate a list of allowed values when evaluated.
It is important to understand that the Valid_If constraint is a strict constraint on the allowed values. If this constraint is provided as an expression that computes a list, then AppSheet can show that list in the form of a drop-down to choose from. However, new values that are not in the list cannot be added by the user.
Choose one value via "lookup" from a strict computed dependent list
Each region has a different list of countries. Once the user chooses a specific region, the choice of country should be limited. How should this be modeled?
This should also be modeled using a Lookup table and a Valid_If constraint for the column. In the sample app, you will observe that the Lookup table has two columns --- Region and Country. The Valid_If constraint needs to indicate that the allowed values are the subset of country values where the Region matches the chosen Region.
In AppSheet, the common cases of such "dependent drop-downs" are simple to set up, as described Dependent drop-downs.
Choose one value from a (not strict) computed list of suggested options
What if you wanted to give the user a computed list of choices, but also want to let the user provide their own value instead?
The way to model this is similar to the Enum design earlier. Use an Enum type column with no explicit values. Enable the Allow Other Values option and provide a Valid_If expression that computes the list of suggested values and also one additional value (Other).
As an example: LookupTable[Region] + {"Other"}
Now add an additional column that is hidden by default, but with a Show_if condition that checks to see if the Enum value is Other.
When an app is set up this way, the user gets the option to choose Other from the drop-down. If it is chosen, the extra input field is shown so that the user can enter a custom value.
Choose an entity
In Scenario 2 (the Order Capture app), each Order needs to choose a Product. Each Product is not just a value -- it has several properties that are associated with the product. In this case, the Order needs to reference the Product. This is modeled using a Ref column type. When the user is entering an order, the reference column will be presented as a list of Products.
By its nature, the list of Products is dynamic because it is dependent on another table (Products). In most apps, such a table is set up to be read-only or add-only. This ensures that once an Order references a Product, then that Product is not later removed.
Choose more than one entity
What if each Order actually corresponds to a purchase of many products? In most common cases, this decomposes into the Item-Detail pattern. For example, each Order has multiple Order Detail line items. And each Order Detail line item has a Ref column to one Product.
It is rare that there is a need for a column where the user can select multiple references. In terms of data modeling, such a column should be of type List of Ref. While AppSheet does not have this specific column type, it is possible to simulate this with an EnumList column type and an appropriate Valid_If constraint.
When there are multiple design choices
Now that we've covered all these lookup/drop-down alternatives, we still have to choose the right option for each specific scenario. Sometimes it is not immediately obvious.
Scenario 3 (the Building Inspection app) demonstrates a common data modeling issue. The app captures inspection records. Obviously, there is a table to hold Inspections and each inspection record picks a specific building. Should these be considered values (Building A, Building B, and so on) or should they be considered entities represented by a separate table?
Both are valid design choices. If there is nothing to be modeled beyond the name of the building, then it is fine to model it as a value. We have done that in our sample app to keep the design simple. However, as the app evolves, it is often the case that there is a desire to record properties with each building (for example, the square footage or the address). This causes a redesign to break out Buildings as a separate entity/table. In general, concepts that represent a natural class of things in the real world will naturally be modeled as entities/tables in AppSheet.