Manage common key between AppSheet and Airtable

CHRISTOPHE_CHAN · 02-10-2021 06:58 AM

Hi,

I am testing AppSheet with Airtable datasource. I am wondering what is the best practice managing common keys between these 2 tools.

Airtable has a record ID field working backend but easily visible. I suppose it is a good key to use in AppSheet, if the data source is read only in AppSheet. But I can’t find out what could be a correct key if data is add/update/delete in AppSheet, and. potentially linked with other tables in Airtable.

Thanks

MultiTech

This is a good place to start:

Realistically any value that’s unique to a table can be used as they key.

In this instance, I would stick with the RecordID field from Airtable.

In regards to what you should use for the initial value, here are my three rules for keys:

Always a text column type
Always hidden
Always UNIQUEID()

There are few instances where creating a “common sense” key (where you take a value entered in the row (like a name or something) and use that (cleaned up of course) as the key - but this is rare.

CHRISTOPHE_CHAN

Hi,

I usually use UNIQUEID() expression in AppSheet. I am sure the RECORDID expression in Airtable is also the best key, also computed.

The question is more about how manage keys if both AppSheet and Airtable are allowed to create new record. There is a conflict between 2 applications computing the same key field.

Steve

If rows are or can be created in the app, the app must determine the key column values. Key column values must be defined when the row is created. If a row is created in the app, the row–which must have a key column value–exists and can be referenced in the app even before the row is sent to the data source for storage.

CHRISTOPHE_CHAN

Thanks Steve. I already got your point about “key must be generated by Appsheet if aloowed to create rows” and “even before row creation in datasource”. If I want same relations to work in both AppSheet and Airtable, I need common keys what looks impossible if both application are allowed to generate new rows.

I may try something like :

One ID column for AppSheet using UNIQUEID()
One ID column for Airtable using RECORD_ID function from Airtable
Set an automation in Airtable, replacing the value of the AppSheet ID column by the value of the Airtable ID column, as soon as a new row is created.

Steve

Correct.

This will greatly limit what you can do with references in AppSheet. Under no circumstances would I recommend this.

CHRISTOPHE_CHAN

Sure Steve I know this looks terrible for consistency reasons. I guess for all relation issues it would generate.

I meant replacing the AppSheet ID value of the newly created row, as soon as this row is created (and only at this specific moment). The trigger would be the appearance of this row in the data source. This being instantaneous, I did not see the risk of it because the original key created by AppSheet (the one which desappear) would never be used for ref anywhere.

Did I miss something in the logic ?

Steve

AppSheet does not interact with the spreadsheet in real-time. Please be sure you fully understand this document:

Peter_Borg

This is the approach I have taken, despite @Steve not recommending it under any circumstances. Problem is I can’t see any easier way. I’ve put out a call for help here and if not mistaken, I think @CHRISTOPHE_CHANDELIE 's initial query was never resolved (??).

CHRISTOPHE_CHAN

Hi Peter,

I did not pursue in this direction of using airtable as back end due to these limitation. I may try again but for read only or add only tables. Turnaround to manage key by duplication look too sensitive unfortunately. Airtable and could be an interesting combo.

CHRISTOPHE_CHAN

Hey that’s what I missed thanks for your suggestion.

Austin

Could you just not make the keys unique to each system? Start and end the unique keys with 2 different values that would prevent them from ever creating the same value? This is why I use INT cause if I need system values I can just auto int which databases can handle and if I want appsheet values I can just use negative values which can’t be produced by the system.

CHRISTOPHE_CHAN

@Austin_Lambeth You lost me somehow here I am not familiar with the INT concept you suggest. The main concern in my case is that Airtable does not seem to allow the computation of the key field. All relations would be based on a system generated record ID.

Austin

INT as in integer or just a number for a key.