Jlanderwerlen
You expressed that you were not sure of the goal and did not see work.
My primary goal for this thread is to pick the application’s optimal way to handle manual entry errors. Secondary goal is learning how suggested methods would work.
not an exhaustive list of options, pros, and cons, but here are the options being discussed:
option 1
Allow operators to curate with submit and delete. (this is the current system)
Pros:
If they make an error then they don’t lose any data until they choose to delete some data.
(unless the error is deleting the wrong data)
cons:
my queries are getting excessively complex to total the shift data due to having multiple rows
(maybe I can perform totaling with the hours from the manual entry table in a better way to mitigate this )
Option 2
if duplicate error on insert then update (unique clustered index), and operators curate with submit and delete (like if they entered data, but production was scrapped for something)
pros:
I will always have 1 row for each machine per shift
cons:
complexity is higher (unique clustered index is another thing for me to teach the team going forward)
Data is automatically deleted on an update (if red machine data is written to blue accidentally, then the data for blue machine is gone for that shift)
(can be reduced with a confirm popup)
Option 3
on submit button press, call delete on any machine and shift matching for the last 8 hours, then insert the row
pros:
not complex (no additional training for the team members)
I will always have 1 row for each machine per shift
cons:
Data can be automatically lost like in cons of option 2
Option 4
let operators curate with submit and delete, then at the end of shift, call a script to eliminate redundant rows per machine per shift, keeping only the last entry for each
pros:
I will always have 1 row for each machine per shift
Not complex, no additional training for team
If they make an error then they don’t lose any data until they choose to delete some data or at the shift end (unless the error is deleting incorrectly)
cons:
script scheduling in 8.1.0 is not optimal
not as robust as insert and update on exception
does not clean up the table in a prevention way on data entry
then additionally some tangents arise
like discussion of the 10 digit input, that is now a dropdown to eliminate some writing transpose errors (eliminates invalid numbers from being entered)
the dropdown is using a table for options to eliminate some reading transpose errors (limits available dropdown answers, eliminates errors entering valid numbers on invalid machines for those numbers)
another tangent was row_number() for this application instead of rank()
I hope that clarifies things, but also I can see how the amount of details can be too tedious.
I appreciate the help.