How to make Dependent Dropdown in Forms with data from different Tables

[sorin_mihai]

Hello

I thought I would be able to nail this, but I can’t…

So in a Task Management app, a Project has many Objects, and an Object has many Tasks
(the project would be to make the furniture of a coworking, and an object would be to make a table, and a task would be to make the desk of the table).

There is a Projects Table with a Project Name column
There is an Objects table, with an Object Name column and a Project Column that references the Project Name column from the Projects Table.
There is a Tasks Table, With a Task Name column, a Project column that references the Project Name column from the Projects Table and of course, an Object Column that the Object Name column from the Object Table.

So, I create:

Project A

• Object 1
• Object 2

Project B

• Object 3
• Object 4

What I can’t figure out how to do, is that When I create a New Task, after selecting the Project to which it relates, in the dropdown for the Object to which it can relate, I get all the Objects already created.
What I am trying is to get only the Objects that refer to the Project already selected in the question above…and I can’t figure it out…it’s a dependent dropdown but the Video I found as tutorial from @MultiTech_Visions is somewhat a different situation as all the data is in one Table:

Dependent Dropdown Video
[https://help.appsheet.com/en/articles/961554-dependent-dropdown](http://Dependent Dropdown article Written by Santiago Uribe Montoya)

The irony, is that there is the automated Virtual Column made in Projects, with Lists of Related Objects and is basically exactly what I need to show up in the selecting the Object question of the Form, but I can’t put it in the Form, I can’t duplicate it because I can’t make a list column … I tried a bunch of things but they didn’t work…

I’m guessing that in the Object Column from the Task Table, which is a reference to the Object Table, there should be a formula for the Valid If field.

Any ideas?

Thank you so much,
Sorin

[Steve · Accepted solution]

To allow selection of only objects already attached to the project to which the task itself is attached, use the following as the Valid If expression for the Objects column (presumably of EnumList type) in the Tasks table:

IFS(
  ISNOTBLANK([Project Name]),
  ORDERBY(
    FILTER(
      "Objects",
      AND(
        ISNOTBLANK([Project Name]),
        ([Project Name] = [_THISROW].[Project Name])
      )
    ),
    [Object Name]
  )
)

1. IFS(ISNOTBLANK([Project Name]), ...) only evaluates the given expression (...; see (2)) if the value of the Project Name column of the row in the form is not blank. See also IFS() and ISNOTBLANK().

2. ORDERBY(..., [Object Name]) sorts the given rows (...; see (3)) by their respective Object Name column values. See also ORDERBY()

3. FILTER("Objects", AND(..., ...)) gathers the rows of the Objects table that match the all of the given criteria (...; see (4) & (5)). See also AND() and FILTER().

4. ISNOTBLANK([Project Name]) matches only rows where the Project Name column is not blank.

5. ([Project Name] = [_THISROW].[Project Name]) matches only rows with a Project Name column value that matches the Project Name column value of the row in the form.

Alternatively–and much simpler–dereference Project Name to access that list you mentioned of associated objects. Instead of the expression above, use this one:

ORDERBY(
  [Project Name].[Related Objects],
  [Object Name]
)

As you noted, the system generates a List of Related Objects for each Projects row, which is a list of all Objects rows that reference that Projects row.

[sorin_mihai]

@Steve Thanks a lot for taking the time in writing these flawless huge formula AND explain them both work perfectly

[Mozart_Alberto_]

HI! I did this, and it works! just one little thing, it loads the key value not the lablel, any suggestion to load the label in the dependant dropdown?
Thanks

[Steve]

Mozart_Alberto_Garci:

it loads the key value not the lablel

That suggests the column you’re choosing a value for isn’t of type Ref.

[Mozart_Alberto_]

HI! thanks, in fact it is a no ref type, it is an enum in my data.
I have an area with many products and one area can have many locations, then when I’m creating a product I would like to choose the area, an then the location based on the area. I dont know if my data structure is wrong!

Captura de pantalla 2020-06-20 a la(s) 10.54.48|572x500

[Mozart_Alberto_]

[Mozart_Alberto_]

Steve:

type Ref .

It worked, a location can have many products, and it loaded the labels, thanks