BUG: Cannot add row to AppSheet Database that includes a ref with custom key

[andy-mcdonald]

AppSheet Databases don't seem to like custom keys. Here's a simplified example:

• I have 2 tables: Project and Person
• Project includes an Owner column that is a Person ref
• Person has an Employee_ID column set as its key

When adding a new row to the Project table using an action, I'd expect to be able to enter an Employee_ID as the value for the Owner field, and the ref to resolve correctly.

If I try to do this via an Action, everything seems to work fine client-side but, I get a sync error saying "Unable to add row to table 'Project' -> Invalid request provided to AppSheet Database" and the new row is not saved to the database. If I try to add the row via the API, the request fails with a similar message.

Using the default Row ID column as the key for the Person table works as expected, so I suspect this is a bug related to custom keys for AppSheet Databases. I've seen similar comments elsewhere (including this post) and the advice from several months ago was that AppSheet Databases is still in preview and may contain bugs. Using custom keys seems like a pretty common use case (especially when working with external data sources) so just looking for confirmation this is a known issue, and any updates on when a fix may be coming. 

cc: @ShirleyN 

[ShirleyN · Accepted solution]

Hi there, this is behaviour of the system generated "Row ID" key is working as expected, however, I agree is not very intuitive. We are looking into improving this, in the meantime there are a couple work arounds for your existing reference column and when creating new ones. Basically the experience defaults back to a Sheet: https://support.google.com/appsheet/answer/10106672?hl=en&sjid=9314050451854058934-NA#system-generat... 

[andy-mcdonald]

Thanks for the quick response @ShirleyN!

I followed those instructions and I'm no longer getting the sync errors. That said, it seems in order to use custom keys with AppSheet Databases, you basically can't use relationships in the database editor itself - hopefully the AppSheet team can find a solution that allows for both in the future 🙂

FWIW, I think steps 2 & 3 might be a little misleading... the newly created Lookup column needs to be converted to another type (eg: Text) before deleting the original Reference column, otherwise it will be deleted too.