Filter a dropdown list based on a value in the same dropdown table

[stefanasks]

I have a table Tasks, a table Jobs and a dropdown where jobs can be added to the task. The Jobs Table also has a column is_active. Now I want to filter them out based on the Is_active switch, only from the dropdown, so that the can't get selected if is_active is false.

How can I do that?

[Suvrutt_Gurjar]

Please create a slice called say "Active_Jobs" on the Jobs table with a slice filter expression something like [is_active]

Then in the Tasks table in the [Job] column dropdown,  you could have a valid_if expression something like 

Active_Jobs[key column of Jobs table]

[stefanasks]

Ok, 

• I made a slice called active_job with a filter [is_active]
• Then I have put the slice as reference in Table_Report in column Table_Job

The result is that appsheet only doesn't lookup the job name, but the ID is still there.

What did I wrong?

[Suvrutt_Gurjar]

Please make the job Name column as label in the Jobs table.

[stefanasks]

The name is set as label already. When its not, all entry are shown wrong, but its only those who are not is_active.

[stefanasks · Accepted solution]

Oh sorry, I got it. My mistake was that the referenced column needs a valid list in general. And in that step I just needed to change the select from

select(Table_Job[Row ID], true) to select all, to:

select(Table_Job[Row ID], [is_active] = true) to select only active.

[Suvrutt_Gurjar]

---

@stefanasks wrote:

select(Table_Job[Row ID], true) to select all, to:

select(Table_Job[Row ID], [is_active] = true) to select only active.

---

Actually I had suggested the same thing , albeit with a slice

---

@Suvrutt_Gurjar wrote:

Then in the Tasks table in the [Job] column dropdown,  you could have a valid_if expression something like 

Active_Jobs[key column of Jobs table]

---

[stefanasks]

For whatever reason a slice did not work for me. With a slice there was no name lookup for the referenced id, so that in the dropdown the id appeared together with a warning.

Also the slice requires a boolean in the filter condition, and table column valid-if condition requires a list.

[Suvrutt_Gurjar]

---

@stefanasks wrote:

Also the slice requires a boolean in the filter condition, and table column valid-if condition requires a list.

---

Correct. So I had requested a Boolean filter condition such as below

---

@Suvrutt_Gurjar wrote:

Please create a slice called say "Active_Jobs" on the Jobs table with a slice filter expression something like [is_active]

---

Please note that Y/N type columns do not further need "TRUE" or "FALSE" to know their status.

In short ,

[is_active] = TRUE is same as simply [is_active]

and 

[is_active] = FALSE is same as simply NOT([is_active])

For valid_if,  had suggested the following

---

@Suvrutt_Gurjar wrote:

you could have a valid_if expression something like 

Active_Jobs[key column of Jobs table]

---

Please note that Active_Jobs[key column of Jobs table] 

will produce a list.

or in short, a Table_Name[Any_Column_Name] or 

Slice_Name[Any_Column_Name]

expression(s) will produce a list of that column for the entire table or slice.