Forum Discussion

JamesTrory's avatar
JamesTrory
Qrew Assistant Captain
7 years ago

Use IF text lookup in a numeric formula

Hi,

I have a numeric formula that calculates the amount of hours that have passed if a date/time exceeds 24 hours.

If([Delivery (hours)]>24,[Delivery (hours)]-24)

I would like to add the condition that if the field [Priority] matches a certain text that it change the result. Something like this:

If(
[Priority]="Normal" AND [Delivery (hours)]>24,[Delivery (hours)]-24),
[Priority]="Rush" AND [Delivery (hours)]>12,[Delivery (hours)]-12)

However because I'm now calling on a Text field, QB complains about it because this is a Numeric formula.

If I convert the formula to Text, now I can't calculate the numbers.

Can anyone help me with a way to do this?
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    I'm confused, What is wrong with the formula you have as a formula numeric field.  Why do you feel it needs to not be a numeric field type?

  • JamesTrory's avatar
    JamesTrory
    Qrew Assistant Captain
    7 years ago
    After the first AND in the second row QB says "expecting ," and that there are "extra characters beyond the end of the formula". I assume that means that it doesn't like the AND function and concluded it must be because [Priority] is Text in a Numeric formula.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    no its miffed because the formula is not passing the syntax requirements due to mis-matched brackets

    I removed the trailing bracket on the line 2 below, as the IF does not end until the end of the formula.

    If(
    [Priority]="Normal" AND [Delivery (hours)]>24,[Delivery (hours)]-24,
    [Priority]="Rush" AND [Delivery (hours)]>12,[Delivery (hours)]-12)
  • JamesTrory's avatar
    JamesTrory
    Qrew Assistant Captain
    7 years ago
    I noticed the trailing bracket too and removed it, I still get the same error. I copy/pasted your formula above just to be sure... I really don't know then why this formula won't work.
  • JamesTrory's avatar
    JamesTrory
    Qrew Assistant Captain
    7 years ago
    I figured it out. It's that "AND" is capitalized. I made them lowercase and now it lets me save the formula without errors.

    I had no idea that this could cause conflict. Good to know.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      Both upper and lower case and AND will both work identically in a formula so that cannot have been the problem, but I'm glad it's now working for you.

      If you do not believe me, then change the AND to and, it will still work  
    • DanLadner1's avatar
      DanLadner1
      Qrew Trainee
      7 years ago
      I've definitely noticed that the syntax highlighting stuff doesn't always indicate an actual error, and that you can still save and have a working formula. It can be pretty misleading.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      Unfortunately it highlights functions with incorrect capitalization, such as isnull would be highlighted as it will be corrected to IsNull on save.  or dbid() will be highlighted.   So those syntax "errors" which will be corrected when the formula saves should not be flagged as warnings.