Please verify that it generates a List and that the contents of the List are Ref values. Ref values

[Robbo]

Every time I change my app, it identifies 2 errors, yet the report has been working perfectly for years, and continues working. 

I am confused as to why. 

The code that is supposedly in error is as follows, however if I add the code as a variable to a table (my way of testing it), it works fine. [Worker] is the Key field but it is a calculated field in this case, a concatenation of two other fields. 

Orderby(Select(Workers[Worker],AND([Worker Type]<> “Suspended”,Count(Select([Related Tickets][Name],[Expiry]<=Today()+30))>0)),[Worker])

This is the other code that reports the same type of error. Here I have shown the full Error message that includes the full START expression I am using.

Start expression 'Select(Workers[Worker],AND(OR(Count(Select([Related Timesheets][Hours],[Work Date]>=Today()-2, true))>0,Count(Select([Related Labours][Hours],[Work Date]>=Today()-2, true))>0,Not(OR([Worker Type]="Suspended",[Worker Type]="_System"))),[Supervisor]=” CAMPBELL, Brian”),true)' should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the 'Key' column of the referenced table

Can anyone help me identify what the error is, and why it keeps working despite the reported errors?

[Go]

It seems like the character code for double quotes is wrong.

“Suspended” -> "Suspended"
” CAMPBELL, Brian” -> " CAMPBELL, Brian"

[Robbo]

Thanks for the comment, but I don't quite understand what you're suggesting. Following is the code. What is wrong with the double quotes? It appears to be the same character I use consistently. Can you clarify please?

<<Start: ORDERBY(Select(Workers[Worker], AND([Supervisor]="CAMPBELL, Brian",OR([Worker Type] ="Active",[Worker Type] = "Senior",[Worker Type] = "Supervisor")),true),[Worker])>>

[Go]

ORDERBY() is a function that returns a Ref, so the first argument must be a key item.
Will I get the same error even if I replace the code below?

ORDERBY(
  FILTER(
    "Workers",
    AND(
      [Supervisor]="CAMPBELL, Brian",
      OR(
        [Worker Type] ="Active",
        [Worker Type] = "Senior",
        [Worker Type] = "Supervisor"
      )
    )
  ),
  [Worker]
)

[Robbo]

I tried that and this is the message I get

'Weekly Expiring Ticket Report' task 'Weekly Expired Ticket Report' Body template. Expression 'Start: ORDERBY(  FILTER(    "Workers",    AND(      [Supervisor]="CAMPBELL, Brian",      OR(        [Worker Type] ="Active",        [Worker Type] = "Senior",        [Worker Type] = "Supervisor"      )    )  ),  [Worker])' is invalid due to: Unable to find column 'Worker'.

[Steve]

The errors are telling you that the Worker column of the Workers table is not the key column of the Workers table.

[Robbo]

I think it's set up correctly as Key

[Robbo]

It's odd because the reports both work. I only get a bug report if I save and Verify.

My fear is that it doesn't like a Key that's a composite field. I plan on changing that in time, but it's a big change. The Worker table was the first I created, when I was an absolute beginner at AppSheet. Rookie mistake. 

[stefanasks]

Is your Workers[Worker] a datatype REF or enumList with base type REF and does it point to the right table? If not, create another virtual column like Workers[Worker_2] with other settings and use this for your template filter expression or even another template bot for testing.

There are also sometimes templates delayed in saving. So For me I always add a little version number somewhere in the template to make sure I see the result of the latest template saved.

[Robbo]

Hi Stefanasks, thanks for your comment, but I am not sure what you're asking.

The report is using the following statement to select the records to use. As Steve noted,  it's extracting a list of what should be KEY values from the Worker column of the Workers table, but it thinks that the Worker column of the Workers table is not the key column of the Workers table.

The fact is that the workers table has a VC called Worker that is the Key field (see screen shot below). It is a TEXT field but other tables that use this field have it as a REF field. I now know I should have used UNIQUE() to generate a key, which would have made it possible for me to easily correct spelling mistakes and name changes in the Worker table, but as a rookie, I didn't think of that at the time.  

Orderby(Select(Workers[Worker],AND([Worker Type]<> “Suspended”,Count(Select([Related Tickets][Name],[Expiry]<=Today()+30))>0)),[Worker])