Dynamic Filtering on a dropdown list. I have...

Expected Result:

List Limited if Task is selected for Today AND for the selected branch.

Actual Result
List is not limited at all.

I see the list of tasks for ALL branches and even if the task is dated today the list still shows it.

My Opening Task list has two sets of opening tasks, one for each branch, with different ID numbers.

So, JOP1 = first task for Branch J
SOP1 = first task for Branch S

If I click Branch J in the form, the list should only show items in J branch
And if the task is already completed for today, it should not show in the list.

Best Case would be to put ALL tasks, opening and closing in one table and just limit the list by:
Branch
Opening / Closing
Selected Today

Dream scenario - Create a button list of tasks
Select Location and a list of incomplete tasks pops up
Select Task -> Go to Log form
Complete Task

Basically instead of clicking Create Task, App sheet would pre populate the tasks.

Of Got it to limit the list but still showing items for both branches.

Got it @Steve

FILTER(“Opening Tasks”,[Branch]=[_THISROW].[Branch])
SELECT(
Opening Tasks[ID],
NOT(
IN(
[ID],
SELECT(
Opening Log[Task],
AND(
([Date] = TODAY()),
([Branch] = [_THISROW].[Branch])
)
)
)
)
)

I was wrong. While that filtered by location, it does not limit the list. I will keep playing with it

@Steve I am legitimately stuck

I have my Opening Tasks List. It includes Tasks for 2 locations, that may differ.

[Task ID], [Task Name], [Task Branch], [Task Time]

When the list is limited, I first only want to see the items for the selected branch.
Then from there, I want use the Select function that works to limit the list.

Currently, It shows me everything in the list for both locations and when i complete a task, it removes that line item (however, I simply only want to see the selected location tasks from the onset)

Have to make this fool proof, because you know what happens if you don’t :slight_smile:

Is the Task Time column a Time type (as the name suggests), a Date type (as your comparison to TODAY() suggests, or something else?

Simple Text, opening and closing. not really a time at all just a time of day :slight_smile:

Opening Tasks go in the opening log, closing tasks in the closing log, i suppose i could have used a single log with an identifier like opening / closing. But I did not

1 Like
SELECT(
  Opening Tasks[ID],
  AND(
    ([Branch] = [_THISROW].[Branch]),
    NOT(
      IN(
        [Task Name],
        SELECT(
          Opening Log[Task],
          AND(
            ([Date] = TODAY()),
            ([Branch] = [_THISROW].[Branch])
          )
        )
      )
    )
  )
)

Thank you kind sir. Learning so much

1 Like

@Steve Last year you helped me greatly with this code

SELECT(
Opening Tasks[ID],
AND(
([Branch] = [_THISROW].[Branch]),
NOT(
IN(
[ID],
SELECT(
Opening Log[Task],
AND(
([Date] = TODAY()),
([Branch] = [_THISROW].[Branch])
)
)
)
)
)
)

I want to simplify for something else I am doing. Basically, i have a roster of players and everyday we are going to take attendance.

I have a sheet PLAYERS with Player ID, Name and Team (and other stuff). I have my Attendance sheet which will capture the Player ID.

First Question, when creating the attendance log, can I first Filter my list by Team. This will limit the dropdrown to just players on that team.

Second, Similar to the above, Once a player is checked in or check absent, I want their name to disappear.

Thanks

This is a bit confusing to me. My initial expectation would be that you would use the dropdown to choose everyone that is present, with those left unchosen considered absent. But here it sounds like you want to explicitly choose present or absent, with the possibility that some people would be neither. Can you clarify?

Actually this is a good idea. I will take Absent out as a choice. So really, I just want to use the dependent dropdown for the choosing members of the team and then have a bit of code that acts as:

If Player Present = True, remove from available players in list.

1 Like

Okay. From what list do you want their names removed?

I think it would be dynamic. I should have added the following

Attendance sheet has a column for level and player, that dependent dropdown works. After a player is checked in for the day, they should not be able to be checked in again for the day.

So the attendance sheet level valid_if = Players[Team] and Player ID valid_if = Players[ID]

Want the player id to start list limiting.

Hope that makes sense

1 Like

You won’t be able to use a simple dependent dropdown because you have a non-simple dependency. You can keep the level column Valid_If as Players[Team], but the player column Valid_If should be something like:

(Players[ID] - Attendance[Player ID])

See also Constructing a List with Subtraction, here:

Thank you @Steve

The Dependent Dropdown works well with Valid_if Players[ID] in the Attendance[Player ID] column. When I add the Subtraction expression, it no longer filters the list, I get a list of all players instead of just the players on that team.

The behavior I am trying to create is as follows:

Create an attendance record.

  • Select Team (SLW)
    -Select Player (From list of players identified as SLW in Players[Team]
    Complete rest of form

When I go to create the next record for Today, I select Team, then it looks back at the records for today and removes any player who has a Today dated record for attendance. When tomorrow hit, the list will be fully available because there would be no records dated (Today) in the spreadsheet.

The app functions in its current state but it’s a little tedious. I want to cut down attendance time.

Any time I change the Valid_If statement to anything but Players[ID], it breaks the dependency, which I guess should be the case.

Lastly, any tips to sort the list alphabetically. It gives me an error that I cannot do that in a REF column