Dropdown from Valid_If when target is an ID from another table


I am trying to use the select statement with “Valid If” to filter available values as shown in


Dropdown from Valid_If
How to create a dropdown that depends on a particular list of values.

I have the following Tables


Goal ID Goal
e7270592 Develop Running
f63db96b Develop Swimming


Objective ID Goal Objective
8886384e e7270592 Put On Running Shoes
52d041ee e7270592 Go to Track
98d4b656 f63db96b Put on Swimming Suit
7a2361ff f63db96b Go to pool

Each Objective is assigned to a Parent Goal

In the Objective table the Goal Column references Goals Table.

Clients get assigned goals

Goal Assignment ID Patient ID Goal ID
60a8ea32 2ff7ea76 f63db96b
6348ad07 a541ce98 e7270592

Clients have multiple goals with multiple objectives assigned to them in a table (The Goal Column is from the Goal Assignment above)

Objective Assignment ID Client Goal Objective Objective status
1e540cb6 2ff7ea76 60a8ea32 8886384e Assigned
dc779f0a 2ff7ea76 60a8ea32 52d041ee Assigned
f790f904 a541ce98 6348ad07 98d4b656 Assigned
525167fd a541ce98 6348ad07 7a2361ff Assigned

My Goals here is that when I want to add an objective to a client

I first select a goal from a dropdown of all goals

Then I select an Objective from a dropdown where only choices are objective belonging to the prior selected Goal.

To accomplish this used this select statement, as mentioned in the help article, in the “Valid if”

of the Objective column for Objectives assigned

SELECT( Objectives[Objective] , [Goal] = [_THISROW].[Goal])

This returns nothing

However if I replace the “[_THISROW].[Goal] “ with a raw goal ID for the Goal (Develop Swimming)

SELECT( Objectives[Objective] , [Goal] = “f63db96b” )

Then my selection is filtered to only objects that belong to that Goal

I can see why this Raw ID is technically working but how can I structure my select statement so that goals I select

Properly filter the available Objectives?



Do you have a relation between the Goals table and the Objectives table (as ref type)?

I just revised my post .

Yes I have relations ships built.

I realized that [_THISROW].[Goal] is going to return the “Goal Assignment ID” and not the “Goal ID”

I am going to keep trying to figure it out.



I figured it out.

I needed this for my valid if statement

SELECT( Objectives[Objective] , [Goal] = LOOKUP( [_THISROW].[Goal], “Goal Assignments”, “Goal Assignment ID”, “Goal” ) )



I was just guessing based on your initial question before edit, However, looking to such detail question, I believed you will figure it out.

Where did you put this statement? You are brilliant I am developing an app that uses the same setup as yours. Mine is an app that is on a hierarchy. And I iterate through the numbers. 1,2,3,4,5,… 15. Each number in the sequence will eventually be in the first row position. 2,4,5,8,9; 3,6,7,12,13; 4,8,9,16,17; 5,10,11,20,21. I have been searching for this formula. You are a God sent angel!!! Where am I supposed to put the formula?

View columns of the Table in question.

Click the Edit button image to the left of the column in question

Under the Data Validity section you will see where you enter the “Valid If” statement.

Hope that Helps

1 Like

Yes, I understand, what I don’t understand is what column you put it in. That’s where I am confused still

If you can think of your columns as Parent and Child

The Child would have a valid if statement based on the data in the Parent column.

I would need to know more about the structure of your data to answer more beyond that

1 Like

Ok please just tell me where you put it in your app. That’s all. I actually structured my app just as yours. It was the perfect setup. Is there a way to give you access to the app

Wow so you really do figure this out on your own and really fast too. I realized that you were doing a drop down and what I wanted to accomplish was a match. So I used a simple dereference/lookup formula!!! Thanks so much for the inspiration with how you setup your app. It was my break through!!! You ROCKKKKKKKK

1 Like

Yeah that is the way it goes. I often figure it out an hour or so after I post my question.

Have fun!


It’s amazing!!! I guess just writing it out helps you understand the logic you are trying to create.