LinkedIn
Twitter
Hey @Phil_Waite, I just wanted to chime in here and say something about SELECT() formulas inside SELECT() formulas.
ISNOTBLANK(
FILTER(
“Orders”,
AND(
[Outlet No] = [_THISROW].[Outlet No],
[Complete Order Total Inc Vat]>0,
[Delivery Date] = LOOKUP([Unique ID], “Running Sheet 2”, “Unique ID”, “Delivery Date” ),
[Order Status Calc]=“Open”,
IN([Route] ,LOOKUP([Unique ID], “Running Sheet 2”, “Unique ID”, “Route” ))
)
)
)
If we take out the ISNOTBLANK() part and just focus in on the FILTER() expression, another re-write of this would be:
SELECT(Orders[OrderID], AND(
[Outlet No] = [_THISROW].[Outlet No],
[Complete Order Total Inc Vat]>0,
[Delivery Date] = ANY(SELECT(Running Sheet 2[Delivery Date], [Unique ID] = [_thisrow].[Unique ID])),
[Order Status Calc]=“Open”,
IN([Route] , ANY(SELECT(Running Sheet 2[Route], [Unique ID] = [_thisrow].[Unique ID])))
))
You’ve got a main SELECT() with TWO sub-select() formulas inside. 
Generally speaking, it’s a bad idea to have a select() (or any port: LOOKUP(), FILTER() ) inside another SELECT() - the resulting cascade of calculations can REALLY slow things down inside your app, depending on the amount of data being parsed, the complications of the various formulas, etc.
If I’m interpreting your formula’s intention inside the slice, what you’re wanting to do is isolate out certain orders based on data from their order details right?
On of the things I like to do when building complicated multi-stage logic like this, is to create an “Order Status” virtual column that contains an App Formula that does these types of calculations - but on the order level.
It’s in the technical weeds on why this is a more efficient way of calculating things, but the short version is: Certain calculations are made a certain times, and others at others. If you can store the result of a calculation somewhere (typically in the form of a virtual column of some sort) then the system doesn’t have to re-do that calc over and over again.
I would see about creating a virtual column that would hold a version of this formula. A benefit from moving this into the Order table is that now you have easy and efficient access to the [Related Order_Details] column and the amazing feature called a “list dereference.”
As was suggested by:
As you can see in the " [Reverse Reference Column of Order Details in the Orders Table][Product Name] " part.
If you can move these calculations into a physical column, that’s even better; but this can only be accomplished in certain circumstances.
And in regards to the LOOKUP formulas inside your Filter():
Is there a way you could store that lookup result somewhere? What exactly is that part of the formula doing? If you could provide a little more detail on what that’s doing for the formula, maybe I can propose an efficient way to store it.
Anyways, sorry for the long essay of a post. 
I would use this as a sub-status column. I typically will have several columns involved in determining the status of a record.
- I’ll have a manual entry, where people can physically change the status - like admin types closing something that was left open or canceled.
- I’ll have formulas like what @Suvrutt_Gurjar suggested above, giving me an easy true/false to work with for if some criteria is met
- Then one final [Auto_Something_Status] (as I typically call it) that’s watching all the various parts and making a final determination.
The real benefit of splitting things up is that you can use them later on in other integrations or automations you might build. 
