Eliminating Already Selected Enum Options in Child Table

[Fadhil_HARON]

Hi everyone,

I'm working on an app with two main tables:

Table 1: Contains a column [Student Name].

Table 2: Has an enum list column named [Absent Student], pulling options from Table 1 using the formula Table 1[Student Name]. Additionally, Table 2 has a child table named Table 3, with an enum column called [Absent Student Detail].

I'm looking to accomplish the following:

1. Populate the enum column [Absent Student Detail] in Table 3 with the options selected in the enum list [Absent Student] in Table 2. For example, if [Absent Student] has enumlist values Mary, John, and Peter, [Absent Student Detail] should offer any of these three options.

2. In the child table that i set as a part of Table 2 form, after adding a row by selecting a value in [Absent Student Detail], I'd like to remove that option from the available choices for subsequent rows in Table 3. For instance, if Mary is selected in the first row, only John and Peter should be available in the enum column for the next row.

Any guidance on achieving this would be greatly appreciated.

Thank you!

[WillowMobileSys · Accepted solution]

---

Full disclaimer - there isn't anything "wrong" with tracking the absent students as an EnumList column in the Classes table.  Just be aware that you will be having the users select each absent Student twice AND you will end up with two duplicate lists - when you create the Absence records they will have a Ref to the Classes table and a list of "Related Absences" will be automatically created by AppSheet.

Now...to answer your questions...

---

---

@Fadhil_HARON wrote:

Is there an expression I can use in the Absence Form to automatically populate the enum options with the same values as the enumlist in the Classes Table?

---

Yes,  Your Absence rows will likely have a Ref column to the Classes table.  You can use that to select any column value from the referenced Classes row.  So... in Student column of the Absences table, you can set the Valid_If to this expression:

[Class].[Absent Students]

As long as [Absent Students] was defined as EnumList with a base type of Ref to the Students table, then you will see only the [Absent Students} as choices in the Student column of your Absences Form.

---

@Fadhil_HARON wrote:

Will your earlier suggestion (the expression) still apply in this case with the "Absent" action method? Does it still work to prevent selecting an absentee who has already been marked in the Absence Form?

---

Yes, with adjustments to the key column in the Student table, it should work as is....but I am not perfect...I do make mistakes.  If you see an issue let me know and we can figure it out.

[WillowMobileSys]

I would recommend that instead of having Table 2 and Table 3.  Only have a single table named "Absences".  This table has the columns:

• Student - a Ref column to the Students table
• Date of Absence
• any of the other details you are including in Table 3.

You can then filter/group this table by Date to see all absent students on any particular day.  Also, you will automatically get a list of Dates a particular Student was absent.

You can still remove Students already marked as Absent with an expression such as (assuming [Student Name] is your key column😞

SELECT(Students[Student Name], 
       OR(
          [Student Name] = [_THISROW].[Student Name],            
          NOT(IN([Student Name], SELECT(Absences[Student], [Date of Absence] = TODAY(),
       )
)

 If Student Name is NOT the key column to your Students table then replace "Student Name" with the key column name.

Note:  the OR is there to allow editing of an existing row.  

I hope this helps!

[Fadhil_HARON]

Thank you and I apologize if my initial description was unclear. Allow me to provide a bit more context. In Table 2, i also have other column [Classes Column], and each row in Table 2 represents a specific class, and the [Classes Column] serves as the key. There are a total of 10 classes, each corresponding to a row in Table 2.

When I click to edit these rows, my aim is to select the absent students for that particular class. For instance, in the first row, I have an English class specified in the [Classes Column]. In the subsequent column, [Absent Student], I would select the three absentees: Mary, John, and Peter, who were all absent for the English class.

Now, what I'd like to do is list these three students in separate rows within a child table. This would allow me to record specific reasons for their absence.

I'm not entirely sure if my current approach is the most suitable. Pls suggest how to achieve this or i should try other approach.

[WillowMobileSys]

Ok, so I understand that Table 2 is a Classes table. Then Table 3 is your Absences table.

Then the question is how to get the entries into this Absences table.  

Having a choice list of students that are absent doesn't really gain much in app usability in this case.  Since your Absent use case requires inserting additional information other than just Student and Date, users will need to add each absence student-by-student anyway.   

I would recommend an "Absent" action button that you place within the Class row.  When the button is tapped, it launches the Absences Form view pre-populated with Today's Date and the Class.  The user then selects the Student and inserts a reason.  If possible, make the Reason column a choice list for quickest row entry.  With Class set as a Ref column, the Class row will automatically have a list of Absences for that Class.

[Fadhil_HARON]

Thanks. Pls let me ask more

I'll give the "Absent" action method using linktoform a try. However, if I decide to stick with an enumlist for tracking absent students in the Classes Table, here are my questions:

1. Is there an expression I can use in the Absence Form to automatically populate the enum options with the same values as the enumlist in the Classes Table? This would make it more convenient for users to select from the list of absentees, especially in a class with 30 students.

2. Will your earlier suggestion (the expression) still apply in this case with the "Absent" action method? Does it still work to prevent selecting an absentee who has already been marked in the Absence Form?

[WillowMobileSys · Accepted solution]

---

Full disclaimer - there isn't anything "wrong" with tracking the absent students as an EnumList column in the Classes table.  Just be aware that you will be having the users select each absent Student twice AND you will end up with two duplicate lists - when you create the Absence records they will have a Ref to the Classes table and a list of "Related Absences" will be automatically created by AppSheet.

Now...to answer your questions...

---

---

@Fadhil_HARON wrote:

Is there an expression I can use in the Absence Form to automatically populate the enum options with the same values as the enumlist in the Classes Table?

---

Yes,  Your Absence rows will likely have a Ref column to the Classes table.  You can use that to select any column value from the referenced Classes row.  So... in Student column of the Absences table, you can set the Valid_If to this expression:

[Class].[Absent Students]

As long as [Absent Students] was defined as EnumList with a base type of Ref to the Students table, then you will see only the [Absent Students} as choices in the Student column of your Absences Form.

---

@Fadhil_HARON wrote:

Will your earlier suggestion (the expression) still apply in this case with the "Absent" action method? Does it still work to prevent selecting an absentee who has already been marked in the Absence Form?

---

Yes, with adjustments to the key column in the Student table, it should work as is....but I am not perfect...I do make mistakes.  If you see an issue let me know and we can figure it out.

[Fadhil_HARON]

[Class].[Absent Students]

Thank you very much it works like what i want, although i am struggling with eliminating already selected value.

[WillowMobileSys]

It looks like you went with the "Absent Students" column route.  When I answered your second question, I didn't consider your first - that you would be limiting the list to the Absent students as the starting point.

So, you will need to change the expression to this:

[Class].[Absent Students]
-
SELECT(Absences[Student], AND([Class] = [_THISROW].[Class],
                              [Date of Absence] = TODAY())
)

If you still have trouble, let me know.

[Fadhil_HARON]

Everything is working perfectly now, but a new issue has emerged. Let's say in [Class].[Absent Students], I select two students as enumlist and then the both students will populate as enum options in the Absences [Student] field. The issue is that even after both of them are selected (and eliminated from enum options) the user can still add a new row with a blank value in the Absence[Student] column. I attempted to change different columns as keys, but the problem persists.The problem remains whether I open the Absence table with the Absent action or through the "new" button in the nested form.

[WillowMobileSys · Accepted solution]

Which column property did you apply the expression that filters the Students list - in "Valid If or "Suggested Values"?   If you place it in "Valid If" then the Student list will not show when the choice list is empty.

My preference in this situation would be to prevent launching of the Absence Form altogether when no Students are absent OR all absent students (so far) have been entered.  In your Absent action you can handle this very easily by updating the property "Only if this condition is true" with an expression like:

COUNT([Absent Students] - [Related Absences]) = 0

However, the "New" button is harder.  It uses the system ADD action and is NOT a row-level action so will not have access to row columns.  There is not a way, that I know of, to conditionally hide that button.  What I would do is DISABLE this action and force users to use your custom button only.  To do that simply insert "False" into the "Only if this condition is true" property of the system Add button for your Absences table.

I hope this helps1

[Fadhil_HARON]

---

@WillowMobileSys wrote:

My preference in this situation would be to prevent launching of the Absence Form altogether when no Students are absent OR all absent students (so far) have been entered.  In your Absent action you can handle this very easily by updating the property "Only if this condition is true" with an expression like:

COUNT([Absent Students] - [Related Absences]) = 0

---

Yes finally solved it all for me. Got so many new input from your responses. Thank you legend @WillowMobileSys