Best way to autoupdate column

[Mark_11]

Hello,
i have a table with some column i’d like to autoupdate like “status” “location” based on a changing in another table.
i’ve deleted the columns and create new ones as virtual column to achive the autoupdate on every sync.
Everything is fine except this system is not flexible at all. Could be some exceptions where i want to update that field with an action or a workflow to force a different value.
Any suggestion to find a way to achive a more flexible autoupdate?

[Steve]

Virtual columns will not work at all if you want to use actions or workflows.

We’ll need more details of what you’re trying to do to offer concrete suggestions.

[Mark_11]

Is not a specific thing, there are some different scenarios;
one of them for example:
i had an Enum column [Location] (parking lot, zone A, zone B)
Now i have another table “Order” that give me this information so i have switched the Enum column [Location], with a virtual column [Location] (lookup(maxrow(Order…)) so everything is automated and no one need to update the location that is great.
I also have a workflow with a condition [_THISROW_BEFORE].[Location] <> [_THISROW_AFTER].[Location] that seems doesn’t work anymore;
is because the virtual column change doesn’t trigger this formula?
If it is, i’m trying to figure out other possbile to way to manage such automations without loose features in the app.

[Steve]

Mark_11:

is because the virtual column change doesn’t trigger this formula?

Correct. Workflows are only triggered by changes to normal–not virtual–rows.

A big downside of virtual columns is the time it takes to calculate them. They’re recalculated each time the app syncs, and the time it takes grows as your data does. Expressions that look through tables, such as SELECT(), LOOKUP(), and MAXROW(), consume a lot of time very quickly, and are best avoided whenever practical.

Based on what you’ve shared, I suspect you would benefit by replacing the virtual column with a normal column and use an action to update the column’s value. The action could be attached to the Order form to update the location when the form is saved, or you could use a workflow to trigger the action when the updated form is synced.

[Mark_11]

So i need to go way more deep with actions and workflows;
the actual pattern is like, action/workflow that change something and then a cascade of virtual column to update a lot of field in various table that is such a great automation but at the same time i’m loosing other features. More work for me; nothing impossible, just way more tricky to adapt everything.

Thanks

[Mark_11]

Steve:

Based on what you’ve shared, I suspect you would benefit by replacing the virtual column with a normal column and use an action to update the column’s value. The action could be attached to the Order form to update the location when the form is saved, or you could use a workflow to trigger the action when the updated form is synced.

For the specific case.
Order table
I have an action “CLOSE” (set value…) [Status]=CLOSE
then i have [Location]=if([Status]=CLOSE,“parking lot”,“zone B”)

table TRUCK
vc [LOCATION]=lookup(max row…) that just pick the more recent location from the table Order based on a Truck’s plate.
How can i send this information from the Order table and automatically update the row on Truck without edit the row?

[Steve]

For the Truck table, you need an action to set the current location:

• For records of this table: Truck
• Do this: Data: set the value of some columns in this row
• Set these columns:
  • Location: (you current LOOKUP() expression)

For the Order table, you need an action to tell the truck to update itself when the order Status has changed to CLOSE:

• For records of this table: Order
• Do this: Data: execute an action on a set of rows
• Referenced table: Truck
• Referenced rows: LIST([truck-ref]) (replace truck-ref with the name of the column in the Order table that contains a Ref to the truck)
• Referenced action: (the action above that updates location)
• Only if this condition is true:

   AND(
      ("CLOSE" <> LOOKUP([_THISROW], "Order", "row-key", "Status")),
      ("CLOSE" = [Status])
    ) 

  replacing row-key with the name of the key column of the Order table.

Then, in the Order form view, attach the second action above as the Form Saved event action:

[Mark_11]

Thanks,
i would try this method that i didn’t know;
the second part is not accurate about my situation;

For the Order table, you need an action to tell the truck to update itself when the order Status has changed to CLOSE :

in the Order table i would always pick the “current” [Location] because it changes during the day, not only when the status switch to Close.

[Mark_11]

I’ve done exactly what you told me,
except for the

Only if this condition is true:
AND(
(“CLOSE” <> LOOKUP([_THISROW], “Order”, “row-key”, “Status”)),
(“CLOSE” = [Status])
)

in the second action, because as i said before, i’m not sure this is my scenario. I just left “true” as condition and tried activating manually both the actions.
Result:

• From table Truck, action (lookup(maxrow…) works perfectly, it updates my column extracting the exact value from Order table and trigger the workflow based on change value before/after;
• From table Order, action “to tell the truck to update itself”, seems ok too BUT in this case the change doesn’t triggers other workflow like the VC issue.
  At this point i have 2 issues,
• i need these 2 actions automatic during the various Orders steps
• the change caused by second action has to be valid for the [_THISROW_BEFORE].[Location] <> [_THISROW_AFTER].[Location]
  that trigger others worflows as well.

Thank you very much for help, i’m not far from the final goal with this App; i just need a couple automations more to be really worthy.

[Steve]

Mark_11:

i need these 2 actions automatic during the various Orders steps

There are two ways to achieve this:

1. Attach the Orders action from above to the “various Orders steps”. If the steps are done from a form view, attach the action to the form as I described above. If the steps are done as actions, add this action to those actions, perhaps as part of a grouped action.

2. Create a workflow for all updates (not adds or deletes) on the Orders table that performs the Orders action above.

Mark_11:

the change caused by second action has to be valid for the [_THISROW_BEFORE].[Location] <> [_THISROW_AFTER].[Location]
that trigger others worflows as well.

I don’t understand what you mean by this.

[Mark_11]

Ok @Steve ,
Part 1
the various Orders step, some are triggered by action in detail view, other are workflow with “change data” triggered by an enum choice inside the form…
so now i have:

• in table Truck a new action called “lookup location” (lookup(max…)
• in table Order a new action called “trigger lookup location”

Do i have to attach “trigger lookup location” in every scenario i suppose to want to trigger it? Am i right?

Part 2.
Table Truck
I have a workflow “record history” where if a certain number of columns change, it does “change data”; the trigger condition is
OR(
[_THISROW_BEFORE].[Location] <> [_THISROW_AFTER].[Location],
.
.
.
)
So if at least one column change i want to trigger this action.
Everthing was fine but this is why i started this thread; because 2 of these column were VC (one was Location) and this workflow didn’t works.
Now apparently this workflow doesn’t work also with this “trigger lookup location” action; the change of value seems doesn’t trigger it like the VC did.
But with some other test seems that this workflow doesn’t work as expected; i don’t know why at this point; is just an OR…

At this point i’d like to fix the first part with the auto update, then we will check th second part.

[Steve]

Mark_11:

Do i have to attach “trigger lookup location” in every scenario i suppose to want to trigger it?

Yep.