How to get a "blank" answer when using "if" statements in a table

[sscochran]

I'm using a table for an invoice where an item is ordered, but more may be shipped than ordered. I have the formcalc expression figured out where I can get a 0 in the "backordered" column if the amount shipped is more than the item ordered, but I can't get rid of the 0 in the rest of the backordered column when there isn't any information in the "ordered" and "shipped" columns. Trying to get that column to be blank if there is no shipping or ordering information.

So far this is working for the math part. Not sure how to write a "blank" into the calculation

Sum(ordered-shipped)

if (Sum(ordered-shipped)>0)

then backordered=Sum(ordered-shipped)

elseif (Sum(ordered-shipped)<0)

then backordered=0

endif

Any advice will be greatly appreciated. Thanks!

[Former Community Member]

Supposing backordered is a numeric field,

  you can write backordered.rawValue = null in your else condition;

If backordered is a variable, then just write backordered = null. It will display the blank automatically.

kc

[sscochran]

I'm still getting a 0 in all the backordered columns when I'd like them to be empty. The form has more rows than I will be using, so when there is no information in the shipped and ordered row, I'd like the backordered row to be empty/blank. I've tried writing this at the end of my formula

elseif ordered=null and shipped=null

then backordered=null(but I really want it to be blank)

or elseif Table1.Row1.ordered=null and Table1.Row1.shipped=null

then Table1.Row1.backordered=null

And yes, backordered is a numeric field.

Any more advice? Thanks!

[Former Community Member · Accepted solution]

Did you check the patterns entered for your numeric field in the object pallette?

That might be cause for the zero. I tried on my local system and if I assign null, it's showing blank value.

kc

[sscochran]

Thank you! It's always something simple. I didn't check the Allow Blank or Allow Zero options under patterns. Got it and it works now! Fantastic.