Scheduled Report: Deliveries Incomplete

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 :slight_smile:

Replace this:

SELECT(Orders[Order No],

with this:

FILTER("Orders",

Best to always use FILTER() with ORDERBY(), rather than SELECT().

See also:



2 Likes

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]>>

1 Like

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?

1 Like

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

1 Like

I will try and get the error screenshots when it is safe for the user, and let you know asap thank you

1 Like

1 Like

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])>>
4 Likes

Thanks very much Steve the errors have gone now :slight_smile:

1 Like