List subtraction help

[flan828]

I have a table called attendance_table. in attendance_table the columns student_id, acadamy, date and present are of consequence. student_id is of type enumlist base type ref student_table. present is of type yes/no, date is of type date. acadamy is of type ref acadamy_table.

when I take attendance from the add attendance view the list is filtered by the acadamy chosen using FILTER(“STUDENT_TABLE”, ([ACADAMY] = [_THISROW].[ACADAMY_ID])) in the student_id column.
multiple student_ids can be selected. date is today(). I need the present column to equal true for all the students selected. for all the students that are not selected in the filtered list, I want present to be false.

I’ve been trying to figure this out for a few days and have read several articles and tried calling support to help as well. I haven’t been able to figure this one out. If someone has a good example. I think what I am trying to do is use a filtered list subtraction… If that makes sense.

Thanks for the help.

Flan

[WillowMobileSys]

It’s not exactly clear how you are using the [Present] column and how it fits into your app. Also, I assume this [Present] column is a Yes/No type?

But based on what you have described, all you need to do is set the App Formula for the [Present] column like so:

IN([Student ID], FILTER(“STUDENT_TABLE”, [ACADAMY] = [_THISROW].[ACADAMY_ID]))

If the Student of the current row is in the filtered list, the value is TRUE, otherwise it’s FALSE.

[flan828]

yes present is true/ffalse
and thank you . I been trying to figure this out for days.
Ill let you know how it works.

[flan828]

this is what my form looks like to take attendance. I select the academy and the student_id(s) are filtered based on that. The student_id is enumlist because I want to be able to choose more than one student.
So if I have lets say 20 students and I select 10 of them. The ten student’s present field should be marked as true. the other 10 that are not selected, those student’s present field should be set to false.
I’ve tried all sorts of behavior actions and bots and all sorts of formulas. I just haven’t got the knowledge to pull all that together. Someday I may, just not today.

[Steve]

flan828:

The ten student’s present field should be marked as true. the other 10 that are not selected, those student’s present field should be set to false.

It sounds like you want an action to be triggered automatically that will update some other table (student_table?) to reflect the attendance logged from this form. You’ll need several actions:

Update attendance for today for this student

• For a record of this table: STUDENT_TABLE
• Do this: Data: set the values of some columns in this row
• Set these columns:

  • present:

    ISNOTBLANK(
      FILTER(
        "ATTENDANCE_TABLE",
        AND(
          ([_THISROW].[ACADAMY_ID] = [ACADAMY_ID]),
          ([_THISROW].[DATE] = TODAY()),
          IN([STUDENT_ID], [_THISROW].[STUDENT_ID])
        )
      )
    )
    

Update student attendance for these students

• For a record of this table: ATTENDANCE_TABLE
• Do this: Data: execute an action on a set of rows
• Referenced Table: STUDENT_TABLE
• Referenced Rows: [STUDENT_ID]
• Referenced Action: Update attendance for today for this student (from above)

Set the Form Saved event action for the ATTENDANCE_TABLE form to Update student attendance for these students (the second of the two actions above).

[flan828]

First let me say thank you for the help.
Let me be a bit more succinct.
I have lets say 50 students in total between two academies. In the aforementioned form, I select the academy and the student_id(s) are filtered based on that academy selected. The student_id is enumlist because I want to be able to choose more than one student.
So if I have lets say 20 STUDENT_IDs in the STUDENT_ID Enumlist filtered by the academy, and I select 10 of them form the enumlist STUDENT_ID.

The 10 student’s selected [present] field in the ATTENDANCE_TABLE should have the value of “TRUE.” The other 10 in the STUDENT_ID enumlist that are not selected, those student’s [present] field in the ATTENDANCE_TABLE should have a value of “FALSE.” (This is where I was thinking of using the list subtraction. However, using list subtraction here gives me ALL 30 students not selected. I want just the 10 left over from the filtered enum list.) (student_table[_thisrow]-[STUDENT_ID]) <— was the formula that I was using for the list subtraction.

I am also using @Steve 's Looping FAQ: add row per value in EnumList to update the ATTENDANCE_TABLE so that each student_id has a separate record for the days that they attend.

Thanks again for the help.

[Steve]

Did you try my suggestion?

[flan828]

I did. It didn’t work mostly because of what I believe was my inability to communicate what I was trying to accomplish.
right now I am updating the attendance_table[present] field by using the “add row per value in enumlist.” I am setting the attendance_table[present] field to ‘true’ for the students that get checked in the student_id enumlist. What I need is for the attendance_table[present] field to get the value of false for all the students in that same list (the filtered by acadamy list) that are not checked off.
for example
if the acadamy is “albany”
the date == today()
student_id == sarah, chet, charlie, and joe.
I check in the student_id enumlist sarah, chet and charlie.
Joe is unchecked.
when I hit save, the present table should have
acadamy = the id for albany,
date = today’s date
student_id = sara’s id
present = true

the same for chet and charlie.

Joe’s record should look like this:

acadamy = the id for albany,
date = today’s date
student_id = joe’s id
present = false

[WillowMobileSys]

flan828:

The 10 student’s selected [present] field in the ATTENDANCE_TABLE should have the value of “TRUE.” The other 10 in the STUDENT_ID enumlist that are not selected, those student’s [present] field in the ATTENDANCE_TABLE should have a value of “FALSE.”

I would like to offer a different design approach that may help make things easier.

It seems for an Academy, that you will want to track daily attendance for each Student regardless if they were present or not. This way you can easily present a view of an individual Student and clearly see their attendance history.

So, I would recommend a slight change. In your Form show only the Academy and Attendance Date. When you tap Save, pre-insert ALL the rows in the Attendance table for each Student, set the Initial Value for the Present column AND THEN automatically show these newly inserted rows in a Table View set to Edit Mode. The teacher/faculty member can then simply walk down the Attendance rows checking each as Present or skipping if Not Present. When done checking attendance, tap Save to update all rows at once.

Table in Edit mode - just scroll down list to mark as Present

Example Table after Save - Using Yes/No labeling to show Present/Not Present values

[flan828]

please explain a bit further as I don’t understand “pre-populate” and “edit mode”

Thanks,
Flan

[flan828]

how did you get the checkboxes next to present

How to add a checkbox to a table view Tips & Tricks 💡

The question of how to add a checkbox to a table view has come up before: The answer (attaching an action to a blank virtual column) was provided but as I don’t think the implementation is completely self evident, I thought I’d provide a little example and explain the steps one needs to follow to implement it. First, here’s an image to illustrate the goal: [28] The empty checkbox and the checked checkbox are the same action. When the action is invoked it changes a column called “Check” fr…

[WillowMobileSys]

flan828:

please explain a bit further as I don’t understand “pre-populate” and “edit mode”

Sure!

By “pre-populate” I simply mean to add, automatically, rows that are no yet fully completed. For instance, as described above, on the Form fill in the Academy and the Date and then tap Save. Actions that are attached to the Form Saved behavior will then automatically create an Attendance row for each student in that Academy assigned with the chosen Date.

“Edit mode” - Table view have a switch for Quick Edit that can be turned on (see below). Quick Edit allows the table to be edited directly without having to go into each row to edit them one-by-one.

How to add a checkbox to a table view

needs

In the example I showed above, when the table is placed into QuickEdit mode, the checkboxes are automatically shown because it is a Yes/No type column.

---

To allow QuickEdit on a Table View - turn on the shown switch

To manually place a Table into QuickEdit Mode - tap the shown button

[flan828]

I figured out how to get the checkbox… I am having issues with prepopulating the table I have the loop that would populate it if I checked off the students in the enumlist. but with no students checked off there is nothing to index.

[WillowMobileSys]

flan828:

but with no students checked off there is nothing to index.

Right. But you should know which students attend the chosen Academy. You would loop over that list. So the Action that kicks off the looping would use an expression that returns ALL students attending the Academy rather than using the EnumList column to list the Students.

[flan828]

this is part of the looping that adds the students that are in the enumlist.
under STUDENT_ID I put index( FILTER(“STUDENT_TABLE”, ([ACADAMY] = [_THISROW].[ACADAMY_ID])) ,[count])
and its just not filling it in. Perhaps I am just looking at this for to long.

[WillowMobileSys]

While this is an approach, I didn’t explain well what I meant.

I would re-use the Students EnumList column you had. Instead of showing it for a user to select the Students, simply assign ALL Students to the list using an Initial Value - i.e. copy the expression you are using to populate the drop down and move it to the Initial Value property. The column is not shown.

Then you can use your looping as is - or at least as your were before.

[flan828]

That worked.