Re: How to change reference column?

percarlen · 10-15-2024 03:03 PM

When I created my app I imported my Google Sheets data to Appsheet, which is now my main database. The relations between the tables are based on row numbers that I had in Google Sheets. For example, the column customer_id in the table Contracts, refers to the column customer_id in the table Customers. This gives me problems in certain scenarios so I want to use Row ID as reference (that's the correct way, right?).

How do I change my table references from using my custom columns to using Row ID, without doing it manually?

Thanks!

Suvrutt_Gurjar

If there are many rows in the Contracts table , you could use reference actions or using a bot that has reference actions. (Action type" Execute and action on a set of rows)

Search results - AppSheet Help

Sample app for reference actions from the AppSheet help article Get started by using the sample apps - AppSheet Help

The sample app is below

Reference Actions - AppSheet

In general, you would need to add another column in the Contracts table called say [ customer_id_2] , run reference actions on this column to get the row IDs populated from the parent table. Once all the row Ids are populated, rename the original [customer_id] column in the Contracts table as [Customer_id_old] , make it a text column rather than reference and make the [ customer_id_2] as the reference column.

Please note this is a bit tricky and please do it with utmost caution if your app is being used in production. We from the community may not be able to help in case of any incorrect references etc.

percarlen

Thanks for your reply. Shouldn't references use the Row ID column?

Edit: When I think of it, the Row ID column will be the reference. Right? Will it be even for new entries? I also wonder if this only will be applied to the app, or to the actual database itself?

percarlen

This solution did not work. I have populated a new column with correct Row ID's, made the renamed the old reference column and made it to a text column, and finally made the new column a reference column. The references aren't found (all the rows in the column are empty). What am I doing wrong?