How to automate relations via a shared column value

Hello community, I’m currently migrating from Google Sheets and Glide to Airtable. Glide’s dynamic filters / automatic relations are missed and I’m struggling to recreate those in Airtable. :-/

Each row in my table represents an item ordered from a vendor, items may be part of an order of multiple items from multiple vendors. How can I automatically relate items within one order that are supplied by the same vendor. The purpose is to create a roll up per vendor, for example to send a single automated email to the vendor per order instead of each single item.

In Glide I would simply have a formula field combining the OrderID and VendorID, which creates an automatic relation. How can I achieve similar functionality in Airtable?

(I’m wondering if this can be down within Airtable or needs to outsourced to Integromat, Zapier…)

Hi @Ricardo ,

welcome to Airtable community ! :slight_smile:

First of all you have to create the relationship of Items with vendors. This could be easy but due to you want to combine it with an email automation, firstly we have to think the condition under the which the automation will run. You may are interested how those two parts are related, the answer is in the info you want to include in your mail and the condition.

Giving us more specified directions and making your issue more clear, you will help us to give the solution you are looking for.

Thanks

Thanks @Dimitris_Goudis :slight_smile:

I have a setup with four related tables: Portfolio (available items), vendors, orders, ordered items (line items). In the latter, each row represents an ordered item.

I use a manual checkmark on the order record, which is looked-up by the related items of that order. Once the checkmark is set/true, the ordered items move into a new view, which triggers the automation to send emails to the vendors.

The automation sends one email per item, but I would like it to send one email by vendor. In another application I use a formula field to combine the OrderID and VendorID, which relates items from the same vendor dynamically / automatically (something Airtable doesn’t seem to support). I then use a conditional roll-up to only send one email for all items of one vendor.

What is your suggestion to recreate a similar functionality in Airtable?

I thought to run the link records by fields script once the checkbox field is clicked to relate the ordered items, but the script currently one supports matching to one record at a time aka won’t related many to many.

Hi @Ricardo,

I have a clear view of what you try to do, however to send you a solution I would like to run some experiments and trials. Would it be easy for you to share me a duplicated table with draft data inside to run those tests? You can send me in a message the sharing link. :slight_smile:

Thanks for your assistance, I just shared a reduced copy of the base via PM.