Interested in the community’s thoughts on this. I have come up with a solution that seems to be too complex, but cannot think of anything easier.
The best guidance I’ve seen so far on this forum is this post but I don’t think it came to a conclusion.
The problem is we are using Airtable as an application, not just a database. Building quotes and scheduling work and many other things. It is very functional (actually business critical), but a little bit too wild. We want to lock down more tightly who can get access to what. Also hide complexity and offer simple use cases to certain user categories, where they cannot modify data that they shouldn’t.
So our approach is to create an Appsheet front end for our database, while still allowing certain user categories to access the native Airtable base as an application. We thought this would be pretty basic. Turns out not, or we are missing something fundamental. If we are, I’d be grateful if someone could explain.
It seems that if we link tables together in Airtable and want to be able to manage those links in native Airtable as well as via an Appsheet app, then we need to setup duplicate methods for linking:
- In Airtable a link column and then a text column (which we call App Link )
- In Appsheet, ignore the link column but make the App Link column type Ref
- Use automations in Airtable to copy App Link to whenever values are updated or a new row created.
And also – we use formulas for our key columns in Airtable, but the key columns in Appsheet must be editable (because we want to allow Appsheet to add new rows). Therefore we need to:
- create an extra column in Airtable for key (which we call App Key )
- is a formula in Airtable while App Key is editable short text
- is ignored in Appsheet, while App Key is set as the key
- an Initial Value expression is set up for App Key is Appsheet that replicates the formula in Airtable
One of the down sides of all this is that App Key and can diverge over time, even though they are both still doing thier respective jobs holding all the correct references. We have not worked out if this is a show stopper yet. Still prototyping.
Is there an easier way???