Initial Value using a Ref

[Alex3]

Hello,

New to Appsheet.

We are building a project management app with three main tables:

Project
ProjectStage
Task

Objective:

1. We want only the Project Stages, for the specific Project, to appear in the Ref drop down when new Tasks are created

2. We want to set the Initial Value of the Task[ProjectStage] column to a specific default value based on a formula

• The default Project Stage value will be specific to the Project

• The default name of the Task[ProjectStage] will be "Stage To Be Decided’

• Each Project will have a Project Stage named “Stage To Be Decided”

• The various Project Stages that are named “Stage To Be Decided” all have unique Primary Keys (i.e. different ProjectStage.ID values)

Below is an example:

Project | | ProjectStage | | ProjectStage[ID]
App Build | | Stage To Be Decided | | 1
Learn French | | Stage To Be Decided | | 7
Coaching | | Stage To Be Decided | | 21

Table Structure Notes:

• Every Project has at least one Project Stage. A project can have multiple stages
• A Project Stage contains Tasks
• Every Task belongs to a single Project Stage

The relationship between the tables is as follows:

Project has a de-reference relationship with ProjectStage
ProjectStage has a de-reference relationship with Task

MYSQL foreign key relationships define the column names (with the exception of the virtual columns which are highlighted in italics, with the app formula appearing below)

Important columns
Project Table

• Project.ID

-Related ProjectStages
REF_ROWS(“ProjectStage”, “Project_idProject”)

ProjectStages Table

• ProjectStage.ID
• ProjectStage.Name
  -Related Tasks
  REF_ROWS(“Task”, “ProjectStage_idProjectStage”)
  -ProjectName
  [Project_idProject].[Name]
  -ProjectID
  [Project_idProject].[ID]

Task Table
Task.ID
ProjectStage_idProjectStage
-ProjectName
[ProjectStage_idProjectStage].[ProjectName]
-ProjectStageName
[ProjectStage_idProjectStage].[Name]

Current Valid If Statement for the Project Stage column in the Task table
IFS(
ISNOTBLANK([ProjectID]),
ORDERBY(
FILTER(
“ProjectStage”,
AND(
ISNOTBLANK([Project_idProject]),
([ProjectID] = [_THISROW].[ProjectID])
)
),
[ProjectStage_idProjectStage]
)
)

This formula works as expected to provide the Project Stage values that are specific to the current Project

Problem / Question
We are trying to set the initial value of Task[ProjectStage] programatically, based on a formula. When the initial value is hard coded, to a specific Project Stage ID, the default initial value works correctly and only the Project Stages for the specific project appear in the drop down. But we need to use a formula set the initial value for Project Stage.

Our Attempt
ANY(SELECT(ProjectStage[ID],AND(IN([_THISROW].[ProjectStage_idProjectStage],Project[Related ProjectStages]),[ProjectStageName]=“Stage to Be Decided”)))

The initial value formula above leads to a circular reference

Thanks in advance,

Alex