I have a base that contains records of artwork with record#s that contain letters. D for drawings, P for Paintings and S for sculptures. The prices for all the artwork is based on a formula derived from the dimensions of the art. Drawings and paintings have their specific formula to calculate value. At the moment I have two columns that show the price: one for the paintings with its specific formula and another one for the drawings with a formula of its own. I would love to create a single column with a formula with an IF statement that would apply one price formula if it finds a P in the record number and another one if it finds a D. Is it possible?. I don’t understand if it is possible to write a conditional to IF if it finds something. Any help or suggestions would be greatly appreciated.
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.