Help required understanding/resolving a circular definition

[Jake_Naude]

Hi,

Please could someone give me a hand understanding a circular definition issue.

I’ve created a VC helper-column called [FINDMAXROW] to house an expression that I need to re-use several times an Initial Value in a form.

[FINDMAXROW]

IF(

		[survey_type] = "Node",

MAX(
	SELECT(
		survey[_ROWNUMBER],
	AND(
		[survey_type] = "Node",
        [_THISROW].[LCS] = [lcs],
        [_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],
        [_THISROW].[select_asset_type] = [select_asset_type],
	    NOT([branch_info] = "new branch")
		))),

IF(

    [survey_type] = "Cable",

MAX(
	SELECT(
		survey[_ROWNUMBER],
	AND(
		[survey_type] = "Cable",
        [_THISROW].[LCS] = [lcs],
        [_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],
        [_THISROW].[select_asset_type] = [select_asset_type],
	    NOT([branch_info] = "new branch")
		))),
    ""
    ))

.
.
Testing [FindMaxRow] VC in the Expression Assistant yields the correct result and shows max row 2 for “node” and max row 12 for “cable”

.
.
I then apply it as an Initial Value like this in the [survey_type] column:

LOOKUP([_THISROW].[FINDMAXROW],"survey","_ROWNUMBER","survey_type")

.
Testing [survey_type] Initial Value in the Expression Assistant yields the correct result and shows “node” and “cable” in the right places

.
.
However, saving the app presents a circular definition error on the [survey_type] column:

Here’s the survey table if it helps:

[GreenFlux]

Is there a Reset_On_Edit condition?

The image cuts off before that section so I can’t tell.

If so, try temporarily removing the expression and see if that’s were the conflict is.

Editor Bug: Circular Definition Error Questions

As @Steven_Aung confirmed (thanks Steven!), the new cycles discovered are because of the use of an expression/formula for the Reset on Edit property of a column. Here is the explanation for the issue: if you set up a formula for Reset_On_Edit for a column A and it depends on B, then this dependency is included when checking the dependencies for initial value formulas. I’m still a bit confused about this, so going to request @Dan_Bahir to explain further. And also, definitely, to improve the err…

[Jake_Naude]

GreenFlux:

s there a Reset_On_Edit condition?

Hi @GreenFlux,

Thanks for sharing the post…

I don’t have any Reset_On_Edit conditions.

I had a few EditableIf conditions which I have now removed but the issue stands.

My first thoughts were that I may be using the [_THISROW] incorectly somewhere…?

Still investigating… Cheers…

[Suvrutt_Gurjar]

I am sure there will be better guidance than this.

However it sounds that you are using the column type [Survey_Type] in computing FINDMAXROW expression and you are in turn using that MAXROW in LOOKUP () to again return the [Survey_Type] and then using the output as initial value in the same column [Survey_Type]

So it sounds that the same column is used to compute its own value. I believe that constitutes a circular reference?

Also since the [Survey_Type] is used in the expression FINDMAXROW expression , I believe it will always match the [Survey_Type] for that row even in expression assistant.

[Jake_Naude]

Suvrutt_Gurjar:

However it sounds that you are using the column type [Survey_Type] in computing FINDMAXROW expression and you are in turn using that MAXROW in LOOKUP () to again return the [Survey_Type] and then using the output as initial value in the same column [Survey_Type]

Hi @Suvrutt_Gurjar,

Thanks for that… I have binned the [FINDMAXROW] VC expression and am back to just using the Initial Value expression in the [survey_type] column as below but I still get the error - though only two and not three this time…

IF(

		[survey_type] = "Node",

LOOKUP(
MAX(
	SELECT(
		survey[_ROWNUMBER],
	AND(
		[survey_type] = "Node",
        [_THISROW].[LCS] = [lcs],
        [_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],
        [_THISROW].[select_asset_type] = [select_asset_type],
	    NOT([branch_info] = "new branch")
		))),
        "survey","_ROWNUMBER","survey_type"
        ),

IF(

    [survey_type] = "Cable",

LOOKUP(
MAX(
	SELECT(
		survey[_ROWNUMBER],
	AND(
		[survey_type] = "Cable",
        [_THISROW].[LCS] = [lcs],
        [_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],
        [_THISROW].[select_asset_type] = [select_asset_type],
	    NOT([branch_info] = "new branch")
		))),
        "survey","_ROWNUMBER","survey_type"
        ),
        ""
    ))

.
I found that the two offending lines were:
.

    [_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],
    [_THISROW].[select_asset_type] = [select_asset_type],

.
Removing them and running the expression like this makes the error go away:
.

IF(

		[survey_type] = "Node",

LOOKUP(
MAX(
	SELECT(
		survey[_ROWNUMBER],
     	AND(
	    [survey_type] = "Node",
        [_THISROW].[LCS] = [lcs],
        NOT([branch_info] = "new branch")
		))),
        "survey","_ROWNUMBER","survey_type"
        ),

IF(

    [survey_type] = "Cable",

LOOKUP(
MAX(
	SELECT(
		survey[_ROWNUMBER],
	    AND(
		[survey_type] = "Cable",
        [_THISROW].[LCS] = [lcs],
        NOT([branch_info] = "new branch")
		))),
        "survey","_ROWNUMBER","survey_type"
        ),
        ""
        ))

.
Still investigating…

[GreenFlux · Accepted solution]

Jake_Naude:

I then apply it as an Initial Value like this in the [survey_type] column:

LOOKUP([_THISROW].[FINDMAXROW],"survey","_ROWNUMBER","survey_type")

Try removing [_THISROW].

[_THISROW]. is used inside SELECT() or FILTER() expressions to reference back outside of the SELECT/FILTER.

But LOOKUP()'s first parameter is in the same context as the table already, so you can reference it directly.

LOOKUP([FINDMAXROW],"survey","_ROWNUMBER","survey_type")

---

I’m not sure if this is the only issue though. This seems like separate cause. But then you did have 3 errors to start with, so…

Jake_Naude:

I found that the two offending lines were:
.

    [_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],
    [_THISROW].[select_asset_type] = [select_asset_type],

[Jake_Naude]

GreenFlux:

Try removing [_THISROW].

[_THISROW]. is used inside SELECT() or FILTER() expressions to reference back outside of the SELECT/FILTER.

But LOOKUP() 's first parameter is in the same context as the table already, so you can reference it directly.

Hi @GreenFlux,

I removed every instance of [_THISROW] and the errors have gone!

A quick check also looks like the expression still produces the desired result.

I kind of get what you said about the SELECT() referencing back outside and LOOKUP() being in the same context as the table… So it would be right to use [_THISROW] in SELECT() but it isn’t needed in a LOOKUP()/SELECT() combo…? I’ll chew on that for a while and it’ll make sense later I’m sure…

I looked but couldn’t find an AppSheet doc on [_THISROW]…

Many thanks for your guidance…! I’m up and running again…! Cheers…