Very much possible. Although, SWITCH might scale better here than IF blocks. You’ll be doing something like this:
SWITCH(
the first character of the record number,
if that first character is P,
use this price,
whereas if the character is S,
use this price,
otherwise default to this value
)
You can add as many combinations of “character letter, corresponding price” as you need if more letters or prices are added over time. Just add them before the default value with a comma at the end.
A potential solution would look like the code below. You’ll need to replace the field names (the words inside the curly braces) with the field names you’re actually using.
SWITCH( LEFT({Record Number},1), "P", {Painting Price), "S", {Sculpture Price} )
That last bit “” will return an empty string if no prices were found but you can replace that with some value as well.
Very much possible. Although, SWITCH might scale better here than IF blocks. You’ll be doing something like this:
SWITCH(
the first character of the record number,
if that first character is P,
use this price,
whereas if the character is S,
use this price,
otherwise default to this value
)
You can add as many combinations of “character letter, corresponding price” as you need if more letters or prices are added over time. Just add them before the default value with a comma at the end.
A potential solution would look like the code below. You’ll need to replace the field names (the words inside the curly braces) with the field names you’re actually using.
SWITCH( LEFT({Record Number},1), "P", {Painting Price), "S", {Sculpture Price} )
That last bit “” will return an empty string if no prices were found but you can replace that with some value as well.
Thank you very much @Zollie!!. Would it be possible to embed formulas inside the curly braces instead. At the moment there are two columns because P and D have different formulas to calculate price. I am looking to combine the two VALUE columns into a single one that detects if it is a P or a D based on a statement and applies the correct formula.
Thank you very much @Zollie!!. Would it be possible to embed formulas inside the curly braces instead. At the moment there are two columns because P and D have different formulas to calculate price. I am looking to combine the two VALUE columns into a single one that detects if it is a P or a D based on a statement and applies the correct formula.
The curly braces are part of the field name. I’m guessing that what you really meant to ask is whether or not you can replace the field name with another function, and the answer is yes. You don’t have to only return the value of a field. You can perform almost any operations you want to generate the value to be returned for each option.
Also, you can—and probably should—omit the closing empty string ("") from the example above. The SWITCH() function will default to returning nothing if none of the listed options are found. Because the other return values are numeric, returning an empty string as the fallback is a type mismatch. When building formulas, all options should return the same data type.
