This article assumes you are familiar with basic AppSheet design concepts and how the AppSheet platform works.
"Item-Detail" is one of the most common data design patterns in mobile apps. In fact, almost every meaningful AppSheet app utilizes this pattern in some form. It is a natural consequence of the "entity" approach to modeling data.
Scenario 1: An app that captures sales orders. Each order is for one customer and has many line items. Each line item identifies a product and a quantity purchased.
Scenario 2: A factory has several pieces of equipment and each has to be inspected several times over the course of the year.
Scenario 3: A school class assigns group projects for the students. Each student needs to add information about the project she worked on.
Scenario 4: A number of papers are submitted to a conference and the reviewers add their reviews to each paper.
In all of these scenarios, there are two entities involved in a nested relationship. Let us use the first scenario to explain. The entities are Orders and LineItems. The standard way to model the data is to have two tables: Orders and LineItems.
- The Order table has an Id, Customer Name, and Date
- The LineItem table has an Id, OrderId (Ref to the Order table), Product, Cost, and Quantity.
The OrderCapture sample app in our samples gallery demonstrates this pattern.
In order to add a new order, the user follows this sequence of steps:
- Create an Order with the customer information
- Add one or more LineItems to the order just created
Here are some common variations that augment the basic Item-Detail pattern.
- De-referenced values: each LineItem may need to include some information from the Order. For example, the Order may have a virtual SalesTax column and the LineItem may use this in a SubTotal column. This column would have an AppFormula: [Cost] * [Quantity] * [OrderId].[SalesTax]
- Aggregate computation: the Order may have a virtual column that sums up the total value of the order across all the LineItems. This would be a virtual column Total with AppFormula: SUM(SELECT(LineItem[SubTotal], [OrderId] = [_THISROW].[Id]))