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

Karl_Kenny1 · 04-18-2018 05:00 PM

Dynamic Filtering on a dropdown list.

I have a 3 tables.

Table 1 = Data from the app Table 2 = List of items (say 20 items) Table 3 = List of characteristics (say 50 characteristics) My data input form comes from table 1 It has three columns Column 1 = Unique ID (which is a concatenation of Item + Characteristic) Column 2 = Item (ref to table 2) Column 3 = Characteristic (ref from table 3)

The goal is to have the users fill in all the data over time i.e., Item + Characteristics such that each Item will have 50 characteristics.

Challenge:

I want to have the app ONLY show the unused characteristics.

Explanation:

Item = Car, Characteristics = Check point 1 to 49.

If Car + Characteristic 1 has been entered previously, I want the dropdown to only show Characteristics 2 to 49 for Car.

Do I have to create a list of 1000 answers (20 items

50 characteristics) or is there a smarter?

tony1

@Karl_Kenny3 I’d create some virtual columns on your Items table to do some intermediate work. Please take a look at this example app that I made (description at the bottom of the page).

https://www.appsheet.com/samples/Use-data-validation-to-select-distinct-pairs?appGuidString=af6bc767...

John_Krenkel

@tony Can this be done similarly but instead of using already assigned add a dimension for date.

If Already Assigned Today, filter from list. Tomorrow, would be a new date so the full list would be available again.

John

kasun_charitha

It’s Great. Thank you

Karl_Kenny1

@tony Brilliant thx Tony.

Aleksi

Yes you can do that… one simple way could be…
LIST(One,Two,Three)-SELECT(Table[Column],[Date]=TODAY())

John_Krenkel

@Aleksi Thank you. Did this but now it is all just a string of data

List(Tasks[Task Name])-SELECT(Opening Log[Date],[Date]=Today())

Guessing Lists isn’t the right function here.

Basically the logic i am thinking is, if Task Name is in the opening log for today, filter from list.

Aleksi

Remove the List expression… Task[Task Name] is already a list.

John_Krenkel

I put that code in the “TASK” column Valid If field in my Opening table and it didn’t work.

The Task Column is REF to the TASKS table.

Missing something

Aleksi

Is the “Task Name” column a key column?

John_Krenkel

I got it. and exactly what you just suggested. Thanks

Aleksi

You’re welcome

John_Krenkel

Ok one more question on this Thread. Can we further limit the list to only show tasks that are opening tasks.

The idea solution would be this.

Pick a location.
In the Tasks, I have Location Opening and Location Closing tasks.
So they pick location X.

App looks at Tasks[Task Loc Detail] = Location selected on screen(I don’t think this would be a record yet)

So at this point we should be filtered by Location Opening only.

The we would do a second filter where we limit that list to Today’s items. We are successfully doing this now but the list has all the tasks (opening Loc A, Opening Loc B, Closing Loc A, Closing Loc B).

I do not want to create new tables for each one so is there a way to create a multidimensional filter

Aleksi

Probably something like… SELECT(Tasks[Task Name],[Location]=[_THISROW].[Location])

John_Krenkel

Tried this, no worky
Tasks[Task Name]-And(SELECT(Opening Log[Task],[Date]=Today()), SELECT(Tasks[Task Loc Detail],[Branch]=[_THISROW].[Branch]))

Steve

You’re using AND() wrong.

Steve

Tasks[Task Name]
- SELECT(
  Opening Log[Task],
  AND(
    ([Branch] = [_THISROW].[Branch]),
    ([Date] = TODAY())
  )
)

Mike_A

Another good example of syntax, syntax, syntax

John_Krenkel

Thank you @Steve

I am dusting off my coding hat. It has been a LONG time so taking some time to get back the syntax.

John_Krenkel

having a strange issue now.

Opening Tasks[Task Name]-SELECT(Opening Log[Task],AND(([Date]=Today()), [Branch] = [_THISROW].[Branch]))

Works fine but instead of putting the Task ID number in the Task column of the Log sheet, it puts the task name.

Opening Tasks[ID]-SELECT(Opening Log[Task],AND(([Date]=Today()), [Branch] = [_THISROW].[Branch]))

Puts the ID number in the log sheet but does not limit the list, which works fine in the above example.

The dropdown selection box does list the Task Name, which is how I would like to show on the UI but something isn’t jiving.

Table -> Log -: ID - Key
Table -> ID - Key Task Name - Label

Steve

This:

Opening Tasks[Task Name]
- SELECT(
  Opening Log[Task],
  AND(
    ([Date] = Today()),
    ([Branch] = [_THISROW].[Branch])
  )
)

generates a list of task names (Opening Tasks[Task Name]) the subtracts from it another list of task names (SELECT(Opening Log[Task], ...). The result is the first list of names with the names in the second removed. A list of names, not IDs.

This:

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

starts with a list of IDs (Opening Tasks[ID]) and subtracts a list of names (SELECT(Opening Log[Task], ...) from it. Presumably, your IDs and names look nothing alike, so none of the names occur in the list of IDs, so nothing was removed.

What you want is to generate a list of task IDs of tasks whose names do not occur in the list of today’s completed tasks:

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

SELECT(Opening Tasks[ID], ...) generates a list of task IDs from rows in the Opening Tasks table that match the given criteria (...; see below).
NOT(IN([Task Name], ...)) matches only those rows with a Task Name column value that does not occur in the given list (...; see below).
SELECT(Opening Log[Task], ...) generates a list of task names of tasks logged as complete for today.

John_Krenkel

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.

John_Krenkel

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

John_Krenkel

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])
)
)
)
)
)

John_Krenkel

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

John_Krenkel

@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

Steve

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

John_Krenkel

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

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

Steve

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

John_Krenkel

Thank you kind sir. Learning so much

John_Krenkel

@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

Steve

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?

John_Krenkel

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.

Steve

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

John_Krenkel

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

Steve

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:

John_Krenkel

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