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