Key field question

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

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.

3 Likes

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:

2 Likes

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

1 Like

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:

2 Likes

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

1 Like