Hello Everyone. Can anyone advise on the bes...

references
(Mic L. Angelo) #1

Hello Everyone.

Can anyone advise on the best way to set up my app so i can so edit the fields used as the Primary Key?

I have several Google sheets in my Appsheet app that contain static data (like Personnel Usernames and Location records). I am using these sheets as a reference to another dynamic sheet that display all data. (The dynamic data sheet contains columns that reference the static data sheets)

However, i would still like to be able to edit the primary key in the static data sheets (as usernames for my users change from time to time) . Can anyone offer advise on the best way i can organize my sheets to do this?

(Philip Garrett) #2

@Mic_L_Angelo

Do the following: 1. Add an empty EmployeeId column to the Employee worksheet and enter “EmployeeId” in the column header. 2. I am going to assume that you will use UNIQUEID() as the INITIALVALUE for the EmployeeId field value. If so, assign each existing row in the Employee table an EmployeeId as described here help.appsheet.com - Manually Generating UniqueId Key Values 3. In the Editor go to the Data>Columns pane and click “Regenerate”. This adds the “EmployeeId” column to your table. 4. Mark the “EmployeeId” field as the key. Reset the key property for all other columns in the “Employee” table. Set the INITIALVALUE of the “EmployeeId” field to “UNIQUEID()”. 5. You will need to update the values of any records in other tables that refer to the “Employee” table. The Ref fields in those tables will contain the current “Employee” table key. You need to replace those with the appropriate new key values from the “Employee” table. Manually Generating UniqueId Key Values help.appsheet.com

(Philip Garrett) #3

@Mic_L_Angelo

You cannot change the value of a key. The key uniquely identifies the entity and must be fixed for the life of that entity.

If it is necessary to change the value of a field like Name, then you cannot use Name as the key. Instead, you should use some other field as the key, and you must pick a value for that field that never changes.

For example, if you are creating an Employee table, you should assign each employee a EmployeeId that is unique for each employee and never changes. You are then free to change the employee’s Name or other properties, if you wish.

(Mic L. Angelo) #4

@Philip_Garrett_Appsh thanks for the quick response Philip. In this scenario, i reference the Employee column in another table. So if were to create a Employee ID,

how would that change how i set up the Appsheet reference for the employee in the other table? Would i have to make a new column of the employee ID in the other table to make sure the employee gets referenced correctly??