I need some help with accessing columns in Pa...

[Marc_Dillon]

I need some help with accessing columns in Parent/Grandparent records in a select() expression, inside of a <> expression, within a workflow. The thisrow/thisrow-1 expressions aren’t working as I expected.

I’ve got:

Workflow on records of Table A

<> expression for records of Table B

SUM(Select( TableC[Amount] , ** ))

Table C is a child table of both A and B, and there is no reference relation between A and B.

I either need to: 1.) do the select() on the child records of Table A (specific records in Table C) -or- 2.) In the select condition, I need to be able to compare a column in Table C, with a column in Table A.

It seems if I do [_THISROW].[col] inside of the <> expressions, it accesses a column in Table A, which doesn’t seem right, I thought I would have to use [_THISROW-1].

So if I try to do #1, I tried :

SELECT(

[_THISROW].[child list][Amount], *)

, which doesn’t work saying "can’t find column ‘child list][Amount’. I’m guessing this is a dereference of a dereference, or is there a different syntax I need to use here? Also tried with a period in between.

For option #2, I’m just wanting to do the same thing, but compare the child’s parent ref column, to the key column of Table A, but I’m not sure how to access a column from Table A from inside the SELECT.

On a side note, “You can’t dereference a dereference”. Why not?

[Marc_Dillon]

I had read all of that before posting. Unfortunately reading it again did not help either. But I did discover something I may have made an incorrect assumption about. Does thisrow-1 refer to a parent record, or the nextmost outer level context? If the former, how does it decide which parent, if there are multiple parents.

[Phil]

“Orders” == “Table A” so here is the rewritten paragraph.

"It is important to remember that the Start Expression is evaluated in the context of the template that contains it. In this case, the outer template is used to format the Table A

record, so the Start Expression is evaluated in the context of that Table A record. Therefore, the Start Expression can make use of the columns in the current Table A record."

thisrow-1 refer to the first most outer level context. thisrow-2

refer to the second most outer level context. thisrow-3

refer to the third most outer level context. And so on. .

[Marc_Dillon]

Right. So inside my start expression for Table B, I should have to use thisrow-1 to access Table A. By experimental evidence, this is not true. I used just thisrow.

It is probably important to reiterate that my structure is not A-B-C parent-child-grandchild. C is a child of A. B is moreso just a convenient table to iterate through.

[Marc_Dillon]

Also to note is that my issue is further exasperated by the inability to easily test expressions in webhook templates. I have to make the change, wait 30 or more seconds to ‘save and verify’ and reload, hope I didn’t make an error,

and then hit the workflow test button, and wade through the output.

[Marc_Dillon]

Actually…

Inside the <> expression for Table B

<<[_THISROW].[Products]>> <<[_THISROW-1].[Products]>>

Those both output the same thing, a list of row refs from the [Product] column of Table A.

[Marc_Dillon]

Ok so after a few more slow hours of experimentation, I’ve got it working how I want it. I still have a big question of why does it work that way.

Workflow on Table A --Start expression on Table B -----select([Products][Amount], AND([Parent id] = [_THISROW].[id] , [Type] = [_THISROW-1].[Type]

)

[Products] is the key column of Table C

If I print out [Products] one line before the select expression (still inside the start expression) it gives me the correct list of Table C child records for the record of Table A. However if I do a select expression over [Products], it selects ALL records in Table C, so I then have to add the id matching condition.

[_THISROW].[id] accesses the id column of Table A, whereas

[_THISROW-1].[Type] accesses the type column of Table B.

Seems a little screwed up to me.

[Phil]

@Marc_Dillon

The following two paragraphs explain why the Start expression is referring to your Table A. In the explanation that follows, the Orders table is the outer table and Order Details is the inner or child table. These two paragraphs are taken from this article help.appsheet.com - Template Start Expressions

"It is important to remember that the Start Expression is evaluated in the context of the template that contains it. In this case, the outer template is used to format the Orders record, so the Start Expression is evaluated in the context of that Orders record. Therefore, the Start Expression can make use of the columns in the current Orders record.

By contrast, the expressions between <> and <>, other than the Start Expressions, are evaluated in the context of each child record. In our example, since the Start Expression refers to Order Details records, the expressions between <> and <> are evaluated in the context of a child Order Details record. The expressions between <> and <> normally refer to the columns of the Order Details record."

Topic “Double Dereference Expressions” in the following article covers “dereference a dereference”. It also explains how to overcome this limitation.

https://help.appsheet.com/expressions/expression-types/dereference-expressions Template Start Expressions help.appsheet.com