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

AppSheet AppSheet Q&A
1

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?
2

@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-8706-47a3-b72b-66ba17fd20c0

2 Likes
3

@tony Brilliant thx Tony.

4

@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

5

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

1 Like
6

Aleksi:

LIST(One,Two,Three)-SELECT(Table[Column],[Date]=TODAY())

@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.

7

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

8

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

9

Is the “Task Name” column a key column?

10

John_Krenkel:

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

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

11

You’re welcome

12

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

13

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

14

Aleksi:

SELECT(Tasks[Task Name],[Location]=[_THISROW].[Location])

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

15

You’re using AND() wrong.

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

Another good example of syntax, syntax, syntax

1 Like
18

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.

1 Like
19

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

20

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

  1. SELECT(Opening Tasks[ID], ...) generates a list of task IDs from rows in the Opening Tasks table that match the given criteria (...; see below).

  2. NOT(IN([Task Name], ...)) matches only those rows with a Task Name column value that does not occur in the given list (...; see below).

  3. SELECT(Opening Log[Task], ...) generates a list of task names of tasks logged as complete for today.