Skip to main content

IF Formula to apply formula

  • July 20, 2020
  • 3 replies
  • 50 views
D

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.

3 replies

Z
Forum|alt.badge.img+18
  • Zollie
  • Inspiring
  • July 20, 2020

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.

D
  • David_Syre_Art
  • Author
  • New Participant
  • July 20, 2020

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.

Justin_Barrett
Forum|alt.badge.img+21

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.

    Terms & ConditionsAccessibility statement