Automation reset on edit

[Aline_Mav]

We have a table of projects (contains key, project and task for multiple employees)  and a table for time entry (with primary key, project, task, employees). We created a behavior to create the lines in the time entry table depending on the project table. We followed these steps  (thanks @Steve ) and added an automation to be able to scheduled and activate the behavior when the data change in the project table.

Important points :

-the key in the time entry table is [project] +[task] + [employee] + [start of the week] because we don't want duplicates for the same week

-the behavior works well when creating new lines for a new week

We have a column for each day of the week. The problem we have is that when we have data manually entered by a user in the time entry table,  the automation , when activated, will reset the duration by the initial value.

We tried :

-set the "Reset on edit"  off in the time entry table

- In "Initial value" : [_THISROW_BEFORE].[Monday] 

-In "Initial value" : if([_THISROW_BEFORE].[Monday] <> [_THISROW_AFTER].[Monday] , [_THISROW_BEFORE].[Monday] ,"")

-In the behavior when we set the values, we added in [Monday]=[_THISROW_BEFORE]

Thank you!

[Rafael_ANEIC-PY]

Hello there,

The problem is that when a new record is added for a week that already has one, the ID that you constructed is repeated, and when AppSheet runs a data change action using an already existing ID it will modify that row instead of creating a new one, hence why your data is getting "updated" against your will.

You need to switch to an UNIQUEID() type of key column and only use your constructed key in order to detect and hide/delete the duplicates.

That, or you need to FORBID the user from creating a new entry for a week when there is already one.

[Aline_Mav]

Hi Rafael,

Thank you for your advice. We already tried with an UNIQUEID() and it is not the best because we wish to have only one entry : [project] +[task] + [employee] + [start of the week]. With the UNIQUEID() it creates new line with default value when the automation execution already added a line. By having a foreign key ([project] +[task] + [employee] + [start of the week]) it corrects that problem.

When we forbid the user from creating new lines, the behavior still reset the entry.

Here is the dataset :

Thank you 

[Rafael_ANEIC-PY]

Aline, from translating some of your columns I believe it is a timesheet you're working on, you might find it easier to develop it a bit differently.

Instead of having all of the days of the week in a single row in different columns, have each entry for each day in a separate row by only having a single column for collecting the time, and another column for saving the day it was created on.

You won't have to worry about your automations or formulas resetting by any reason because they will be independent from each other, and it makes for cleaner data that is easier to work with.
You also won't mind the duplicates because you could just ignore them.