Unstable Key Error

Is anyone else receiving errors involving Unstable Keys for tables that use _RowNumber as the key? I have handfuls of tables that are used exclusively to pull data that is combined from 3-5 tables via a view in my database. These tables don’t have unique values that could be used as keys. This hasn’t thrown any errors until yesterday evening.
@Steve I already have a support ticket in, just wondering if this is unique to us :upside_down_face:

Yes, I saw this today when temporarily assigning a row key to the _RowNumber for a test.

You really DO NOT want to use _RowNumber as a key value. IMHO, they should actually NOT ALLOW it to be set as a key. As I understand it, _RowNumber is simply a sequential number assigned to the rows as they are retrieved. But they MAY NOT be retrieved in the SAME order, meaning rows MAY NOT get the same key value from session to session of using the app.

If this column is used on Ref columns and Parent/Child setups, where the row key value is SAVED in the data and rows are given different _RowNumber values at some later date, then the linked rows will change and cause app issues.

In practice, I advocate to ALWAYS use data less keys - key values that are not tied to the data at all. AppSheet has the UNIQUEID() function just for this purpose. This is standard practice in the software development industry.

WHY?

Well, for basically the same reason as described above for _RowNumber. If that data used as the key needs to change OR you realize that you need to multiple rows with that same key value, then all of the underlying “linked” tables/row will be affected. I can provide examples if you like.

Bottom line, is the structure that links all the tables/rows together should be separated from the data. This typically means a dedicated unique ID column in each data table used as the row key. The only exception might be utility tables used to support the data (e.g. a Color list, Code list, Category list, etc).

3 Likes

The only reason there is a ref column in this keyless table is because that returns the label and may not even be totally necessary. I very rarely leave _RowNumber as the key but I do have tables that do not contain any valid key value since they use joined database views.

I do agree with the statement that tables should all have data less keys. Any table that I use, in my more recent apps, does have data less keys. Learned that lesson the extreme hard way. But allowing _RowNumber to be the key does help the ease of use for very simple apps.

I’ve found this in my older apps where I had not learned that I should always have keys in my data even if it wouldn’t theoretically be necessary. The app that I first saw this new error just happened to have 1 table with _RowNumber as the key by accident because I didn’t bring through a column that would be unique in my database view and didn’t bother to go back and bring it through since that key would not be what is referenced to grab my necessary data as I’m grabbing sets of rows instead of single rows.

I completely understand. For even simple apps, the longer they live the more likely they can run into key issues when using _RowNumber.

In case you didn’t know, in the initial creation of your sheet or database table, if you name a column with “ID” at the end, AppSheet will automatically, upon table creation, assign that column (or the first one) as the row key, set it to Text data type AND automatically set the Initial Value expression to the UNIQUEID() function. It can’t get easier than that!

2 Likes

Posting my response from another thread with the same problem:

3 Likes