A key uniquely identifies each individual row in a table.
When you add a row to a table, that row must have a unique key value. The row's key value must remain constant for the life of the row.
When you update or delete a row through an AppSheet application, the key is used to find the row to update or delete.
If two or more rows are inadvertently assigned the same key value, we say that the table contains "duplicate keys". This is a serious problem. If someone attempts to update or delete one of these rows, the wrong row may be updated or deleted.
Type of Keys
AppSheet supports three types of keys. From best to worst, these key types are:
- Natural Keys
- System Generated Keys
- Row Number Keys
Many tables have a "Natural" key. That is, they have a single field or a combination of fields that uniquely identify each table row.
Natural Single Column Keys
Your table may often have a single field containing a value that uniquely identifies each row. For example, an Employee table may contain an EmployeeId field that contains a unique employee id. When such a field is present, it makes an ideal key.
Natural Multi-Column Keys
Your table may contain two or more fields that together containing values that uniquely identify each row. For example, a Vehicle table, may contain a State field and a LicensePlateNumber field that together uniquely identify each row. A multi-column key is slightly more unwieldy than a single column key, but it makes an excellent key.
Natural Computed Keys
Your table may contain fields that can be combined to yield a unique computed key value.The row's computed key value must remain constant for the life of the row.
You specify the key computation expression in the key field's AppFormula property.
The Editor examines this expression to ensure that it yields the same result over time, and displays an error if it does not. For example, an AppFormula that includes the current date or time might yield different results over time, so it would be prohibited in a key field's AppFormula.
Why Not Worksheet Formulas?
In a spreadsheet, it is sometimes convenient to have an ID column which is computed with a worksheet formula. For example, the worksheet formula might increment the value in the previous row. This does not work for AppSheet table keys for two reasons. First, keys must be unique and unchanging over time, but that is not possible to guarantee with worksheet formulas. Second, it must be possible to compute the formula when the application is working offline, but that is not possible with worksheet formulas.
System Generated Keys
Some tables do not have a natural key. Instead, you would like the system to generate a unique key for each new row.
We are often asked if there is a way for AppSheet to generate sequential, unique keys starting from a user specified initial value. “Sequential” means there must be no gaps in the key sequence. For example, the values INV01000, INV01001, and INV01002, are sequential. Ideally the sequential keys should be issued in order of record creation time.
Unfortunately in a distributed system, with multiple users, and offering offline data inserts, it is technically impossible to generate identifiers satisfying all of these requirements. That is true whether the generated value is used as a key value or as a normal field value.
You can use a MAX expression to generate sequential unique key values; however, this only works when a single user adds records. For example, you might use the expression MAX(Orders[OrderNumber])+1 to generate a sequential OrderNumber key value for the Orders table. It is very risky to use such an expression to generate a key value when more than one user can add records, because the computed key values may not be unique.
On a related note, you may wish to use a MIN or MAX function to initialize a non-key field. For example, for a customer engagement record, you may wish to initialize the "Contact Date" field with the most recent date on which that customer was contacted. You can do this using a MIN or MAX expression such as MAX(Customer Engagement[Contact Date]) in the field's InitialValue or AppFormula property.
One alternative to system generated sequential keys, is system generated random keys.
We currently support two mechanism for creating system generated random keys.
- The “UniqueId()” function generates a 8 character long unique text value containing letters and numbers.
- The “RandBetween(numberLow, numberHigh) function generates a pseudo random numeric value between lowNumber and highNumber.
Because these functions generate pseudo random values, they do not require coordination between the clients and the server. They work for multiple users doing offline inserts.
Row Number Keys
If you do not specify a key and AppSheet cannot automatically find a good key, AppSheet will default to using the worksheet row number as the key. This isn’t a good key. If entries are moved or deleted, or if users add or delete entries simultaneously, the row number for each row will changes and there is no way for AppSheet to uniquely identify the row. The Editor will give a warning if row number is chosen as the key.