Formula Help Request

Question

I'm trying to create a formula that will read 6 different numeric fields and then decide which field to populate as a results.&nbsp;
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.&nbsp;
Any help you can provide is greatly appreciated.&nbsp; I've tried many things and nothing is working.
Thanks
Sheila Allas------------------------------Sheila Allas------------------------------

chayceduncan · Answer

Do you not have a need to show Numeric Field 6? Such as if Numeric field 6 is not empty show it?&nbsp;
In that case I would do:&nbsp;
if(&nbsp;
&nbsp; &nbsp; &nbsp;not isnull(field 6), field 6,
&nbsp; &nbsp; &nbsp;not isnull(field 5), field 5,
&nbsp; &nbsp; &nbsp;not isnull(field 4), field 4,
&nbsp; &nbsp; &nbsp;not isnull(field 3), field 3,
&nbsp; &nbsp; &nbsp;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:&nbsp;
if(&nbsp;
&nbsp; &nbsp; &nbsp;not isnull(field 6), null,
&nbsp; &nbsp; &nbsp;not isnull(field 5), field 5,
&nbsp; &nbsp; &nbsp;not isnull(field 4), field 4,
&nbsp; &nbsp; &nbsp;not isnull(field 3), field 3,
&nbsp; &nbsp; &nbsp;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:&nbsp;
not isnull(field) you would use Nz(field) != 0------------------------------Chayce Duncan------------------------------

sheilaallas · Answer

Thank you that was very helpful.------------------------------Sheila Allas------------------------------

Forum Discussion

Formula Help Request