Auto update multiple rows in table view after manually updating one row

[MauriceWhelan]

Hi.

I have had a request from a colleague based on the attached screenshot. It is a Production Plan grouped by [Area] with [Start],[Duration], and [Finish] columns among others. What we are looking to do is if the user changes the [Start] for [Job Card] 000023 from 07:30 to 08:30 all subsequent values for [Start] & [Finish] (by [Area]) in this View will update by +1Hr. Notwithstanding that updating subsequent rows will only be required for those rows whose work starts after the row that is updated. e.g. if [Job Card #] 000025 was updated, only 000026 would auto update.

I would need to implement the same behaviour if the [Duration] value changed also. For example if the [Duration] on [Job Card #] 000023 changed from 4hr to 5hr then that would change the finish time to 12.30 and update all subsequent [Start] & [Finish] times by 1HR.

Hope this makes some sense. Any advice on if this is achievable and how would be greatly appreciated.

Thank you.

[Marc_Dillon]

You’ll definitely want to be using the “Data: execute an action on a set of rows” Action type. This could be triggered by a workflow that triggers upon the change of the first record.

I think you’ll have a few different options to accomplish this, depending on your exact requirements. I’m thinking you’ll have a much easier time if you can limit the adjustments to just +/- 1 hour, or +/- 1/2 hour.

Check out this sample app:
https://www.appsheet.com/samples/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e73-...

[MauriceWhelan]

Hi @Marc_Dillon

I have implemented your suggestion based on the sample app. Hopefully this will suffice. Thanks so much for your assistance.

[MauriceWhelan]

Hi

I may have been a little presumptuous saying I have this working. I found a small issue in testing. The issue I am having is getting the [Duration] functionality working. As per above ‘if the [Duration] on [Job Card #] 000023 changed from 4hr to 5hr then that would change the finish time to 12.30 and update all subsequent [Start] & [Finish] times by 1HR.’ [Finish] is currently a formula so that will update correctly based on the [Duration] & [Start] so actually no need to factor this in really.

• I created a ‘Set the values of some columns in this row’ and set [Duration] to [Duration]+“000:15:00” and [Start] to [Start]+“000:15:00” to increment in 15 min intervals.

• My ‘Only if this condition is true’ expression needs to update the existing row’s [Duration] & [Finish] and all subsequent [Start] & [Finish] values. Do I need to create an additional ‘Set the values of some columns in this row’ action, group them and select that grouped action as the Reference action below?

The reason I ask is because I think I can only achieve my requirement with 2 different ‘Only if this condition is true’ expressions. One to increment the current row’s duration and another to ignore the current row’s [Start] but update all subsequent [Start]s.

I just can’t seem to get those expressions right because what is happening is all [Duration]s are updating by 15 mins and none of the subsequent [Start] times are updating.

My ‘Execute an action on a set of rows’ action is created with a Referenced Row expression
FILTER(“Routings_Master”,
AND(
[Routing Date]=[_THISROW].[Routing Date],
[Department]=[_THISROW].[Department],
[Equipment / Area]=[_THISROW].[Equipment / Area]
))

Referenced action as per above.

My ‘Only if this condition is true’ expression is [Status]=‘In Production’

Could you maybe advise if I am on the right track and what expression(s) might help to achieve what I need?

Many thanks.

Maurice.

[Marc_Dillon]

Nice work, sounds like you’re very close.

Here’s what I’d do:

Sounds like you’ll want 2 visible buttons for the user;

1. “add 15min to start time”
2. “add 15 min to duration”.
   These will both be “Grouped” actions.

Additionally I’d create 2 “set values of columns” actions;
3. [Start] = [Start] + 15
4. [Duration = [Duration] + 15

5. Then the 1 “execute an action on a set of rows”
   I think your referenced rows FILTER() is correct, but possibly you need to add one more condition to the AND() to only increment records after the one being edited.
   This action will trigger #3 action from above.

Visible “grouped” Action #1 will run #3 and #5.
Visible “grouped” Action #2 will run #4 and #5.

I’m not sure what you’re getting at with the “only if this condition is true” stuff. You may be misunderstanding the working of that.

[MauriceWhelan]

Hi @Marc_Dillon

Thank you for such a detailed reply. I think I have it working as per your suggestion. For some reason I was under the impression I would use the Referenced Row Expression to return the rows I need and then for the ‘Set the values of some rows’ action I would use the ‘Only If this condition is True’ function to dictate whether the functionality was triggered. I guess I misunderstood that piece in this instance.

Thank you again.