Re: Initial Value using a Ref

Alex3 · 11-23-2020 04:04 PM

Hello,

New to Appsheet.

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

Project
ProjectStage
Task

Objective:

We want only the Project Stages, for the specific Project, to appear in the Ref drop down when new Tasks are created
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:

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

Marc_Dillon

I think you need a Ref type column in Tasks table, that points to the grandparent Project record.

At which point you can auto-select the “Stage to be Decided” stage via:

ANY(
  FILTER( Project Stages ,
          AND( 
               [Name] = "Stage to be Decided" , 
               [Project] = [_THISROW].[Project] 
              )
         )
    )

How are your users creating the Task records?

Alex3

Hi Marc,

Thanks for the reply. I should have included the Ref you indicated in my
original email. The Task table also includes the following virtual Ref
column

ProjectID

[ProjectStage_idProjectStage].[ProjectID]

This Task Table virtual column is derived from the ProjectStage table and
uses the foreign key relationship between the Project and Project Stage
table.

Grandparent = Project

Parent = Project Stage

Grandchild = Task

When I use the ProjectID ref column I get a circular error that states:

Expressions in schema ‘Task_Schema’ have a circular definition. Check app
formulas and initial values for column ‘ProjectStage_idProjectStage’

It seems the circular error is caused by the inheritance of the
Task[ProejctID] which goes through the ProjectStage table.

Table Structure

Structurally, the Task table has a foreign key (i.e. Ref) relationship to
the ProjectStage table.

The ProjectStage table has a foreign key (i.e. Ref) relationship to the
Project table.

On the MySQL side, establishing a relationship directly from the Task table
to the Project table causes a foreign key constraint error (which makes
sense based on the schema).

Any ideas?

Thanks,

Alex

Marc_Dillon

I would say that this is correct, your expressions do seem to be circular. In particular this one:

…is “completing the circle” with your valid_if expression. The valid_if depends on the projectID to return a list of results for the stages column, but the selected stage will change the projectID value, which in turn changes the list of valid stage values.

I have no idea what this means.

My suggestion is that you’ll need to set the Task[ProjectID] when the Task is first created, and get rid of this expression that you have, which will mean that you need to change this to a real column (which I’d also advise about other virtual columns that you’ve described).

Please provide screenshots of what you described in your last post about how users create tasks.

Alex3

Hi Marc,

I appreciate your help. Below is a screenshot of the form that new Tasks are
entered on. The virtual column ProjectID brings through ID of the project
selected.

Is it possible that I need an IN expression where the Project Stage ID is in
the list of Project Stages for the selected project?

Thanks,

Alex

Marc_Dillon

How do you get to the form shown in the screenshot? (another screenshot please)

No I don’t believe this would resolve the circular reference issue.

Alex3

Hi Marc,

Below are screenshots showing the navigation from the Project View to the
view with the Project Stages (for the selected Project).

Marc_Dillon

And then the user clicks the plus button here to add a new task?

Your second screenshot appears to be the “Tasks” view. I don’t see anything about Stages in these screenshots.

Alex3

Hi Marc,

The user clicks the plus button to add a new task

The Task View is the view of all the tasks for the selected Project (grouped
by Project Stage)

Thanks,

Alex

Marc_Dillon

So the user clicks one of those 3 Project Icons, and is forwarded to the “Tasks” view, correct? Do you have a LINKTOFILTEREDVIEW() action for the Project view’s Row Select Behavior then, or something similar?

Basically I’m not seeing any way how the ProjectID in a new task can be easily auto-populated based on the “selected” Project.

Regardless of that though, go ahead and get that ProjectID column converted to a real column as I suggested above, get rid of any App Formula in it, and for now just allow the user to manually select the appropriate project from the Add Task Form. And use the expression that I posted originally as the Initial Value for the ProjectStage column.

Alex3

Hi Marc,

You asked about the number of users. Currently 10 - 20 users.

Thanks,

Alex

Alex3

Hi Marc,

Users create a Task after selecting a Project (in the Project View).

Selecting a project, navigates the user to the TaskView. The TaskView lists only the Project Stages, for the selected Project.

In the TaskView, our ProjectID indicates the correct ProjectID (for the project that was selected in the ProjectView).

Within the TaskView, users can add a Task.

Thanks,

Alex