Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

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

Avatar

Level 2

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!

1 Accepted Solution

Avatar

Correct answer by
Former Community Member

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

View solution in original post

4 Replies

Avatar

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

Avatar

Level 2

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!

Avatar

Correct answer by
Former Community Member

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

Avatar

Level 2

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.

The following has evaluated to null or missing: ==> liqladmin("SELECT id, value FROM metrics WHERE id = 'net_accepted_solutions' and user.id = '${acceptedAnswer.author.id}'").data.items [in template "analytics-container" at line 83, column 41] ---- Tip: It's the step after the last dot that caused this error, not those before it. ---- Tip: If the failing expression is known to be legally refer to something that's sometimes null or missing, either specify a default value like myOptionalVar!myDefault, or use <#if myOptionalVar??>when-present<#else>when-missing. (These only cover the last step of the expression; to cover the whole expression, use parenthesis: (myOptionalVar.foo)!myDefault, (myOptionalVar.foo)?? ---- ---- FTL stack trace ("~" means nesting-related): - Failed at: #assign answerAuthorNetSolutions = li... [in template "analytics-container" at line 83, column 5] ----