I have a submit button putting data into a runprepupdate call on a page.
Maybe a worker goes home early, and I end up with two submissions for one shift.
How do I script to look at the table and perform a delete of redundant data after the entry?
I think if I can read the data from the table to my script, I can call a delete on the right data.
If redundant data should not be allowed, then the DB should be configured with a Unique Constraint on a column or combination of columns to insure the data is unique.
Barring that I suppose you could query the data and compare it to check for duplicate data prior to submission.
I highly recommend @lrose 's first suggestion over the second. The first one allows your scripting to be much easier as well and lets you write in the pythonic way of trying to do something and then failing gracefully. The second way you have a race condition and could have two clients who both check for a redundant record, both don’t see one, and then both insert, leaving you with a redundant record.
Check out this post - Insert into database only if the entry doesn't exist - #2 by bkarabinchak.psi
I want to keep the new submission.
I could call delete on all matching submissions before calling the new insert now that I think about it.
If I call delete and none are found, it is fine.
Then I don’t do a compare or read it in at all.
Though the race condition would exist if two users were separately entering data.
To eliminate the race condition, I need a scheduled script to run.
8.1.0 doesn’t have a scheduler. So I have to limp it along till October when I get an update maybe?
I could call delete on all matching submissions before calling the new insert now that I think about it.
If I call delete and none are found, it is fine. Then I don’t do a compare or read it in at all.
This just seems like a longer more painful way to to implement a UNIQUE index via scripting imo.
Personally I try to put as much of the data constraints in the database as much as possible as that is the only way you actually ensure some level of data integrity. Plus then you can try/except and catch the different db error types and know what went wrong - it makes scripting much easier imo.
I want to keep the most recent entry though.
did I misunderstand an option?
Oh ok I guess I didn’t understand either because you said you were going to delete the entries with a delete query? So I guess I thought you didn’t want to really keep the data lol.
If you want to keep the redundant row indefinitely - yes use scripting.
If you are just going to end up deleting the redundant row anyways - I don’t see the point and I would personally just use a UNIQUE index.
like shift is from 7 to 5 say
at 3pm someone’s got a family emergency, they submit their data and head out right away
I get one entry
the sub/alternate/next person to pick up the work and finish will submit end of shift
I get second entry
I want to preserve the last entry of the shift for that machine.
Right now, I have a manual delete button, but sometimes they don’t use it.
And sometimes, I would rather they didn’t have the button.
It doesn’t sound like you are designing a good process. I would check if an entry for the shift existed, and update it, possibly with a confirmation.
LOL of course I am designing a good process
it is a work in progress lol
how would you check?
how would you do the confirm?
You can make a UNIQUE index on any number of columns. So if you include the userId or whatever you call it, it would keep both employees record of their shift. What it would prevent is one employee making multiple records violating the same constraint. I am not exactly sure of your use case.
But for example imagine I have a table and imagine I only want to have one row per employee per shift
userId shift dateOfshift otherdata....
1 1 1/1/2022 blah blah
And you made a UNIQUE index on (userId, shift, dateOfShift). Then what this would prevent is an additional row for the same employee in the same shift on the same day - which makes sense for my use case. But then imagine a different worker worked the same shift the same day - that’s fine. Or the same worker working a second shift the same day - that is also fine.
I would read up on UNIQUE INDEX’s - based on what you are saying I do think they will still work for you.
does that block a new record overwriting the previous?
I want to keep the new record.
delete old record
Check if it exists with a select query, then you could use system.gui.confirm if this is in vision, i don’t know how you do that in perspective, probably with a popup.
I want to learn the way to do the check.
I don’t right now know how to in a script, look at the values in a query.
In my scenario it would block the same employee working the same shift on the same day. Ok if you want to keep the record - then use yes scripting. If you are just going to delete the record anyways after insert or after the next record makes and checks, then I would just use a unique index.
In that scenario you gave me before - a worker goes home, and another worker works the same shift - would be completely acceptable with the correct unique index definition. The purpose of a uinque key is to prevent specific kind of redundant entries you don’t want in the database table (imagine two employees with the same SSN - your db should reject that).
I would recommend reading up on what a Natural Key (and for that matter a surrogate key) is and see what natural key makes sense for your table. I like to make UNIQUE INDEXES on the natural key of my table as it makes the code flow much more easily.
Surrogate Key vs Natural Key Differences and When to Use in SQL Server.
oh, I think the same person might leave, handle the emergency, and come back.
In this case I get three records.
Them, the person covering, and them again.
At the end, I want to have that last entry preserved, and delete the other two old records at each new submit phase.
this is in perspective, not vision I meant to say
This sounds like the perfect scenario for the method described in the link in my first post - you try inserting the second time - you get rejected on the INSERT statement and use that opportunity to instead UPDATE the existing record. Repeating for the third record.
this is in perspective, not vision I meant to say
Doesn't matter - good database design is independent of all frontends.
that part about it being in perspective was for that comment about the way to implement the confirm feature
Check out this topic:
Someone put data into the wrong machine just now. (they could make an errant confirm if implemented)
Shortly after shift, a script will call a query to delete oldest of the redundant rows, and preserve the most current for each machine.
I will use Rank() for this.