I am working off a Wedding Planning template but would like to implement a table which sole purpose is to help me roughly calculate the estimated cost of each Venue + Caterer + Vendor combination.
Essentially, in my Venue Table, there is a Linked Data column that links to the Caterer Table where the cell is populated with Caterers that serve that specific venue. In my Estimated Cost Table, there’s a column for the Venue, one column for the Caterer, and one for other Vendors. The last column performs pulls data from 3 lookup fields from each of the Tables and sums them into an estimated cost. However, when I go to click on the Caterer lookup field, it’s listing ALL the Caterers but I’d like it to only show Caterers that only serve to the Vendor I selected. Is there a way to do this?
I realize this sounds confusing so I’ll provide an example:
Venue Venue Cost Caterers
Apple Field $1000 Caterer A, Caterer B, Caterer C
Corn Field $2000 Caterer B, Caterer D
Plum Farm $3000 Caterer E
Caterer Cost Venue Partnered
Caterer A $10 Apple Field
Caterer B $15 Apple Field, Corn Field
Caterer C $20 Apple Field
Caterer D $100 Corn Field
Caterer E $10 Plum Farm
In the Estimated Cost Table, I’d like to be able to do something like:
Option Name Venue Caterer Venue Cost Caterer Cost Est Total Cost
Apple + Caterer A Apple Field Caterer A $1000 $10 $1010
Plum + Caterer A Plum Farm Caterer E $3000 $10 $3010
When I click on the Caterer field, I’d like it to only show records that serve to the venue selected. Is there a way to do this?
I believe what you’re describing is literally a many-to-many Joint Table. Your “Option” table is adding calculated details about the combinations of Caterer + Venue that exist.
Only relatively recently a Script was added to the Apps that allows you to automatically add a new record to a Joint Table when you add a new Link between your two tables. Applied to your example here, when you add a link to a Caterer from your Venue table, it would automatically create a new record in your Option table and populate the record with the link to the Caterer and the Venue. If you configured your Joint Table in advance, you could set up the Lookup for the Caterer.Cost field, Venue.Cost field, and a Formula field that sums the two Lookups.
I haven’t used the script myself, but it should be available in the Apps under the Scripts tab. You should be aware though that if you change your mind and remove a Caterer from a Venue, you’ll have to manually locate the matching cross-join entry in the Option table and remove it.
Dynamic Link filtering is not an option within Airtable. Airtable hasn’t address it, I would just use cascading drop downs in excel. I know of no script that can facilitate this functionality.
You should reach out to firstname.lastname@example.org and ask them for this. Granted, I’ve been bothering them for 2 years now, crickets.