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
@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โ?
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
@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"
)
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]
User | Count |
---|---|
43 | |
28 | |
24 | |
24 | |
13 |