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?
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.
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.
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.
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
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?
For the Truck table, you need an action to set the current location:
For the Order table, you need an action to tell the truck to update itself when the order Status has changed to CLOSE:
LIST([truck-ref])
(replace truck-ref
with the name of the column in the Order table that contains a Ref to the truck)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:
Thanks,
i would try this method that i didnโt know;
the second part is not accurate about my situation;
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.
Iโve done exactly what you told me,
except for the
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:
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.
There are two ways to achieve this:
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.
Create a workflow for all updates (not adds or deletes) on the Orders table that performs the Orders action above.
I donโt understand what you mean by this.
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:
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.
Yep.
User | Count |
---|---|
40 | |
36 | |
33 | |
23 | |
17 |