Key field question

kjhaugh
Participant I

I am just started down the road of AppSheets and love it but need help.

I work with a non-profit organization and am building an app to manage maintenance requests for them. I create an “ID” column as the first column of the tables I create. So, for example, the RESIDENT table’s first column is “Resident_ID”, the second column is Last Name, the third column is First Name, etc. Other tables are Units, Maintenance Records, Issues, Maintenance Staff. I know I will have to develop relationships between each of the tables but I’m not there yet.

When I import the tables in the MyApp web interface, the system created a field called “_ComputedKey” in the “Resident” table and concatenated the Last Name and First Name fields. Question: Is it better to use that “_ComputedKey” field as the key field OR use the “Resident_ID” field as my key. If the latter, will the system automatically create the unique data in the “Resident_ID” field in the underlying Resident table?

I ask because I use to work with Access tables and always stated each table with a “tablename_ID” field and then manually established the relationships between tables.

Any help you can provide would be greatly appreciated.
Ken

0 14 2,448
  • UX
14 REPLIES 14

Jonathon
Participant V

In this case you should use Resident_ID as the primary key. The computed key on first and last name would in theory fail if two users had the same name and spelling.

Edit: Remember, the goal of the primary key is to always be UNIQUE within the dataset.

There are some circumstances where, by the structure or nature of the application, combining two columns will always produce a unique result. In these cases you may consider using computed primary key to save space in your database.

Thank you, Jonathon. I appreciate your help.
Ken

Jonathon,
Another quick question…
If I do use the “Resident_ID” as the key field, do I have to manually give it a number (i.e., 1,2,3,4,max#) OR does AppSheet generate a unique number and populate that field in that table?
Ken

You can use the UNIQUEID() formula for the initial value - this will generate a random 8 character hex code.

You can read more here:

Excellent. Thank you.
Ken

Jonathon,
sorry to bug you. Since changing the key value to the “sheet name_ID” key field with the “UNIQUEID()” value, the (minimal) amount of test data currently in the tables disappears. Any idea why?

I will put further questions out on the list. I don’t want to take advantage of your generosity.
Ken

Does your test data have blank primary keys? This may be one cause.

Otherwise, if you provided me a screenshot of your test data and/or column structure from AppSheet I could help you troubleshoot better

Hi Jonathon,
I’m sorry for the late reply. I was at church and then preparing for having a large group over for the Super Bowl.

I think you found the problem. The tables that have no data in the Primary Key display no data when I am in the Tables section and select View Data select. One table that does display data, I manually entered data in the Primary Key field in the table. Another table that displays data, AppSheet created its’ own Primary Key.

I set some of the tables to Initial Value = UNIQUEID() in the Auto Compute section and thought that AppSheet would populate the data in the table but evidently that is wrong.

What do I do after setting Initial Value = UNIQUEID()? Is there another step after that?

Jonathon, I don’t want to take your generosity for granted so please tell me if I am beginning to bug you.
Ken

By setting the initial value to UNIQUEID(), you are ensuring that all future records that get created through the app will be created with a unique identifier. These unique identifiers should be created once and persist unchanged for the life of the record, so they must be saved directly in the database (they cannot be null).

I presume you are using a Google Sheet as your database. If so, you can use this formula to populate your Resident_ID column:

=DEC2HEX(RANDBETWEEN(0, 4294967295), 8)

Once you have generated the values you will have to copy -> paste as plain text to overwrite the formula and prevent it from recalculating. You can read more about manually generating uniqueid here:

I got the tables to work with the unity “tablename_ID” unique key as you described above. I cannot thank you enough. I truly appreciate your time.

I work for a non-profit organization. We have 3 retirement centers for retired colleagues so money is tight, not all that unusual for non-profits. Consequently, I am trying to build a way to track maintenance requests and maintenance items that all three of our centers can use. You helped get a better understanding on how to set up the tables. I used to create Access databases. Creating unique fields with Access was easy. I found this process a little more challenging.
I still have a long way to go!
Thanks again,
Ken

@Jonathon I think your post answered the question/issue I’ve had about key fields, but I’d like to confirm I’m understanding correctly.

By setting initial value to UNIQUEID(), I’m only taking care of new records that AppSheet creates. Anything I add as a row to Google Sheets myself by hand, or with another tool will NOT have an initial value set, correct?

If that’s the case, is there some way to convince AppSheet to pull in rows with blank key fields, and then apply an initial value?

Based on my current understanding, because I have a 3rd party tool adding columns to my spreadsheet, in order to see those columns in AppSheet, I have to either manually add a unique value by hand(see below), or “run with scissors” and use implicit RowNumber as my key.

I’m currently using the following formula to generate a manual key in Google Sheets when necessary: `=concatenate(year(now()),"-",month(now()),"-",day(now()),"–",DEC2HEX(Randbetween(0,4294967295),8))

Can AppSheet be setup to perform an action every-day at say 5 am? Without requiring a user to initiate that action by running the app?

I’m wondering if I can use 2 AppSheet apps to more safely run my desired app:

  1. App1 - Uses Implicit RowNumber as the key. This app finds rows that don’t have an entry in the “AppSheet Key” field App2 will use, and adds a unique ID to the key field. Perhaps there’s a button/function to “Add Key to any new Rows/Entries”
  2. App2 - Uses “AppSheet Key” as the key field. This is my current app which functions like it does currently, but it lets multiple users be working at once.

Is there some better solution to this?

Can AppSheet be setup to perform an action every-day at say 5 am? Without requiring a user to initiate that action by running the app?

The short answer is no: AppSheet will not read rows with missing primary keys, and therefore it will not be able to perform any actions against them. I don’t believe using a second appsheet app is a good solution to this problem.

What is your other third party tool using for a unique row identifier? It may be possible to use this instead of UNIQUEID().

Another option would be to use Google Apps Script and a worksheet trigger to populate the primary keys from your third party tool as rows are added.

A third option would be to have your third party tool pass the new rows through appsheet via a webhook, if your tool supports this.

This is bad practice with AppSheet. Ideally, AppSheet should be the only thing modifying the spreadsheet. Changes by other means are likely to cause problems.

Nope. AppSheet ignores spreadsheet rows without a key column value.

Adding columns to the spreadsheet will break the AppSheet app until the app is configured to be aware of the added columns.

https://help.appsheet.com/en/articles/961724-reports

I imagine there must be.

Contrary to @Jonathon, I think the “user-less”, rownumber-keyed, 2nd app idea could actually be a good solution here. Have it run a Change Data Report on all rows without the key value. I think that’s some pretty good out-of-the-box thinking. I’ve briefly thought about it to try and identify how or where it could fail, but didn’t come up with anything yet.

Although I do agree, getting your external source to pass through a unique key value from the beginning is the better way to go.

Top Labels in this Space