A key uniquely identifies each 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 app, 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 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.
Above, we can see that the name of a national park is selected as the key of the table. This is important because the key must be chosen carefully. In this case, the key is the name of the park because the name uniquely identifies each park. There is only one Arches National Park, as there is only one Yellowstone. Therefore, there is no risk of duplicate keys and the resulting issues.
Types 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, 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 uniquely identifying 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 app formula 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 app formula that includes the current date or time might yield different results over time, so it would be prohibited in a key field's app formula.
Why Not Worksheet Formulas?
In a spreadsheet, it's sometimes convenient to have an ID column that 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 app 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's 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's 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.
One alternative to system generated sequential keys, is system generated random keys.
We currently support two mechanisms 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. The row number is not a good key. If entries are moved or deleted, or if users add or delete entries simultaneously, the row number for each row will change 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.
Selecting a Key
Automatic Key Selection
When you add a new table to you app, AppSheet attempts to find a suitable key for the table.
AppSheet first examines each worksheet column from left to right, looking for a column that contains unique data values. If your worksheet has a good key column, it's a good practice to make it the leftmost worksheet column. If AppSheet finds such a column, it makes that column the key.
Appsheet next examines pairs of worksheet columns from left to right, looking for a pair of columns that contain unique data values. The pair of columns need not be adjacent. If AppSheet finds such a pair of columns, it will combine the columns to create a "Computed key". The computed key will be added as a Virtual column at the end of the table.
If AppSheet cannot find a key column using any of these techniques, it will default to using row number as the key. The Editor will give a warning if row number is chosen as the key.
Manual Key Selection
You can manually override AppSheet's key selection as follows:
- Open the app in the Editor.
- Go to the Data > Column Structure tab, scroll down to the table you want to change, and click Show.
- Uncheck the "Key" property for the existing key column.
- Check the "Key" property for the column you want to make the key.
- If you don't want to see this column in your app, check the "Hide" property for this column.
- Click the blue pen icon to change the column settings, and scroll down to the "INITIAL VALUE" * setting. Change this to UNIQUEID() to generate a unique text code for each row. Note: You can also use RANDBETWEEN(low, high) if you want a numeric key.
- Save your changes.
The key value for a record must be assigned once when the record is created and it must remain constant for the life of the record. That is why you must specify the key value in the Initial Value property and never in the App Formula property. The Initial Value property is computed once when the record is created. By contrast, the App Formula is computed once when the record is created and is recomputed each time the record is updated.