Parent record status field being reset sporadically with no code configured to do so.

[Daisy_Ramirez]

This one is a brain drain for sure. 

I have a detail form we use to capture data.

The detail form is based on the parent table (processing_hdr) and we have 3 child tables (inventory, work hours and images) displayed on the form as inline views. The user is able to edit fields (quick edit fields) on the form for the parent table. When a record is created the default status of the record is IN_PROGRESS_P.

The user inserts data as usual and at the end of data entry,

• the user would press an overlay button to email the data
• the system would set values for 3 key fields in the parent table including a status change to TRANSMIT and a change time stamp field that triggers a bot 
• the bot has a 10 minute wait time and then creates 3 files, adds the files to an email and sends the 2 emails.  
• it then sets the value of the parent table to FILE_SENT_P and the record is finished and should stay in this status.

The "sporadic" issue:  After the parent is set to FILE_SENT_P, we've noticed the parent record is being set back to IN_PROGRESS_P and the 3 key fields that were previously set are now blank. but the workflow is not configured to make this change.  I'm unable to find any other function that would update the parent record after FILE_SENT_P and I'm unable to replicate the issue myself.  The issue seems to happen with one employee pretty consistently and then across several employees sporadically.  Today happened to be a good day where one record was reverted back to IN_PROGRESS_P after FILE_SENT_P. The issue is critical as an IN_PROGRESS_P record allows for data to be entered when the record should be considered already processed.

I happen to know the status change is taking place back to IN_PROGRESS_P because I've inserted an action to snapshot the parent record and all of its fields at 3 points in the process: When the user presses the button, after the emails are sent in the bot and then after the status is changed to FILE_SENT_P (at the end of the bot). I've spent hours reviewing the audit log but can't find anything out of place and no errors.

I've spent hours and hours trying to figure out what could be causing this issue.  Does anyone have any ideas? Any help would be greatly appreciated!  Thanks so much

[Steve]

That you are using QuickEdit columns indicates you're using a detail view, not a form view. To use a detail view, the row must exist prior to opening the view. Are you taking steps to ensure each user--or even each device--is using its own row, so that no two users or devices are using the same row?

[Daisy_Ramirez]

Yes. Employees insert their emp ID before they begin collecting data.

[Steve]

Possible multiple employees are using the same ID? Or an employee is using multiple devices?

[Daisy_Ramirez]

Sorry for the delay Steve.  Yes each employee has their own ID.  The process is as follows:

• When the user selects "Processing" from the main menu,
• User inputs their employee_id into USERSETTINGS
• A sync happens
• User presses Processing from the menu again
• the app checks the processing_hdr table to see IF there's a record with IN_PROCESS_P status.
• If yes, the app displays that record in the detail view with Quick Edit fields etc.
• If no, the app creates a new record for the employee and then displays the detail view with the new record

Employees rarely use a different device as they have company assigned devices.

[Steve]

How do you prevent a user from entering another user's employee_id?

How are the key column values chosen for the case that no IN_PROCESS_P record exists? What is to prevent two users from getting new records created with the same key column value?

[Daisy_Ramirez]

Hi Steve,

• Nothing prevents an employee from inputting info for another employee. Productivity is tracked for each employee so it would not benefit them to input data for another employee. The app is ultimately a passthrough system and so the data is captured and emailed off for ftp processing in their main system. 
• In the case where there is no IN_PROCESS_P record, the system creates a new record and the key's initial value is set to UNIQUEID()

[scott192]

If you are using Google Sheets as the backend data store then there is a useful tool to be able to see the cell Edit History.  You can choose the cell that holds the status data and view edit history of that cell (right click menu) to get the exact date/time the changes were sent to the sheet.  See if there are any 'exact' timings between the status flipping back to the previous type that may indicate an automation is resetting things.

Also note this as I have seen similar things happen with actions that 'Add a row to another table using settings from this row' when the same row key is used.  I assumed that using the same row key to a record that already exists would instead Update the values in that existing record, but I have discovered that it also resets values randomly to fields that have a default value.  I do not know if this is expected behaviour or not. @Steve ?

[Daisy_Ramirez]

Interesting - thanks Scott.  I'll take a look and report back...

[Daisy_Ramirez]

Scott do you know if the time stamps in the Google Edit History are UTC by default?

[scott192]

Just go into the file menu of your Google Sheet and choose the Settings option right at the bottom.  This will tell you the Time zone for your sheet.

[Steve]

When an action of type Data: add a row to another table using values from this row is used to add a row that has the same key column value as an existing row, the existing row is overwritten entirely--the existing row is lost! The column values of the replacement row will be set as they would be for a new row, with no consideration for the existing row (unless you construct your expressions to do so).

[Daisy_Ramirez]

This is interesting but makes sense. If the key of the previous record is the same as the key of the new record, I could see the replacement row scenario you outlined above.  In this case, when a new record is created we're using a new UNIQUEID() for the key: 

• In the case where there is no IN_PROCESS_P record, the system creates a new record and the key's initial value is set to UNIQUEID()

Here is the add row action: