Create record automatically in a linked table

[Juan_Pedro_Paz_]

I have two tables with customer information that I use for CRM. For every customer record in table-1 there needs to be one and only one record with the same customer name in table-2. The tables are linked by the customer name. The primary field in table-2 is a formula that is equal to the name of the customer in table-1.

Table-1 structure
CUSTOMER NAME
FIELD1
FIELD2
(… sensitive data)
TO_TABLE-2 (linked field, to one and only one)

Table-2 structure
CUSTOMER NAME (formula so it is equal to field TO TABLE-1)
… data accesible by a third party
TO TABLE-1 (linked field, to one and only one customer in Table-1)

They are separated in order to give different view/edit permissions to different people.

Manually everything works fine. But I create customers on the go in my cell phone and can easily forget to create the corresponding customer in table-2.

Is there a way to automatically create and link the customer record in table-2 when a customer is created in table-1?

Thanks!

[Jeremy_Oglesby · Accepted solution]

Hi @Juan_Pedro_Paz_Solda

While I understand your motivation for doing what you are doing, I don’t think it’s going to work the way you want it to. There’s no way to give person access to View/Edit one table in your database, without their being able to View/Edit all the other tables as well. This is one of the more significant shortcomings of Airtable. It will work with just Viewing permissions, but as soon as you give somebody Edit permissions, they can see and edit the entire database, not just one table.

For that reason, my first suggestion is that you are probably much better off just maintaining a single table of all your Customers, and working around the sharing limitations in a different way, rather than trying to balance two tables of identical information.

However, if you still feel it’s necessary to maintain two Tables of Customer information, you will want to do this in two different bases, as that will allow you the closest thing to the kinds of permissions you are wanting. But you have to have a way to ensure data is copied back and forth between the two tables, and there are a couple ways to do that.

You could do it manually by downloading a CSV of the data from one base, and then importing that CSV into the other base on a weekly/daily/hourly basis (however often is needed). This will require manual resolving of conflicts and probably also some massaging of data, and use of the De-Dupe block to prevent duplicates. This will definitely require a degree of manual attention to ensure data integrity.

The other option is to use an automation service like zapier.com – this will allow you to write rules that will handle transferring data automatically, but it will require some carefully written, and probably complex automation workflows to keep the two databases in sync.

If it were my decision to make, I’d probably choose to accept the limitations of Airtable’s permissions system, and just keep my Customer records in a single table, for the sake of simplicity, data-integrity, and my own sanity :confused: