Solved: Dependent Selects Display Value vs. Stored Value a...

rheric · 09-04-2020 08:56 AM

I am trying to create an app to track member participation for a community group. Each member belongs to a specific sub-group, and there is a weekly schedule of recurring meetings, where specific meetings are open to specific sub-group.

The data is structured as follows:

Table: Members (MemberID, Name, Group, other fields…)

M1, John Smith, Group A
M2, Jane Smith, Group A
M3, Bob Jones, Group B
M4, Sally Brown, Group C

Table: Schedule (MeetingID, Meeting Name, Group, Day, Start Time, End Time, other fields…) This data rarely changes and is more of a control table providing values for other views.

S1, Planning Meeting, Group A, Monday, 2 PM, 3 PM
S2, Design Meeting, Group B, Monday, 3 PM, 4 PM
S3, Membership Committee, Group C, Wednesday, 5 PM , 6 PM

Table: Attendance (ID, Date, MeetingID, MemberID)

1, 8/31/2020, S1, M1
2, 8/31/2020, S1, M2
3, 8/31/2020 S2, M3
4, 9/2/2020, S3, M4

I have the view and form for Members no problem - very straightforward, including a text field (not a ref field) using Valid_If to control the possible values for Members[Group]. What I can’t figure out is how to build the form for Attendance. Ideally, I’d like to be able to select a Meeting Name, a Date, and then a multiselect from a list of members who are elligible to attend the meeting. When I built the Attendance table and added it as a Form view, I got this (after a bit of tweaking):

I have two issues:

How do I create a multiselect for Attendees that:

DISPLAYS the Schedule[Meeting Name] values
STORES the Schedule[MeetingID] in the Attendance Table AND
FILTERS the list of Members based on the Schedule[Group] value for the selected Meeting

In the Attendance Table I need to store the MeetingID, not the Group. And I’m confused between the use of an EnumList with a Base of Ref versus a Ref field…

Ideally, I would like for the Attendance Form to create multiple rows in the Attendance Table when the Form is saved - one row for each Attendee. This way we can report on attendance for each Member individually.

Steve

Meeting should be of type Ref to Schedule. Th options displayed in the resulting dropdown will be the label column (presumably Meeting Name) values, and the value stored will be the key column (MeetingID) value. If you’d like the dropdown sorted, use a Valid If expression like this:

ORDERBY(Schedule[MeetingID], [Meeting Name])

Attendees should be a column of type EnumList of Ref to Members.

The Valid If expression for the Attendees columns should be along the lines of:

FILTER(
  "Members",
  IN(
    [Group],
    SELECT(
      Schedule[Group],
      ([MeetingID] = [_THISROW].[MeetingID])
    )
  )
)

Optionally wrap that in ORDERBY() if you want the list ordered.

The dropdown presented will display the values of each row’s label column (presumably Name) value. The selection rows will be stored as their key column (presumably MemberID) values.

EnumList of Ref allows for multiple Ref values; a simple Ref allows only one value. For Attendees, you may have more than one attendee per meeting, so you need an EnumList of Ref. For Meeting, there will be only one meeting per meeting, so you need a simple Ref. You could use an EnumList for Meeting, but it would add needless complexity and probably cause confusion. Similarly, you could use an Enum of Ref, which would also add complexity with no added value.

I’m not clear on “[t]his way we can report on attendance for each Member individually.” Please elaborate.

See also:

rheric

Steve,

Thank you!

To elaborate on “[t]his way we can report on attendance for each Member individually” - my feeling was to make querying which meetings an individual member has attended by making the Attendance table one row per member per meeting. That way we can query/filter by the MemberID column instead of needing to parse out a delimited string of text. And those strings might get quite long for larger gatherings!

For that question, I found this example app: https://www.appsheet.com/Template/AppDef?appName=multirow_creation_example-300884#Data.Columns.form_...

Not to complicate things (but requirements are NEVER complete, are they?), but the overall structure has changed slightly. Now they are requesting that we be able to store meeting organizers/coordinators in addition to Attendees.

I’ve done enough database work that I don’t want to store the Meeting coordinator in the Attendees table, which means a fourth table:

Table: Members (MemberID, Name, Group, other fields…)

M1, John Smith, Group A
M2, Jane Smith, Group A
M3, Bob Jones, Group B
M4, Sally Brown, Group C

Table: Schedule (ScheduleID (changed field name for clarity), Meeting Name, Group, Day, Start Time, End Time, other fields…) This data rarely changes and is more of a control table providing values for other views.

S1, Planning Meeting, Group A, Monday, 2 PM, 3 PM
S2, Design Meeting, Group B, Monday, 3 PM, 4 PM
S3, Membership Committee, Group C, Wednesday, 5 PM , 6 PM

New Table: Meeting (MeetingID (UniqueID()), ScheduleID, Date, Coordinator, other fields…)

T1, S1, 8/31/2020, Jeremy
T2, S2, 8/31/2020, Becky
T3, S3, 9/2/2020, Jeff

Table: Attendance (ID (UniqueID()), Date, MeetingID (pointing to Meeting table now), MemberID)

1, 8/31/2020, T1, M1
2, 8/31/2020, T1, M2
3, 8/31/2020 T2, M3
4, 9/2/2020, T3, M4

If I understand the field types in AppSheet, I should build it like this:

Meeting[ScheduleID] = Ref field, with Label Column for Schedule set to [Meeting Name], with key column [ScheduleID] stored.
Attendance[MeetingID] = Ref field pointing to Meeting table
Attendance[MemberID] = EnumList field of Base Ref pointing to Members

If the above is correct (fingers crossed), my next question is this - is it possible to create an ordered set of forms that would prompt the user to first create a Meeting record, and then on Save, go to the Attendance form and automatically use that MeetingID?

Thanks again!

Steve

Looks good to me!

It is, but you may just want to start with a “nested form”. In the Attendance table, in the MeetingID column, set Is a part of? to ON, then make sure the Related Attendances column is included/visible on your Meeting_Form view. If so, the user will be prompted to add Attendance rows from the Meeting form directly.

See also:

rheric

Steve,

Thank you again for your help. I’ve run into an issue with filtering the list of Members based on their Group membership. The original FILTER expression was for my original three table design, but there’s now an intermediate table between Attendance and Schedule (where Schedule[Group] determines which Members should be possible attendees). Basically, I would need to do something like a SELECT nested inside of a SELECT, but I don’t think this is possible:

FILTER(
  "Members",
  IN(
    [Group],
    SELECT(
      Schedule[Group],
      SELECT(
      Meeting[ScheduleID],
      ([MeetingID] = [_THISROW].[MeetingID])
      )
    )
  )
)

When I put that in the Expression, I get ‘SELECT has invalid inputs’.

Would it be better to do a calculated row that pulls Schedule[Group] into the Meetings table?

I can share the App, if that helps - how do I do that?

Steve

Need to wrap the inner-most SELECT() with IN():

FILTER(
  "Members",
  IN(
    [Group],
    SELECT(
      Schedule[Group],
      IN(
        [ScheduleID],
        SELECT(
          Meeting[ScheduleID],
          ([MeetingID] = [_THISROW].[MeetingID])
        )
      )
    )
  )
)

SELECT(Meeting[ScheduleID], ([MeetingID] = [_THISROW].[MeetingID])) finds all Meeting rows with a MeetingID that matches the current row, then produces a list of the ScheduleID values from those Meeting rows.
SELECT(Schedule[Group], IN([ScheduleID], ...)) finds all Schedule rows that have a ScheduleID value in the list of ScheduleID values from (1), then produces a list of the Group values from those Schedule rows.
FILTER("Members", IN([Group], ...)) finds all Members rows that have a Group value in the list of Group values from (2).

“Give me all Members in the Groups that have a Schedule for a Meeting with this MeetingID.”

rheric

Steve,

I took my own advice and created a virtual column that selects Schedule[Group] from the Schedule table, and then basically used the FILTER expression you had originally provided.

Worked like a charm!

The last part is preventing the default behavior within the Attendance table of writing all Attendees to a single, comma-delimited string in the AttendeeID field. I don’t want this:

I would like this:

I think I need a Workflow, because I don’t see an Action that would logically apply.

Any guidance would be HUGELY appreciated!

Thanks again!

EDIT: I found this example App: https://www.appsheet.com/Template/AppDef?appName=multirow_creation_example-300884#Behavior.Actions

However, that seems extremely inefficient - there is a separate action for each item in the array, so that solution has a hard limit of three items in its EnumList being split into separate rows. What I need is to LOOP through the values in AttendeeID n times, where n is the length of that array. I may have meetings with DOZENS of people!

Steve

Have you considered my earlier suggestion?

rheric

I did. Here’s what I see in the emulator when creating a new Meeting:

Clicking the New button at the bottom brings me to a filtered list of Members!

Exactly what I’m looking for, except for the last part - the data:

I found an example you had posted - Looping with Actions - I am going to try to pull out what I can use from that to finish this first version. I’ll probably create another table - MemberAttendance - that has the individual rows for each member.

Unless there’s a cleaner way to do it in the Attendance table?

Thanks again!

rheric

@Steve

Last question (I’m almost there!) - what is best practice for linking to another form when submitting a form AND passing specific values to the new form? In this app, I think I need to link from the Meeting form to the Attendees form and pass Meeting[MeetingID] and Meeting[Date] to the Attendees form where I can add who was at the meeting being created.

Using the nested form as you suggested is apparently not possible, because Actions cannot be triggered on nested forms. I’ve seen this asked about in other places but couldn’t find an answer.

I need the new row in the Attendance table to trigger a set of Actions that will write multiple rows to a secondary table - MemberAttendance - splitting out the EnumList of MemberIDs to one row per Member per Meeting.

Oddly enough, I had this working earlier today - my looping actions were firing off the nested form. But it looks like a big update was pushed and its no longer working? The visuals of AppSheet sure did change - and it looks great!

Thanks again!

Steve

If (1) the Is part of? property of the MeetingID column of the Attendance table is ON and (2) you add a new Attendance row by clicking Add from the Meetings form then (3) the MeetingID column of the new Attendance row should be automatically populated with the meeting ID.

To then automatically fill the MeetingDate column of the new Attendance row, the Initial value expression for the MeetingDate column can dereference MeetingID to get the meeting’s date from the Meeting row:

[MeetingID].[Date]

rheric

That was how I had it set up, but unfortunately it doesn’t seem possible to fire an Action off a nested form (at least not right now).

Would a Workflow be more appropriate?

Thanks!

Steve

I didn’t suggest using an action.

rheric

Sorry for the confusion!

I’m using Actions to split the Attendance row into multiple rows in a secondary table - MembersAttendance. I used one of the examples to build this out (Looping with Actions - https://www.appsheet.com/Template/AppDef?appName=LoopingwithActions-381190#Info.Spec).

It actually was working, with the Action being triggered off Save on the nested form. But it stopped working yesterday.

Dependent Selects Display Value vs. Stored Value and Filtering