How to reference automation step outputs within start-end loop

[dbaum]

I can't figure out how to reference the output from a Call a process automation step in a subsequent step's start-end loop. Can someone explain how to accomplish this?

Here's the details of my automation.

Parts that work

The "Duplicate item" Call a process step calls the "New item" process, whose Return a value step returns a value for NewItemID. I can then reliably reference the NewItemID value in, for example, a straightforward data action step to set a column to that value. This all works reliably as explained in Use values from a previous step in a non-reusable action or task and Use 'Call a process' and 'Return values' steps in ... - Google Cloud Community. 

Call a process step: Duplicate item

Return a value step: Return item ID

Data action step that (successfully) sets column value using [Duplicate item].[NewItemID]

Part that doesn't work

In the same automation I have a subsequent Call a webhook step that calls the AppSheet API and whose body includes a start...end expression to iterate on a list of rows to update one column in each row. It's an EnumList column, and I need to add NewItemID to each row's list for the column. Any way that I try to reference NewItemID, the app returns an error--either immediately upon saving in the editor due to an invalid schema reference or else later when running the automation and blank is returned for NewItemID, which results in the column failing validation. I know blank is returned from the Automation Monitor--for instance, in this example, the EnumList column's sole existing value (e447bd88) is listed followed by a comma indicating the NewListID blank value was appended to the end of the list, which in turn results in an error because that list isn't valid for the column:

\"Assigned Items\": \"e447bd88 ,\"

Error message:
Row having key 'ee94dee2' in table '...' containing value 'e447bd88 ,' in field 'Assigned Items' failed 'Valid_If' condition

Following are some of the syntaxes I've tried in the webhook body:

"Assigned Items": "<<[Assigned Items] + LIST([Duplicate item].[NewItemID])>>"
"Assigned Items": "<<[Assigned Items] + LIST([_THISROW].[Duplicate item].[NewItemID])>>"
"Assigned Items": "<<[Assigned Items] + Duplicate item[NewItemID]>>"
"Assigned Items": "<<[Assigned Items] + Duplicate item Output[NewItemID]>>"

[dbaum]

@AleksiAlkio, do you have a moment to consider this question? If so, I really appreciate any guidance you can provide. 

[AleksiAlkio]

So.. you are trying to find correct rows where the EnumList column needs an update? Is that the idea?

[dbaum]

No. The Start expression in the webhook task's Body property already returns the necessary list of rows.

In each of those rows, I need to add an additional reference value to the [Assigned Items] EnumList column. That value is the row key generated earlier in the automation via the Call a process and Return values steps and assigned to the NewItemID variable.

No expression that I can come up with for the "Assigned Items" property in the JSON works. One of the following always happens:

• NewItemID returns blank--the new row key is not added to the EnumList column--plus the automation step results in an error because the blank list item (e.g., following the comma in e447bd88 ,) doesn't validate in this Ref column.
• The app editor tells me the syntax is invalid by labeling the step as an error.

[AleksiAlkio]

So.. the problem is: the first step generates the row and the Return value reads it properly. The only problem is the Valid_If doesn't accept it?

[dbaum]

Thanks a lot for working through this with me.

Unfortunately, I still haven't explained it clearly enough.

---

@AleksiAlkio wrote:

The only problem is the Valid_If doesn't accept it?

---

No--the Valid_If failure is just a symptom. The column value isn't valid because the list includes a blank item. The root cause is that I can't figure out how to reference a previous automation step's output (i.e., NewItemID) in the context of the webhook body property's start...end expression so that NewItemID returns its value instead of blank.

Here's an outline of the whole flow.

1. User adds row to Table A
2. Automation is triggered for Table A
   1. Call a process step adds a row to Table B
      1. Called process's Return values step returns the Table B new row's key as NewItemID.
   2. Run a data action step (successfully) updates the new Table B row by referencing NewItemID
   3. Call a webhook task is intended to use AppSheet API to add the Table B new row's key (i.e., NewItemID) to an EnumList-base-Ref column ([Assigned Items]) in existing Table C rows. The body property's start...end expression reliably returns a list of the keys of the intended rows. Within the start...end expression:
      1. The property for the key column reliably populates with each row's existing value for that column ("Table C ID": "<<[Table C ID]>>).
      2. The expression for the property for the EnumList-base-Ref column is intended to take the column's existing value (i.e., [Assigned Items] and append to it the NewItemID value. The expression reliably returns the column's existing value. However, that expression does not return the NewItemID value. Here's one of the many expressions I tried: "Assigned Items": "<<[Assigned Items] + LIST([Duplicate item].[NewItemID])>>". Here's an example:
         1. Existing value for [Assigned Items] is e447bd88.
         2. NewItemID value is 64eaf18c.
         3. The expression returns only e447bd88 , , whereas I expecte447bd88 , 64eaf18c.

[AleksiAlkio]

I normally do this calculating the ID value with a formula to json so I can use the same in the next step. Maybe the webhook is not able to read it yet, this I haven't tested. Have you tested what happens if you trigger only the 2nd step after the 1st step is already done? Does it work then? If yes, then you know the reason.

[dbaum]

---

@AleksiAlkio wrote:

Have you tested what happens if you trigger only the 2nd step after the 1st step is already done?

---

In my outline, section 2.2 indeed already works reliably and depends on NewItemID. When the automation runs, that step works even though the step  in section 2.3.2 doesn't work--seemingly because any syntax I try using to reference NewItemID returns blank.

---

@AleksiAlkio wrote:

Maybe the webhook is not able to read it yet

---

Read what from where? NewItemID is already available within the automation process--its value is even listed as an output of the webhook step in the Automation Monitor. Here's the "Process > Steps > Detail" for the webhook task:

[AleksiAlkio]

Let me try this out tomorrow.. and if I could see the same behavior.