Help with IF Expression

Hi, I need help with and IF() expression (I think).
I’m trying to write an IF statement on a column [Order Status]. So I will try and explain what I need to achieve.
IF the value of [Order Date] isnotblank = “Order Submitted”, but if the value of [Order Date] isnotblank and the value of [Outstanding Order Total]=0 =“Order Received”. I’m sure it’s pretty simple but can’t seem to figure it out! Thanks in advance

Antony

0 24 1,160
24 REPLIES 24

LeventK
Participant V

@AntonyCole1979
You use this expression

IFS(
AND(ISNOTBLANK([Order Date]),ISBLANK([Outstanding Order Total])),"Order Submitted",
AND(ISNOTBLANK([Order Date]),[Outstanding Order Total]=0),"Order Received"
)

Thanks for your help,

Antony

Hi Again,

This is what I fully needed below but for some reason on the detail form it still shows “Order Submitted”, but in the edit form view it shows “Order Received”, when I get to the last part of the expression? I can’t figure out why it is showing differently in both views?

IFS(AND(ISBLANK([Order Date]),[Outstanding Item Total]=0),“Order Pending”,
AND(ISNOTBLANK([Order Date]),[Outstanding Item Total]>0),“Order Submitted”,
AND(ISNOTBLANK([Order Date]),[Outstanding Item Total]=0),“Order Received”
)

@AntonyCole1979
is your [Outstanding Item Total] column a Virtual Column which is calculating from some child records?

Several days ago, you indicated your logic was something different:

which would be:

IF(
  ([Outstanding Item Total] = 0),
  IF(ISBLANK([Order Date]), "Order Pending", "Order Received"),
  IF(ISBLANK([Order Date]), "", "Order Submitted")
)

Levent’s formula is equivalent to:

IF(
  ([Outstanding Item Total] = 0)
  IF(ISBLANK([Order Date]), "Order Pending", "Order Received"),
  "Order Submitted"
)

Hi, yours is correct. The only issue is when I need the order status to be value “Order Received” in the edit form it is “Order Received” but in the Form detail view it still says “Order Submitted” and doesn’t change. Also the underlying table is “Order Submitted”?

Ok iv’e worked it out (Almost). It won’t change to “Order received” Until I enter a value in another column [GFM Order Ref] then it changes.
I can work it out from here. Thanks for everyone’s help @Steve @Aleksi @LeventK

Yes it is. Iv’e tried a few things but still can’t get it to work. I have ref rows virtual column called Requested Items which refers to my child table by the Order ID, REF_ROWS(“Order Request Details”, “Order ID”) then a virtual column called Outstanding Item Total which has a sum, Sum([Requested Items][Outstanding Qty]). Hope this makes sense?

Thanks

LeventK
Participant V

@AntonyCole1979
Are you applying any security filters to either the parent or child table?

In the parent table i have usermail() set on one column to identify who created the record.

@AntonyCole1979
It’s possibly because of the order of the IFS expression as IFS expression stops the execution with the first conditional match. May be you can try with this:

IFS(
	[Outstanding Item Total]=0,
	IF(
		ISBLANK([Order Date]),"Order Pending","Order Received"
	),
	TRUE,"Order Submitted"
)

Thanks but this still doesn’t work. Its very strange that in the edit form it does say “Order received” when I get to the last part of the order but in the Form Detail it still says “Order Submitted” Is it possibly something to do with the form, or is it definitely the expression that is the issue?

@AntonyCole1979
Actually the form and the detail view are fetching the data from the same resource so I believe the form or the detail can’t be the problem here. For sure I have no any idea about your app’s structure, how have you binded your data, how you set-up your app so I’m just trying to make some good guess and trying to eliminate the common errata. Adding @Aleksi to this conversation, so may be he can have a better catch that I’m not noticing.

Thanks for your help, i really appreciate it. I would rather have this data work out automatically so i will stick around to see if @Aleksi has any other ideas of what it might be.

It sounds that you are using virtual column from another table somehow and that’s why you are seeing different result with these two views. Virtual columns are calculated only when you sync the data… the only except is if you use virtual columns with the related table structure.

Thanks for replying on this. Weird thing is the expression works up until the point it should change to “Order Received”. In the detail view it stays on “order submitted” but in the edit form view it shows “order received”? It shows “Order Pending” when it should, and shows “Order Submitted” when it should, just not “Order Received” is something conflicting maybe.

What expression are you actually using, @AntonyCole1979?

Hi, im using this
IFS(
[Outstanding Item Total]=0,
IF(
ISBLANK([Order Date]),“Order Pending”,“Order Received”
),
TRUE,“Order Submitted”
) which is what @LeventK suggested

I find this layout of the expression a little easier to follow:

IFS(
  ([Outstanding Item Total] <> 0),
    "Order Submitted",
  ISBLANK([Order Date]),
    "Order Pending",
  TRUE,
    "Order Received"
)

Steve
Participant V

Thanks. You also said:

which means that it correctly determines [Outstanding Item Total]=0 and ISBLANK([Order Date]) to reach “Order Pending”, and [Outstanding Item Total]<>0 and TRUE to reach “Order Submitted”. That it never reaches “Order Received” suggests [Order Date] is always blank.

I can see where its goinh wrong now, and i dont think it can be corrected easily. This is what i need to achieve, but i think we visited this on yhd furst exotession.
If [order Date] isblank and [outstanding item total]>0 “order pending”
If isnotblank [order date] and [oustanding total]>0 “order submitted”
If isnotblank [order date] and [outstanding total]=0 “order received”
[Outstanding item total] is a summed ref row of the child table by ref [order id]

[outstanding item total] and [outstanding total] are different things?

IF(
  ([Outstanding Item Total] > 0),
  IF(ISBLANK([Order Date]), "Order Pending", "Order Submitted"),
  IF(ISBLANK([Order Date]), "", "Order Received")
)

No sorry was a typo from me. Should only be [outstanding item total]

Top Labels in this Space