If ISBLANK or ""

[DaveWillett]

In a word Template I need to return text if a value is not present (Blank or Empty text "")

IF(ISBLANK([OTHER]), "Please Price")

Can I be helped with the syntax?

Kind Regards

[1minManager]

<IF(ISBLANK([Other],"Please Price",[Other])>>

Simon@1minManager.com

[DaveWillett]

Thanks Simon:

<<IF(ISBLANK([OTHER],"**Not Priced**",[OTHER])>><<End>>

Tried but the report isn't firing?

[1minManager]

You said that you wanted it so it would work IN a template, not as the trigger.  I think you need to send some screenshots so I'm clearer what you're trying to do.

[DaveWillett]

Hi @1minManager Simon

This is the word template:

And the current code is on the right ( to the right of "<<[Code]>>" ) which is:

"

<<IF(OR([Code]="Other",[Code]="New",[Code]="CHECK"),[OTHER],"")>><<End>>"

I want that to change to:

<<IF(ISBLANK([OTHER],"**Not Priced**",[OTHER])>><<End>>

[1minManager]

Syntax issue, missing bracket.  Templates don't error unless you do a "Save & Verify" each time you change them.

<<IF(ISBLANK([OTHER]),"**Not Priced**",[OTHER])>><<End>>

[DaveWillett]

I think we posted at the same time Simon. I picked up on the typo's earlier but the results weren't as I initially posted. As below, if this was VB6 I wouldn't have a problem with a select case statement, however it's more of the below I need to happen. Hope I haven't caused too much inconvenience.

[DaveWillett]

Can I try to make this clearer, the expression:

"<<IF(OR([Code]="Other",[Code]="New",[Code]="CHECK"),[OTHER],"")>><<End>>"

I would like it to be:

IF OR

[CODE]="OTHER",[OTHER],""

[CODE]="NEW",[OTHER],"Please add a part price"

[CODE]="CHECK",[OTHER],""

[CODE]="REPAIR",[OTHER],"You have not enterred a repair line"

So this resembles a select case statement in the old VB/VBA world.

I'm really struggling with this.

[1minManager]

I'm strugggling to interpret your logic above.  I think you need to use an IFS() statement but I'm not sure.  What I don't understand about what you've put is if [CODE]="OTHER" then its [Other] otherwise its blank.  But if thats the case the app would never go to the next line.  Can you explain in more detail whaty you want and I'm happy to have another go 🙂

[DaveWillett]

Sorry about that Simon.

I had thought just the ISBLANK would have solved the riddle, but there are other criteria at hand. The statement:

"<<IF(OR([Code]="Other",[Code]="New",[Code]="CHECK"),[OTHER],"")>><<End>>"

Would be very easy to change to

"<<IF(OR([Code]="Other",[Code]="New",[Code]="CHECK"),[OTHER],"Add a price etc")>><<End>>" (just an example)

But other elements used such as "Paint", "Body" .... etc all inherit the FALSE text so I have to look at it differently. This is primarily for the [CODE] = "NEW", a user must add a New part price. So my go to solution would be Select Case of which I could add the FALSE text anywhere with any code.

As I understand it an IFS works differently and stops at the first occurance of the search. So I have to look at another way hence:

[CODE]="OTHER",[OTHER],""

[CODE]="NEW",[OTHER],"Tell the user this"

[CODE]="CHECK",[OTHER],""

[CODE]="REPAIR",[OTHER],"Tell the user that"

Does this make sense?

And thanks for the support

[DaveWillett]

I think IFS would work?

Example:

IFS(
ISBLANK([CODE])="NEW",[OTHER],"Please add a part price",
ISBLANK([CODE])="REPAIR",[OTHER],"",
ISBLANK([CODE])="OTHER",[OTHER],"Please add price for this item",
)

Obviously my syntax is not correct but I think it represents what I need.

[DaveWillett]

Does this look correct:

IFS(
ISBLANK([CODE])="NEW",[OTHER],"Please add a part price",
ISBLANK([CODE])="REMOVE & REFIT",[OTHER],"",
ISBLANK([CODE])="BODY",[OTHER],"",
ISBLANK([CODE])="REPAIR",[OTHER],"",
ISBLANK([CODE])="PAINT",[OTHER],"",
ISBLANK([CODE])="PAINT < 50%",[OTHER],"",
ISBLANK([CODE])="PAINT > 50%",[OTHER],"",
ISBLANK([CODE])="BLEND",[OTHER],"",
ISBLANK([CODE])="POLISH",[OTHER],"",
ISBLANK([CODE])="CHECK",[OTHER],"",
ISBLANK([CODE])="OTHER",[OTHER],"Please apply a price",
)

Many thanks

[1minManager]

Hi @DaveWillett 

No, the general format for IFS() is

IFS(

IfThis,ThenThat,

IfThis2,ThenThat2,

IfThis3,ThenThat3

)

The process checks each rule in turn.  If is finds a match then it give the result and exits. It doesn't go to the next rule.  Whereas IF() is more like

IF(This,ThenThis,ElseThis)

So IF has a sort of catchall result where it doesn't match the first rule.  But you can engineer the same sort of catchall with an IFS() if you structure it like this

IFS(

IfThis,ThenThat,

IfThis2,ThenThat2,

IfThis3,ThenThat3,

TRUE,CatchallResult

)

This works because if it gets to the last rule, TRUE is an automatic match so CatchallResult will then be the result.

In this:

IFS(
ISBLANK([CODE])="NEW",[OTHER],"Please add a part price",

...

your syntax is wrong because:

1 = Each IFS() line can only have the rule to check and the result.  Yours has 3 items

2 = ISBLANK([CODE])="NEW" is not allowed.  Its either

ISBLANK([CODE])

[CODE]=NEW"

AND(ISBLANK([CODE]),[CODE]=NEW")

OR(ISBLANK([CODE]),[CODE]=NEW")

Hope this helps.  If your still stuck, try writting in plain english what you want the rule to be.  Or if your UK based, email me and maybe we can have a chat.

Simon

[DaveWillett]

Thanks Simon, yes that makes perfect sense, I was hoping IFS() would accept more than two TRUE,FALSE so you've answered my theory on that.

In plain english, this is what should happen and maybe there is another way to acheive this. We can omit the ISBLANK as the rule in the app prevents the user from proceeding unless he adds that value from an ENUM so...

Example:

([CODE])="NEW",[OTHER],"Please add a part price",

If [CODE] = "New" then apply the value of [Other], if the user forgets this then return the text of "Please add a part price" on the word template.

([CODE])="REMOVE & REFIT",[OTHER],"",

If [CODE] = "REMOVE & REFIT" then apply the value of [Other], if the user forgets this then return the text of "" on the word template.

([CODE]) is an enum and the user can select from various text values. [Other] is an aoption to add a cost value - price, but it can't be mandated it has to be optional. The meaning behind the text replacement for [Other] is to highlite the user once he receives the email template that there is no value added and he should check whether he should add a value, it is not required in all cases, ie, replace with a zero length text "" or something meaningful such as "Hey, you forgot to add a value".

Does this make sense?

Man Thanks

[1minManager]

Ok, so you have so many options with what [CODE] could be that we have to use IFS().  The issue I now see is that the logic doesn't fit with what IFS() needs.  So if I put your 2 lines into an IFS() we get

IFS(

[CODE]="NEW",[OTHER],

[CODE]<>"NEW","Please add a part price",

[CODE]="REMOVE & REFIT",[OTHER],

[CODE]<>"REMOVE & REFIT",""

)

As I said above, IFS() looks at each rule in turn.  So if it doesn't match rule 1 where [CODE]="NEW" then its definately going to match rule 2 where [CODE]<>"NEW" (<> meaning Does Not Equal).  So it will never get to rule 3.  Do you see where I'm going here?

I think we need to need to expand the rules so that we can have a list of 11 rules and it be impossible for the data to match any 2 rules.

So some thing like 

IFS(

AND([CODE]="NEW",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],

AND([CODE]="NEW",OR(ISBLANK([Other]),[Other]=0)),"Please add a part price",

...

[DaveWillett]

Perfect sense Simon, I see exactly how this needs to be constructed. When I've put it together I'll post the final expression, I'm sure many others will come across the necessity going forward.

Many thanks for the extended support.

[DaveWillett · Accepted solution]

<<IFS(
AND([CODE]="NEW",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],
AND([CODE]="NEW",OR(ISBLANK([Other]),[Other]=0)),"Please add a part price",
AND([CODE]="REMOVE & REFIT",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],
AND([CODE]="REMOVE & REFIT",OR(ISBLANK([Other]),[Other]=0)),"",
AND([CODE]="BODY",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],
AND([CODE]="BODY",OR(ISBLANK([Other]),[Other]=0)),"",
AND([CODE]="REPAIR",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],
AND([CODE]="REPAIR",OR(ISBLANK([Other]),[Other]=0)),"",
AND([CODE]="PAINT",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],
AND([CODE]="PAINT",OR(ISBLANK([Other]),[Other]=0)),"",
AND([CODE]="PAINT < 50%",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],
AND([CODE]="PAINT < 50%",OR(ISBLANK([Other]),[Other]=0)),"",
AND([CODE]="PAINT > 50%",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],
AND([CODE]="PAINT > 50%",OR(ISBLANK([Other]),[Other]=0)),"",
AND([CODE]="BLEND",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],
AND([CODE]="BLEND",OR(ISBLANK([Other]),[Other]=0)),"",
AND([CODE]="POLISH",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],
AND([CODE]="POLISH",OR(ISBLANK([Other]),[Other]=0)),"",
AND([CODE]="CHECK",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],
AND([CODE]="CHECK",OR(ISBLANK([Other]),[Other]=0)),"",
AND([CODE]="OTHER",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],
AND([CODE]="OTHER",OR(ISBLANK([Other]),[Other]=0)),"Please apply a price"
)>><<End>>

And testing to see what happens 😁

[1minManager]

Awesome.  Two other quick pointers.  Remember to do a save & verify after modifying a template.  This forces Appsheet to check the template forumlas.  Also I've had it where template formulas with carriage returns are unpredeicatable so if it gets weird remove them e.g.

<<IFS(AND([CODE]="NEW",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],AND([CODE]="NEW",OR(ISBLANK([Other]),[Other]=0)),"Please add a part price",AND([CODE]="REMOVE & REFIT",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],AND([CODE]="REMOVE & REFIT",OR(ISBLANK([Other]),[Other]=0)),"",AND([CODE]="BODY",OR(ISNOTBLANK([Other]),[Other]>0)),[OTHER],AND([CODE]="BODY",OR(ISBLANK([Other]),[Other]=0)),"",...

[DaveWillett]

Thanks Simon, we are all up and running after verification, the carriages don't seem to be causing issues as yet, but I will test thoroughly before believing what I see.

Kind Regards

[Steve]

SWITCH(
  [CODE],
  "NEW", IF((([Other] + 0) <> 0), [Other], "Please add a part price"),
  ...,
  ""
)

[DaveWillett]

Thanks Steve. Always great contributions and solutions to be stored for later use.

many Thanks