Selecting a Key

Automatic Key Selection

When you add a new table to you application, 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 is 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. This isn’t a good key however, because 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.

 

Manual Key Selection

You can manually override AppSheet's key selection.

Do this as follows:

  1. Open the application in the Editor.
  2. Go to the Data > Column Structure tab, scroll down to the table you want to change, and click Show.
  3. Uncheck the "Key" property for the existing key column.
  4. Check the "Key" property for the column you want to make the key.
  5. If you don't want to see this column in your app, check the "Hide" property for this column.
  6. 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.
  7. 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 when the record is created. By contrast, the "APP FORMULA" is recomputed when the record is created or updated.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.