Forum Discussion

SheilaAllas's avatar
SheilaAllas
Qrew Trainee
8 months ago

Formula Help Request

I'm trying to create a formula that will read 6 different numeric fields and then decide which field to populate as a results. 

If Numeric field 6 is empty, populate Number field 5

If Numeric field 5 is empty, populate Numeric field 4

If Numeric field 4 is empty, populate Numeric field 3,

If Numeric field 3 is empty, populate Numeric field 2,

If Numeric field 2 is empty, populate Numeric field 1

At minimum, Numeric field 1 will always have a value. 

Any help you can provide is greatly appreciated.  I've tried many things and nothing is working.

Thanks

Sheila Allas



------------------------------
Sheila Allas
------------------------------

2 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    8 months ago

    Do you not have a need to show Numeric Field 6? Such as if Numeric field 6 is not empty show it? 

    In that case I would do: 

    if( 

         not isnull(field 6), field 6,

         not isnull(field 5), field 5,

         not isnull(field 4), field 4,

         not isnull(field 3), field 3,

         not isnull(field 2), field 2,

    field 1)

    If you don't care about the value of field 6 and actually want to blank it out if its present, easiest way I'd suggest would be: 

    if( 

         not isnull(field 6), null,

         not isnull(field 5), field 5,

         not isnull(field 4), field 4,

         not isnull(field 3), field 3,

         not isnull(field 2), field 2,

    field 1)

    Both formulas are reading it that in descending order of fields 5 to 1 that the first time it finds a value then it stops and keeps that value. You should also ensure that you either 1) are enabling that blank values mean null instead of 0 in the settings OR you can convert the formula to use the NZ() != 0 syntax. So instead of: 

    not isnull(field) you would use Nz(field) != 0



    ------------------------------
    Chayce Duncan
    ------------------------------
    • SheilaAllas's avatar
      SheilaAllas
      Qrew Trainee
      8 months ago

      Thank you that was very helpful.



      ------------------------------
      Sheila Allas
      ------------------------------