Dropdown from Valid_If when target is an ID from another table

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

1 Like

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.

Thanks

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

2 Likes

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.