Create dropdown list from another table on status condition

[PG3]

Hi there,

I am trying to create a list of names from Table1 Column 'Name' where row 'Status' is 'Approved' to create a dropdown list in Table2  Column 'Approved Names' to allow a user to select one of the approved names.

I then changed the row to a Enum Type with a Base Type of Ref to Table1 and inserted an expression in 'Valid If' SELECT(Table1[Name], [Status] = "Approved") and I get a yellow sign with an exclamation mark in it in the dropdown.

1. Is this the best way to achieve my goal

2. If yes to (1) then how to I get rid of the yellow triangle

Thanking you in advance

[Suvrutt_Gurjar · Accepted solution]

References require key column in their expression.

Please change the expression to 

SELECT(Table1[Key column], [Status] = "Approved") 

Where [Key Column] is the key column name of Table1

You could also create a slice called say "Approved_Names" on Table1 with a row filter expression something like [Status] = "Approved"

Then your valid_if can be

Approved_Names[Key Column]

[PG3]

Great, that worked.

Now one more question. Each time one of the approved names is used in a submission for payment and the submission is approved how can I reduce the Approved Names list by the submitted and approved names?

I do not want double submissions on the same name.

Example: 

1) Approved Names list = 'Paul', 'Peter', 'John'

2) Payment submission is done for Paul and Approved,

3) Approved Names list now is 'Peter', 'John'

Cheers

[Suvrutt_Gurjar]

Please try 

Approved_Names[Key Column]- (Table2{Approved Names] -LIST([_THISROW].[Approved Names]))