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?