Skip to main content

I am using Airtable as the backend for my app. I have a Table A for registering users and a Table B for registering restaurants. Through the Google Matrix Distance API, I am using longitude and latitude data to calculate the distance between registered restaurants and user data, obtaining the results in the "distance" column of Table B where the restaurants are registered.

However, each automation I run through the script only updates for new users registering, causing interference with the distance results for users already registered. I would like each user to have their own distance calculation without interfering with others. I have no idea how to do this! I believe it might involve a filter or something like user ID .

If each user is only registering for one restaurant for the entire lifetime of your database, then just put the distance calculation in the user table.

However, if multiple users will be registered for multiple restaurants, then the only way that you can do this is to completely restructure your entire database as a “many-to-many relationship”, which is a more complicated type of relationship that requires 3 tables (instead of 2 tables) for your record linking.

You can read more about many-to-many relationships in Airtable’s support document here. https://support.airtable.com/docs/understanding-linked-record-relationships-in-airtable

p.s. If your company has a budget for this project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld 

 

 

Hello @PauloPurcino,

As @ScottWorld mentions, first of all, restructure your database if there is a scenario like that.

There are a couple of other solutions you can do, But it must require some technical knowledge of scripting and how to manage triggers.

Possible solution 1: Need more manual work but less Technical knowledge.

Add extra field(checkbox), Is checked if the matrix is calculated. 

Create 2 different views like "Distance Calculated" and "Distance Not Calculated". Use filters based on that Checkbox field.

You can use any other field to identify the Distance Calculated, But make sure you set up proper views to use on Automation.

You can create another automation(OR duplicate the same automation), Use the trigger when the record is entered to view. 

Do some changes to the script(action step), If needed.

At the end make sure that the record is marked as checked when the process is completed.

Possible Solution 2: Need less manual work but need more Technical knowledge

Here you can also use the same checkbox method OR use any other field to identify the Distance Calculated, But make sure you set proper views.

Then create a script extension to run the script inside of it.

The process of the script looks like, it fetches all the records from the table which not have those distances calculated. Run Loop and calculate it, then update it on the table. 

Note: If you've used a script created by some AI tools(GPT, Bard) then it creates more issues for you to modify the script. 

If you choose 1) stop the automation, otherwise, it calls twice for the future automations.

Reply


Terms & Conditions