Scheduled Report: Deliveries Incomplete

[Sarah_Keown]

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

[Steve]

Replace this:

SELECT(Orders[Order No],

with this:

FILTER("Orders",

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

See also:

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

ORDERBY()

Sort row references

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[Sarah_Keown]

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

[Steve]

Please provide screenshots of both the error, and of the complete expression.

[Sarah_Keown]

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

[Steve]

Sarah_Keown:

The report has the following expression but im getting errors of invalid input/should generate list of ref values.

Please post screenshots of theses errors.

[Steve]

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?

[Sarah_Keown]

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

[Sarah_Keown]

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

[Sarah_Keown]

[Steve · Accepted 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])>>

[Sarah_Keown]

Thanks very much Steve the errors have gone now