Simple Multi-Event Attendance Using the Same Name and ID List

[mmulligan]

Good day all,

I would like to create an app where I can easily choose from a list of events, then see a list of searchable names and scannable IDs that when selected would automatically add that event title to the list of names and IDs. I got so far as to create two tables: (1) Student List with Student Name, Student ID, and Events Attended (Referencing the Events Table) and (2) Events List with one column for Event Title (someone could easily add more events of which they can choose).

I used the Events List as a card view with the hope that when you click on the Event Title it would pull up the Student List and I could create a button that would somehow add that event to their name showing that they attended. But I can't seem to get that list. I loved the Class Attendance template app and tried to mess with that, but it was not close enough to how I wanted this app to function and made it a bit harder to work backwards towards my goal. It worked off a list of names that were preassigned to courses.

Also, I wanted it to be as automatic as possible and attempted a form that would update the list after a quick ID scan, autosave and auto advance (or renew), but it required that I pick an event in the column each time.

I am a newbie when it comes to referencing tables and creating parent-child relationships, and figured this is my missing link (pun intended) and hoping to get some direction.

Thank you appsheet community!

[WillowMobileSys]

I have began this response many times but it gets quite lengthy....

This is the Classic many-to-many relationship problem.  You have a list of Events-any one can be attended by many students.  You have a list of Students-each could attend many Events.  There isn't any built-in feature to help handle a cross-reference between these two tables.  You'll need to implement one and HOW depends on your needs.  Any solution might require a lot of explanation but I'll give it a go....

You described that you want to pull up an Event, select a list of students and then see that Event listed under each Student as you view them in the app.   I will assume you wish to multi-select the Students rather than add them one-by-one.  I will also assume you want the ability to add events from a Students record.

Almost every many-to-many relationship (AppSheet, database or otherwise) requires a "join" table.  In AppSheet, you can handle simple cross-referenes using list columns, but a "join" will provide the best performance and user experience.  It does require some additional implementation.

To implement:

1)  Your "Events Attended" table is your "join" table.  A single row represents a single Event and a single Student that attended it. The table needs to have at least the the following columns:
        Event Attended ID - a unique ID value used as the row key
        Event - a REF column to the Events table.  Set the column property of "Is part of" true.  this will automatically provide an Inline table to the Events Details and Form view.  
        Student - a REF column to the Students table.

2)  Add to your Events table an Attendees column defined as EnumList with a base type of REF to the Students table.  This could be used as the "simple cross reference" I mentioned.  We will use it as a way to select Student easily but then on Save create all of the necessary Events Attended rows - see 3) below.

3)  Create a set of actions that takes the list of selected Attendees and adds them as individual rows to the "Events Attended" table. 

                   b)  add an action in the Employees action list to "Add a row to another table" the table being the "Events Attended" table.  You will need to use the INPUT() function pass the Event ID.

                   c)  add an action of "execute and action on a set of rows" on the Events table supplying the Attendees columns as the Referenced rows.  Select the INPUT parameter and assign the Event ID value.

4)  Attach the new Events action to the Form Saved behavior of the Events_Form.  Now, when the form is saved, it will take the Attendees list and create EVents Attended rows.  You will still have the option to add an Events Attended row directly  - if you wish.

5)  You should automatically see in the Students table a "Related Events Attendeds".  Use this column to show the list of Events for a Student.  You can use the "New/Add" button  on the Inline View to add a new Event Attended for that Student.  

6)  If you wish to multi-select events for a Student, you can mimic 1) - 4) but taylor them for adding Events to a Student.  This does mean a second "Add new row" type action to allow passing Student ID as a parameter.

The advantage of this setup using a join table, is that if you wish to carry additional details in the Events Attended table you can them.  For example, many each event has several Dates but you want to track which Date each Student attended,  you can simply add that as a column in the Events Attended table and track it there.  You couldn't EASILY do that with a simply cross-reference list.

I am sure there are many smaller details I have left out.  Please ask any questions.

I hope this helps!!

[mmulligan]

Good day. I have finally gotten around to trying this. I believe I created everything as it should be, but I am stuck on 3). What do you mean by using and selecting the Input() parameter? I am creating a new action for the Students table, I chose "Data: add a new row to another table using values from this row", table to add to is Events Attended, but then it says "Set these columns" and I am not sure which columns to select and what formulas to use. 

For 3b, I have a new action for the Events table, I chose "Data: execute an action on a set of rows", referenced table is Events, and I typed in Events[Attendees] as the referenced row. I am not sure what you mean by select the Input() parameter.

[mmulligan]

WillowMobileSys, thank you very much for your thorough reply! I will work on implementing this and come back with my results. I deeply appreciate the details!