When checkbox is checked in table A, delete record from Table B

Ho Gabriel, and welcome to the community!

Scripting aside, two very key database constructs compel me to ask:

1. Have you investigated Airtable synching capabilities?
2. Is it possible that your two tables are really just the same table with two different views?

Hi Bill!

Thanks for taking the time to answer my question.

I have not investigated Airtable synching capabilities. I will have a look but any reference would be appreciated.

I don’t think that my two tables are the same with different views but one table does display information from another table. Is there a way to verify that?

Thanks!

Gabriel

Verify? Nah - this is about your data model design. Views are extremely powerful - they let you consolidate information and manage it as a unified collection of records. Then, when you need a certain filtered view of those records and with specific fields, views allow you to virtualize the outcome instead of having separate physical silos.

Hi Bill,

Thanks for your help. I will have a look at the articles you referenced.

To give you a bit more context:

We are using Airtable as an asset management platform to track our Softwares.

We have 1 base with multiple tables.

In one table, we are adding all softwares Subscriptions.

In another one we are adding Purchases (any softwares or services that doesn’t have a subscription.)

A third table compiles data from the Subscriptions and Purchases tables and builds the Budget 2023 table.

I was able to create an automation rule that if a record (a line item) has the Add to budget checkbox checked from the Subscriptions table, it add it to the Budget 2023 table.

Now, what I am trying to do, is to create an automation rule that if the same Add to budget* checkbox is unchecked, it removes this record (line item) from the Budget 2023 table.

It seems the only way to achieve what I am trying to do is to run a script with the When a record matches conditions trigger.

I found those references that is basically providing the script but I don’t know how to tweak it so it works for my configuration:

Maybe our data model design will prevent us from building such automation but it’s hard for me to tell since my experience with Airtable is limited.

Cheers!

This is a common challenge - how do you know if you laid out the data structures to meet the emerging requirements as you continue to shape the solution? It’s a moving target in most Airtable systems.

I don’t have a simple answer for this, but no one else does either because they don’t know your business, nor are there any forward-looking written requirements that can guide us. But I know that early data model choices tend to dictate (often constrain) what’s possible in the future. Also, know that in most cases, early experiences with Airtable typically miss two key capabilities that, if fully understood, invariably change the data model approach. A clear understanding of synching and views may cause you to reshape your data and your workflows.

As a matter of practice, I avoid engaging in data model design, and especially through community conversations because there are so many unknowns in this exchange, and it’s really easy to provide bad advice.

It is possible to build the automation with this data model. However, that is a different (but related) issue from whether or not the data model is a good way of storing and representing the data.

Creating the automation you want is a matter of assembling the pieces in the right order. For example, instead of using a “when record meets conditions” trigger, you could use a “when record updated” trigger that watches the checkbox field. Then use conditional actions to either create or delete the linked record. To delete the linked record, you need to get the record ID of the linked record from the linked record field of the triggering record. This will also probably require a bit of rewriting of the script. Rewriting the script requires a bit of knowledge of JavaScript and the Airtable scripting. Since you don’t know how to write Airtable scripts, you can hope someone will write one for you for free, or you can hire someone to write the changes. The changes are very minor.

However, even if you get this automation to work the way you want, you may still be reinforcing a base design that will not serve you well in the long run.

Thanks for your feedback Bill and Kuovonne.

I will regroup with my Airtable stakeholder and discuss about your feedback.

Cheers :slightly_smiling_face:

Would another approach be that, if an orphaned record in Table B is discovered (where its Link Field to Table A is blank), then that record should be deleted?

Whether this approach works depends a lot on how the table is used. For example, if people ever create a record in Table B manually, you run the risk of a newly created record being deleted before data entry is completed, because it might not have a value in the linked record field. The same issue exists if a record ever needs to change which record it is linked to. In between unlinking the old record and linking the new record, the record will be briefly an orphan.

Depending on the relationship and the mechanism for deleting the child records, it can also mean more automation runs compared with a scripting solution.