Selecting one record from list for report header

AppSheet AppSheet Q&A
1

I have a PDF report generated to display a list of entries in the Room Assignments table, with Room Assign ID as the key. It pulls in only a designated Team’s name based on [Report 1]=1. It works well and I am using this Select command to have those listed:

<<Start:ORDERBY(SELECT(Room Assignments[Room Assign ID],AND([Report 1]=1,[Departure Date]>=TODAY())), [Room Number],FALSE,[Last Name], FALSE, [First Name], FALSE)>><<[First Name]>> <<[Last Name]>>

It has a subsequent End command after the last column.

I want to have a report title above the list that displays the team name [Team] and want to use generally the same command. I tried this variation below to select any of the records in the result as the field [Team] is the same in all of them:

Housing Report - <<Start:ANY(SELECT(Room Assignments[Room Assign ID],AND([Report 1]=1,[Departure Date]>=TODAY())))>><<[Team]>><>

I keep getting this error message - it looks like it’s looking for a list of more than one item:

Task ‘Generate PDF Report’ Body template. Start expression ‘ANY(SELECT(Room Assignments[Room Assign ID],AND([Report 1]=1,[Departure Date]>=TODAY())))’ 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.

I also tried INDEX(“select command”, 1) and got the same error.
How do I display only the first record’s team name in the title of the report?

Thanks, Wick

2

Please try an expression something like

<<Start:TOP(SELECT(Room Assignments[Room Assign ID],AND([Report 1]=1,[Departure Date]>=TODAY())), 1)>><<[Team]>> << END >>

1 Like
3

Perfect! I wasn’t aware of the TOP command. Thanks so much for your help!

2 Likes
4

You are welcome.

Just to elaborate, << Start >> expressions expect a list and wrapping by ANY() or INDEX() creates a text value.

TOP() is a list of initial elements in the list.

Just as a variation, the following expression will also work.

<<Start: LIST(ANY(SELECT(Room Assignments[Room Assign ID],AND([Report 1]=1,[Departure Date]>=TODAY()))))>><<[Team]>> << END >>

2 Likes
5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.