Template Nested Start expressions - can inner expression reference value from outer Start?

[WillowMobileSys]

Background

I have an Order which lists Ordered Kits and Ordered Products. A kit contains a pre-defined list of parts derived from products. A Kit is ordered at the Kit level and when saved, a workflow populates the list of parts into the Order Products table. It may be that not all parts need to be ordered so they can be removed from the Order Products list. AND it may be that other parts are ordered and included in the Ordered Products list.

Invoicing

On the invoice I want to show:

1. a table with the Ordered Kit level details
2. A table beneath the Kit details with the list of ordered parts from the kit
3. Repeat 1) and 2) for each additional Kit
4. Finally a table with Other Products

This means I need to split the Related Ordered Products by each Kit ID and then where ISBLANK(Kit ID)

---

Question

In example template expression excerpt below, is there a way that [Kit] can be referenced from the outer Start row within the inner Start expression? The text is red obviously doesn’t work since it references the Order row used to generate the invoice.

[Steve]

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

Back-references within nested queries ([_THISROW-n]) Consider a complex App formula with “nested” SELECT() expressions (SELECT() expressions within SELECT() expressions): SELECT( table[column1], ... SELECT( table[column2], ... SELECT( table[column3], ... ) ... ) ... ) Let’s call the row for which this entire expression is evaluated as its App formula the Origin Row. Within this entire expression, we can refer to column values of the Origin Row by dere…

help.appsheet.com

Template Start Expressions

Using Start expressions in your workflow templates.

[WillowMobileSys]

Thanks @Steve! I have seen those articles and reviewed them. Neither covers this use case.

To technically boil this down further, I am actually iterating over 2 child lists.

Oh wait, since Ordered Products referenced Kits, I might already have the list in a VC on the [Kits] table!! I should have thought of that. Sometimes you just can’t see the forest right in front of you!!

[Steve]

WillowMobileSystems:

Neither covers this use case.

They both do indeed.

[WillowMobileSys]

To be a copycat…Nope! I didn’t think it would work but thought I would try it anyway. The error returned is:

 'StartSELECT([Related Ordered Products], [Kit]=[_THISROW-1].[Kit])' is invalid due to: Error in expression '[_THISROW-1].[Order ID].[Kit]' : Unable to find column 'Kit'. 

The THISROW variables don’t work in this context. [_THISROW] points to the Order row sent to the template. Order has no parent so there is nothing for [_THISROW-1] to point to. AND my Start’s are operating on two child lists - one nested within the other.

[Kit] is a column within the row of the outer Start. I tried using [_THISROW] hoping it would point to the row from that list - <<Start: [Related Ordered Kits]>> - but since it doesn’t, it can’t fond the [Kit] column.

No matter, as I mentioned above, I have realized that I do have an already existing VC I can use in the inner Start - [Kit].[Related Ordered Products].

[WillowMobileSys]

Well, I was wrong again. '[Kit].[Related Ordered Products]` doesn’t give me what I need. I blame brain fog from staring at a computer screen too long!!

So I am back to my original question. I am trying to resolve the [Kit] = [Kit] problem but [_THISROW] won’t work. I want to avoid yet another VC in app that only serves for use when generating a PDF.

Can the inner Start’s SELECT expression, through some trickery or lesser known variable, refer to a row from the outer Start?

Maybe if I change the column name of my Order Kits row to [Ordered Kit], the SELECT can figure out by context?

[WillowMobileSys]

I feel like I’m blogging! Anyone want to order this gizmo from <<affiliate link>>??

So I tried [Ordered Kit] and the SELECT cannot figure out by context in the template how to resolve. Error below:

Expression 'Start[SELECT([Related Ordered Products], [Kit]=[Ordered Kit])' is invalid due to: Expression refers to undefined field.

So I guess I have to implement a VC after all!

[Marc_Dillon]

WillowMobileSystems:

SELECT([Related Ordered Products]...

Aren’t you missing something in your SELECT here?
Like:

SELECT([Related Ordered Products][key-column]...

[WillowMobileSys]

Maybe. I just discovered in a different SELECT I was missing exactly that. I’ll try it again. I guess I assumed the field the error was referring to was [Ordered Kit].

[Bartboy321]

Hi Willow - did you get this sorted?