I have a table called Users, containing User_ID and name. I have another table called Events, containing Event_ID and date. I have a third table called Attendance, containing references to the User_ID and Event_ID and a status value.
In the App, I want to be able to show a list of users, click on one, then show a list of events and when I click on it, I want to be able to edit the status in the Attendance table.
In my Users view, I have set the Behaviour to call an Action that shows the Events view using LINKTOVIEW(“Events”). And in the Events view, I have set the Behaviour to call an Action that shows the Attendance, using the following: LINKTOROW(CONCATENATE(“1”, ": ", [Event_ID]), “Attendance_Form”). The Concatenate is because AppSheet has created an automatic key based on the two IDs (User and Event).
This works, except of course, the User ID is hard coded to 1. How can I replace this with the User ID selected in the Users view? Or is there a better way please?
With this setup, you should automatically get, in the Users and Events tables, a column named [Related Attendance].
Adding this column in the User view or the EVents view should automatically display an Inline Table of Attendance, which you can use to obtain the list of Events from the Users table or the list of attending users from the Events. If you wish to include additional Event or User details, then pull them into the Attendance record - I would use Virtual Columns.
The above makes use of built in features.
I do not recommend using a combination of User and Event as the Attendance row key. Instead, add another column as a dedicate row key - recommended for ALL tables. Using concatenated keys will cause you huge headaches down the road.
Many thanks for your advice. I have it working now, using the Related attendance. It was hard work to get the formatting right, but I got there in the end (although I would still like to know how to remove the View option at the bottom of the list of related attendances).
I have also taken on board your suggestion about adding a proper key to the table, rather than the concatenated keys.