Using FILTERDIFF() with two tables

[David.Cramblett]

I have a table Student.

I also have table Caseload that has a row-link to the Student table, and a second column formatted as a "Contact" type to track users.

I want to use a filter to create a pick-list of Students from the Student table, while excluding students already assigned to a user in the Caseload table.

I thought I may be able to do this with FILTERDIFF():

=FILTERDIFF(Student, FILTER(Caseload, "Caseload[User]=%", $[SYS_USER]))

Student is the first column in my Caseload table. However, I don't think filterdiff is recognizing the comparison between the Student table and the row-link column in Caseload.

[Alyssa]

Hi @David.Cramblett, welcome to the community!

I'm happy to show you how to set this up. First, I would create a filter in the Student table of assigned cases. This can help organize the data, and from this you can create a picklist that excludes students already assigned to the SYS_USER.

Here's the Caseload table, with a rowlink set up as you described to a Student table:

Here's the Student table, with a Name column and another column that checks the Caseload table for the cases assigned to the student:

Here's the formula for the assigned cases column: =FILTER(Caseload,"Caseload[Student][Name]=[Name]")

Now in Builder, here's the FILTERDIFF formula of your picklist. It checks for the SYS_USER in each student's assigned cases and excludes students already assigned to the SYS_USER:

=FILTERDIFF(Students,FILTER(Students,"Students[CasesAssigned][User]=%",$[SYS_USER]))

Here's the app in action:

Hope this helps! Let us know if we can assist any further.

[David.Cramblett]

@Alyssa Thanks for the quick assistance!

I have setup my application following your examples, and it worked great when I only assigned a Student to the Caseload of a single staff member (User).

However, when I assign the same Student to another staff member (User), the filter seems to break. The student assigned to multiple (User) Caseloads, shows up in the pick-list again for staff (User) that are already assigned the Student.

If you think I may have done something wrong please let me know. I only have two users right now, both have my exact same name (different email addresses). I assume Honeycode uses some type of Unique ID for for users in the back-end, but not being sure, I thought I would mention this one unusual situation. I tried to change my name on the second account to test, but apparently that is not an option with Honeycode at this time.

David

[Alyssa]

Hi @David.Cramblett

You're right, I added more cases, and my example above works for when only one student is assigned to each user (or if the first row in the filter for the cases matches the SYS_USER).

I put together another way that works for multiple students, which filters out all students assigned to the SYS_USER. Here's how I set it up:

I created a User table, where the first column is the Name (contact format), and the next column filters the assigned cases to that user (similar to the formula in the Students table):

=FILTER(Caseload,"Caseload[User][Name]=[Name]")

Note that you also need to make the User column in the Caseload table a rowlink to the User table:

In the User table, I have a column that filters out the students that are in each assigned case. This is achieved with the IN operator:

=FILTER(Students," Students IN [AssignedCases][Student]")

The last column checks the opposite by using the NOT operator (checks which students are not in that list of assigned cases):

=FILTER(Students," NOT(Students IN [AssignedCases][Student])")

Then in Builder, your picklist will just call the filter in the last column of the User table in the row that matches the SYS_USER. This is done with FINDROW:

=FINDROW(Users, "Users[Name]=%", $[SYS_USER])[NOTAssignedStudents]

Here it is in action in the app. It's only showing the students that are not assigned to cases where I am the user (the SYS_USER):

Hope this idea works for you! Let me know how it goes, and if you have any other questions.

[David.Cramblett]

Thank you so much @Alyssa , this is working well for my filtering needs.

I can now get lists of students assigned on a User's Caseload:

=FINDROW(User,"User[Name]=%",$[SYS_USER])[StudentsAssignedOnCaseload]

And lists of students NOT assigned on a User's Caseload:

=FINDROW(User,"User[Name]=%",$[SYS_USER])[StudentsNotAssignedOnCaseload]

However, I'm having one issue due to my lack of understanding on data referencing at this early point in my experience with Honeycode. I'm not sure how to return a single Student reference from one of those filters (FINDROW in this case), for the currently selected student in a picklist. Hoping this screen capture will help:

[David.Cramblett]

Never mind, I just realized I was trying to set the reference one level too deep.

I moved up from the Field element to the Form / List element in the Builder, and changed the Data | Set Source to =$[InputRow] and my issue was resolved.

In my prior configuration I had been using =$[InputRow][Student] for the Source and that was no longer correct with the changes made for the Case Assigned filtering work.

Thanks Again for your help!

David

[Alyssa]

Great, I'm happy you got it all working, @David.Cramblett!

And you're welcome, please feel free to reach out again if you have any other questions as you build.