Mapping two different fields form another table to calculate final costs.

[kdburns]

Work with print media plans and I have an Excel workbook I've used for years to calculate costs. There are many variables with cost calculations and I will stay with this workbook however I'd like to export the final costs for each publication at each page size for import to Airtable to automatically calculate the costs based on each buy. 

Most buys consist of two components. A full page ad and a small page that accompanies the ad for legalese. 

Attached is screen capture of my media planning table and costs table with how I am trying to map costs. Is what I'm trying to achieve possible and/or is there a better way? I tried searching for a solution first and could not locate a similar request so if you know of link to send me to that would be great. 

Thank you in advance. 

[MatthewAT]

I believe what you want is should be easy to do.

Based on the screenshot you share you want to add 2 new Lookup Field columns that pull the prices from the Contact List to the Master Media Plan. From there a Formula Field will do the math.

I do have a few questions, though. I'm assuming you have multiple prices to pull from. Am I correct? A price for full page and a price for 1/3 page? If that's the case, then you're gong to have to have multiple records to achieve that. I would suggest pulling your prices into a new table that references both of your existing tables.

If you'd like to talk for me to walk you through it pick a time Zaporator 

[kdburns]

I think it would be too, I'm so Excel and FileMaker oriented, that I'm likely overthinking it.

Yes, there are multiple prices. A full page ad could be Run of Book, Cover 2, Cover 4, Far Forward and myriad of other positions which all have different costs. The smaller pages for legalese range from a full page to 1/2 page, 1/3 page, 1/4 page and 1/8 page. Any combination of the full size page + legalese could be purchased. 

Right now I have several test tables trying different ideas and I'm sure I can make something work, but is it the best practice? I'm going to dive in this a bit more and I might reach out after the holiday. Thanks!

[MatthewAT]

I think Airtable would be more suitable than Excel, as it will be more dynamic.

A simple restructure should give you the results you want. Link the tables using the below structure (more or less).

Contact List <-> Price List <-> Media Plan

Contact List has a list of all your customers

Price List would have prices listed. Each of the variations would be 1 record, and would be tied to the customer. I would also add dates and maybe an active/inactive field. That way you can maintain historic data.

Media Plan would in turn be tied to the Price List. It would pull the prices (along with any other info you need) from the Price List using a Lookup Field.

I look forward to talking. Happy Labor Day.