I feel like this is an easy thing but I couldn’t find it.
I have a column that is just a single select of options like Small, Medium, Large. Each selection needs to be used in a simple calculation such as “quantity times small” or “quantity times medium” and get different results.
Currently I did this with a linked table, I created a separate table where column 1 is “Medium” and column 2 is the value I can use in the calc. Then in my calculation I can reference the table like TABLE1.VALUE.
My issue is that I don’t want to use a separate table with a dynamic link. I want the selector and values to be self-contained in the table itself without using a separate table. This is because I need to be able to duplicate the page that has this table and then adjust the various values. If I use a separate linked table, then any edit to that table will effect all copies and I don’t want that. Each page needs its own unique values.
So how do I make a simple calculation “COL1 * COL 4 * MEDIUM” where I can somehow relate Medium to the number 20?
The column data type for a Select List doesn’t seem to have a relationship between a label and a value like an HTML select does. Or I’m missing something.
Can I do that on a free plan? Ha, don’t know. But it’s no more complex than I explained.
Quantity
Size
Total
1
Small (2)
$2
2
Medium (4)
$8
3
Large (6)
$18
All I need to do is make that 3rd column calculation, but I don’t want the sizes to be a reference to another table. It needs to be self-contained and editable in case I need to change the options that people can pick from.
Yes I could put the calculation number in its own column, but that disconnects it from the selector in the Size column. In other words the user shouldn’t have to change the size and then also change what the value is in another column. Or, if that extra column is dynamically calculated, I would need an easy way to edit it per-value in the size column, and hide the column since it doesn’t really need to be seen unless adjusting those values. I don’t know.
If the select box worked like HTML it wouldn’t be an issue, we would have both a label and a return “value” which could be used for the calcs. Maybe that’s something for a feature request?
You want the page to be duplicated, and you want each page to have a unique mapping for sizes, and yet you don’t want to have a separate table, so I’m assuming that means you want to embed the mapping directly into the dropdown column options — which looks like what you pictured.
So perhaps you want this formula for Total: thisRow.Quantity*RegexExtract(thisRow.Size, "\d+")
RegexExtract is a formula that uses powerful regular expressions to “extract” the string(s) that you need from a given string. In this case, \d+ extracts digits.
Thanks for the attempt. I’m not able to edit this so I can’t tell what you’ve done or what the calculation is like etc. I can’t change the Size column to switch between sizes on a row. The idea is that each row would select any given size and quantity and then I would get the total based on the “multipler” of the size they selected. Small is linked with 2, medium is linked with 4, etc. But each time I copy this page for a new project, I may need to adjust and change the available options and multipliers.
I assume it would work. It’s just not ideal since I have to maintain another column that I don’t want to see. And also have to edit forumula to add/remove/change items which is prone to error. And it’s two columns to edit whenever making changes.
Hoping another solution first before turning to this.
I thought this would work and I spent forever trying to get a regex that worked. Just using \d doesn’t grab decimals. And Coda seems to reject anything that has multiple captures or groups even if there is just one main capture group.
I need to capture 5, .5, 5.5, etc.
The one that finally worked was just [.\d]+. By using a character class of period and digit with +, it keeps the regex simple, and my text won’t ever contain complex numbers or more than one number or period etc. Should work for now.
That’s how I started, and was not what I ultimately wanted. Each page should be independent, not referenced to another page. Having two tables in the same page is just not pleasing either.
I went with the regex function trick for my needs.
That translates to
If size column equals Large then multiply quantity*15 if not … apply the same if but with medium and fin not …apply it with small if not… blank
So you will only have 3 columns, Quantity, Size and the column where this formula is
You will have to edit only the numbers in the formula, but you can change the size values and it will still work
This hides the values (15,10,5) but you can just write them in the size values and it will not affect, but you will have to manually update them if you change the formula
