Reference Table with Values for Formulas in Other Tables


#1

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…


#2

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

I recently wrote a response to somebody about this here:

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.


#3

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


#4

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.


#5

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 :slight_smile:


#6

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.]


#7

Got it - many thanks.