Problem with report template expression

AppSheet AppSheet Q&A
, ,
1

I am attempting to list out the stores with at least two people that have “passed” a certain training session. I’m getting an error that I cannot compare List with Number. I’m not sure how to accomplish this. If it helps, there is a ref between the Schedule table and the Stores table, but I am unsure of how to leverage that in this situation directly. Here’s the expression I have at this time:

<<start: orderby(select(Stores[org ID],count(select(Schedule[Event ID],and([location]=[_thisrow].[org id],[event type]=Glue Pull Training GA, [All Pass]=Yes),false)>=2)),[tt email])>>

2

You should have …false))>=2

1 Like
3

Thanks, Aleksi, but I just figured it out after beating my head against the wall. I needed to wrap the count in an “if” function:

<<start: orderby(select(stores[org ID],if(count(select(Schedule[Event ID],and([location]=[_thisrow].[org id],[event type]=Glue Pull Training GA, [All Pass]=Yes),false))>=2,true,false)),[tt email])>>

That said, it doesn’t produce the intended results yet, but Appsheet accepts the code as valid and it will execute now. Gotta work on my logic more, I suppose.

4

Why do you need the IF statement? The result for the COUNT(…)>=2 is either true or false already.

1 Like
5

Without the if(), I would get the error listed above about not being able to compare a list with a number, no matter how the parenthesis were configured. I tried every possible combination.

1 Like
6

Is the expression assistant throwing that error or when you save it?

1 Like
7

Put quotes around Glue Pull Training GA.

1 Like
8

Or.. when using [OrgID], are you referring to Stores table or to the record you have triggered this Bot?

1 Like
9

Thanks for the assist. I ended up completely changing approaches and moved the calculation of the number of course completions into the store record in Appsheet as a virtual column and that resolved the issues. I believe it was related to the fact that I had two nested select() functions and there was a need to refer back up the chain that was causing some problems.

2 Likes