Hi all, having some expression issues on a scheduled report.
The report is to go out every evening at 6pm, and include a list of all Deliveries that have been processed as โDelivery Incompleteโ - this is so a follow up can be actioned.
At the minute I need to include everything processed from the previous day i.e. ETA = TODAY - 1, but in case a delivery is early I need to include anything in future also i.e. ETA > TODAY.
So if a delivery was due on the 20th August but came on the 4th incomplete, this needs to be flagged in the report asap - it cannot sit unattended it should be addressed immediately.
The report has the following expression but im getting errors of invalid input/should generate list of ref values.
I tried adding two different conditions into one AND statement but nothing came through in those reports, can anyone advise me here?
<<Start:ORDERBY(SELECT(Orders[Order No],AND([Order_Status]=โDelivery Incompleteโ,[Mech/Elec]=โMechanicalโ,[ETA]=TODAY()-1),AND([Order_Status]=โDelivery Incompleteโ,[Mech/Elec]=โElectricalโ,[ETA]>(TODAY()),TRUE),[ETA])>><<[Order No]>>
Thanks, Sarah
Solved! Go to Solution.
Whenever faced with multiple errors, work to resolve them from top down, as the top errors sometimes cause other errors, so solving the top may solve others. I suspect thatโll be the case here.
Letโs try this expression:
ORDERBY(
FILTER(
"Orders",
OR(
AND(
[Order_Status]=โDelivery Incompleteโ,
[Mech/Elec]=โMechanicalโ,
[ETA]=TODAY()-1
),
AND(
[Order_Status]=โDelivery Incompleteโ,
[Mech/Elec]=โElectricalโ,
[ETA]>TODAY()
)
)
),
[ETA]
)
as your <<Start>>
expression:
<<Start: ORDERBY(FILTER("Orders", OR(AND([Order_Status]=โDelivery Incompleteโ, [Mech/Elec]=โMechanicalโ, [ETA]=TODAY()-1), AND([Order_Status]=โDelivery Incompleteโ, [Mech/Elec]=โElectricalโ, [ETA]>TODAY()))), [ETA])>>
Replace this:
SELECT(Orders[Order No],
with this:
FILTER("Orders",
Best to always use FILTER() with ORDERBY(), rather than SELECT().
See also:
Thanks Steve, I have switched this over but Iโm still receiving the errors as before, is there anything incorrect in the expression such as the double AND that would cause issue?
<<Start:ORDERBY(FILTER(Orders,AND([Order_Status]=โDelivery Incompleteโ,[Mech/Elec]=โMechanicalโ,[ETA]=TODAY()-1),AND([Order_Status]=โDelivery Incompleteโ,[Mech/Elec]=โElectricalโ,[ETA]>TODAY()),TRUE),[ETA])>><<[Order No]>>
Please provide screenshots of both the error, and of the complete expression.
I have had to disable the report as the errors were causing issues with our users. I have a screenshot of the template and the full expression below
<<Start:ORDERBY(FILTER(Orders,AND([Order_Status]=โDelivery Incompleteโ,[Mech/Elec]=โMechanicalโ,[ETA]=TODAY()-1),AND([Order_Status]=โDelivery Incompleteโ,[Mech/Elec]=โElectricalโ,[ETA]>TODAY()),TRUE),[ETA])>><<[Order No]>>
Please post screenshots of theses errors.
Your original expression is invalid. Here it is reformatted for clarity:
ORDERBY(
SELECT(
Orders[Order No],
AND(
[Order_Status]=โDelivery Incompleteโ,
[Mech/Elec]=โMechanicalโ,
[ETA]=TODAY()-1
),
AND(
[Order_Status]=โDelivery Incompleteโ,
[Mech/Elec]=โElectricalโ,
[ETA]>(TODAY()
),
TRUE
),
[ETA]
)
SELECT() take at most three arguments but yours has four. Why is there a second AND() expression?
I originally had it in one AND expression but the reports didnโt generate any results and I could see there should have been output
I will try and get the error screenshots when it is safe for the user, and let you know asap thank you
Whenever faced with multiple errors, work to resolve them from top down, as the top errors sometimes cause other errors, so solving the top may solve others. I suspect thatโll be the case here.
Letโs try this expression:
ORDERBY(
FILTER(
"Orders",
OR(
AND(
[Order_Status]=โDelivery Incompleteโ,
[Mech/Elec]=โMechanicalโ,
[ETA]=TODAY()-1
),
AND(
[Order_Status]=โDelivery Incompleteโ,
[Mech/Elec]=โElectricalโ,
[ETA]>TODAY()
)
)
),
[ETA]
)
as your <<Start>>
expression:
<<Start: ORDERBY(FILTER("Orders", OR(AND([Order_Status]=โDelivery Incompleteโ, [Mech/Elec]=โMechanicalโ, [ETA]=TODAY()-1), AND([Order_Status]=โDelivery Incompleteโ, [Mech/Elec]=โElectricalโ, [ETA]>TODAY()))), [ETA])>>
Thanks very much Steve the errors have gone now
User | Count |
---|---|
35 | |
34 | |
26 | |
23 | |
18 |