Value expression in a workflow to "reassign" Ref Order Id from one Order to Another

[Juan_Monroy]

I am using the Order Capture App (Customer, Orders, Orders Details, Products) as the template for my own App. After structuring the fields of my need and developing several actions and workflows, I wrote an Action that could “migrate” some Order Ids (Ref Column) from Order Details Worksheet to another one, already registered for same customer. In other words, I need to consolidate some Order Ids by changing the “donors” Order Id to the "receivers’ Order Id.

The problem that I’ve been facing is that this Action “Data: sets a value in a column”, when I use Expressions for the Value such as " TEXT(SELECT(Orders[Order Id], [Order Status]=“Destiny”)) ", the test verifies the Order Id of the receiver correctly, but when is submitted to the App, it returns the assignment blank for the correspondent rows. I tried to set the text value manually (both in the action with text characters and/or in the worksheet) and the App runs it perfectly. .

Is there something I’m missing out?

[Aleksi]

Is it possible that your SELECT is finding more than just one value? Instead of using TEXT expression, try to use ANY.

[Juan_Monroy · Accepted solution]

Found the problem within the Workflow IF THIS IS TRUE Condition.

Since I had many Actions related to each step of what I call the Consolidation Process, I established a broad open Condition so that the sequence of several (many) consecutive actions could be triggered. That was my mistake, cause I didn’t know that AppSheet is a redundant based software, which means that every time an Action modifies data, it might triggered the Workflow all over again. This could derived in chaos during the process. The way to solve it is by creating multiple Workflows that satisfy each and every precondition for those, and separately, to be triggered.

Lesson: Carefully verify your Workflow Criteria and don’t hesitate to create as many Workflows as long as each one, compared to the others and related to their Actions, do what they are intended to do.

Aleksi, thank you for being there for us…

[Juan_Monroy]

Short answer to your question is No. There is always just one return value, since in the Workflow, the User is asked to select only one Order Id (Orders Worksheet ) by temporarily modifying the Order Status to “Origin” and then only one Order Id as the receiver so the Order Status is assigned to “Destiny”. I tried ANY() in the first place, and then SUBSTITUTE() but seems like using Expressions to build a Value is apparently not valid in Ref Columns. I also tried to run the Workflow using the above Expressions with a standard Column (rather than Ref Column) and it worked smoothly, but of course it doesn’t do the job to consolidate certain Order Details in just one Order of same Customer.

Any other idea that could be used?

[Aleksi]

One solution how you can have better control… the formula AND(ISNOTBLANK([Column]),[_THISROW_BEFORE].[Column]<>[_THISROW_AFTER].[Column]) will check if that Column’s value is changed or not. If it’s changed, trigger the Workflow.