Reference Table with Values for Formulas in Other Tables

[tom_pandsmarine]

Hi all,

I’m creating a quotes table with multiple formula fields which include prices which I want to be able to change easily.

At the moment, the figures in each of my formulas would have to be changed after a price increase which could lead to some figures getting missed, breaking formula etc. I had it in mind to create a separate prices table which would house all my prices which could be referenced in the formula (via a link and lookup). That way I would only need to change the value in the prices table and the formulas would have their new values in the quotes table.

This would mean however that I would need to link each record in the quotes table to the prices table…

Am I missing something obvious here? It feels like there should be a better way of doing this…

[Jeremy_Oglesby]

You’re not missing anything - this is definitely the best way to do it.

I recently wrote a response to somebody about this here:

How to look up a payrate? Ask the community

Unfortunately, @William_B, you’re going to have to set your “Payrates” table up horizontally instead of vertically. Think of each Record (row) in the “Payrates” table as a “Pay Schedule”. Initially, you only need one, your current pay schedule/rates. You’ll need to create a Field (column) for each “Tier” that could be matched against, and it’s percentage value as the Value for that “Pay Schedule”. The advantage of this is that you can change your pay structure in the future by adding a new “Pay…

The process of linking every record to your fee schedule isn’t so bad as it sounds, at least to my mind. It becomes a pretty quick process of just hitting “Enter” twice if you set it up so that your active schedule is always the top record.

[tom_pandsmarine]

Thanks for clarifying. It will take a long time to set up but will save time editing future price increase/decreases.

[W_Vann_Hall]

As @Jeremy_Oglesby points out in his payrate post, one of the issues with implementing rate sheets/pricing schedules/etc. is deciding how to get the correct value from the rate sheet into the formula used to calculate price. Being one of those guys with a hammer to whom everything looks like a nail, I often end up passing values around as key:value pairs, often concatenated into a longer string. My variation on Airtable’s standard Product Catalog template doesn’t include such a concatenation step, thankfully, but it does rely on key:value pairs. Depending for how many different items you need variable prices, it might prove the easiest way to pass and reference prices.

[tom_pandsmarine]

Thanks @W_Vann_Hall. I’ll have a look at your variation and Airtables Product Catalog. I’m fairly new to databases and am unsure what you mean by key:value pairs. If I can’t see what your up to by looking at your base I’ll come back to you about it if thats okay :slightly_smiling_face:

[W_Vann_Hall]

No problem. A key:value pair is what it sounds like: A key indicating what the value represents, and the value itself. In this case, each item has one or more prices, each related to a specific price schedule. For instance, in the base I referenced earlier, there are two key:value pairs associated with the Barcelona Chair: 01:5429 and 02:5836.18. In practice, the two values are stored in a single string: 01:5429|02:5836.18|. [The vertical bar character ('|') is used to mark the end of a value and the beginning of the following key.]

[tom_pandsmarine]

Got it - many thanks.