Discussions

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

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

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