Dropdown from Valid_If when target is an ID from another table

[Mark_Taylor]

Greetings,

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

help.appsheet.com

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

I have the following Tables

Goals

Goal ID	Goal
e7270592	Develop Running
f63db96b	Develop Swimming

Objectives

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?

Thanks

[hru]

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

[Mark_Taylor]

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.

Thanks

[Mark_Taylor · Accepted solution]

Solved

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

[hru]

Nice…

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

[Laurene_Haynesw]

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?

[Mark_Taylor]

View columns of the Table in question.

Click the Edit button 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

[Laurene_Haynesw]

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

[Mark_Taylor]

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

[Laurene_Haynesw]

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

[Laurene_Haynesw]

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

[Mark_Taylor]

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

Have fun!

[Laurene_Haynesw]

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