Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

record locking

Posted on 2014-10-27
8
Medium Priority
?
131 Views
Last Modified: 2014-11-05
I have the following table structures:
Cartons:  CartonID (autoID), TrackingNumber, RANumber, DateReceived, etc...
CartonContents: ContentID (autoID), CartonID, etc...

RANumber, and sometimes even TrackingNumbers can be found on several cartons. In other words, records in table Cartons can look identical.

When it comes time to enter the contents, staff pull the boxes, several staff at the same time working the same batch of cartons.  I was planning on having them select a carton record and start entering the content data.  Then I thought it may be safer to have an unbound form that when clicking 'Add' it grabs an available CartonID.  The thing I need to guarantee about is that two staff don't try and use the same CartonID at the same time but either method seems it could be possible for users to 'clash'

Any suggestions?
0
Comment
Question by:UniqueData
  • 3
  • 3
  • 2
8 Comments
 
LVL 85
ID: 40408115
In other words, records in table Cartons can look identical.
This is somewhat troubling, since a single Record in a table really should be unique. If it's not, then either (a) you haven't really built the system correctly or (b) the Carton doesn't really need a unique table. Either way, a review of your process and business rules would be in order.

That said: If the records are identical, then why would you care if the users choose the same CartonID? It really wouldn't matter, unless you have to track specific Cartons (at which point my earlier comment would be very relevant).

Can you tell us more about your business, and what the real-world process is?
0
 
LVL 7

Author Comment

by:UniqueData
ID: 40408489
So a shipment comes in with printed rma labels. If it comes in truck freight, the shipping company assigns one tracking number to all the cartons. And if they are from the same customer, all the cartons will have the same rma number. So as far as labels, they will all be identical. So say 20 cartons come in with the same indentfiers. When it comes time to pull them and see what is inside (days later) each inspector would just grab a few of the twenty and enter details into the contents table. The contents would be tied to a carton but it doesnt matter which, as long as it hasnt been used by someone else already.

However, shipments that come in ups, fedex, and usps will usually have unique shipper tracking numbers. So when those get inspected there will be no worries about grabbing a carton id.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40408519
Doesn't the truck freight company or the original shipper put a "1 of n" number on the individual cartons along with the rma number?  How do they keep track of the pieces?  How do you fill out an insurance claim?

If push comes to shove and you really don't have anything unique on the specific carton, you can update a record to "lock" it.  Then, your form would check for the "lock" indicator and not let anyone else update the record.  When the update is complete, you have to unset the "lock".  You will also need a query to find and clear locks because invariably, locks get left on when you use this type of process.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 85
ID: 40408602
The contents would be tied to a carton but it doesnt matter which, as long as it hasnt been used by someone else already.
So if you receive a skid with 20 cartons on it, you'd have twenty identical records in your Cartons table. All you care is that you grab a record for the Carton+RMANumber+TrackingNumber that is not yet being used in the CartonContents table?

I agree with Pat - it would seem odd that a specific package could not be identified. I deal with a LOT of material movement, and I've yet to see that sort of setup.
0
 
LVL 7

Author Comment

by:UniqueData
ID: 40408620
I guess an alternative would be to have them go to a receivings table first and add an "end of receiving posting" that takes all the cartons that contain the same identifiers and makes them one record, with a qty of 20 in the Cartons table. However, the contents table would have things like 5 widgets, XL, 4 Blue thingys.  I would need to change that somehow so I know those two came in one carton so I can track how many cartons have been inspected and how many are outstanding.  But that could be a "group posting" process too so they enter all the contents of one box and post it so they all get an identifier indicating they were all one carton.

hmmm.. that may be the safest method.  Because updating a "lock" field still has the slight possibility that two users could grab the same carton id number and lock at the same exact moment, right?
0
 
LVL 40

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40408667
No.  Using the lock process will prevent that.  If two users read the same record and both try to lock it by updating the record, one will succeed and the second will fail with an error message from the database engine that someone has changed the record since you read it.
0
 
LVL 85
ID: 40408670
It's always possible, depending on the level of usage and such. Pat's suggestion to create a "Lock" field is something I've done in the past, and it works well enough for the most part.

If an RMANumber or TrackingNumber are unique, it might be worthwhile to consider something like this:

tRMA
-----------
ID
RMANumber
DateReceived
NumCartons
etc etc

Then associate CartonContents with tRMA, and have a field where the user could indicate which "carton" they're working on (i.e. 1 of 20, or 10 of 20, etc).
0
 
LVL 7

Author Closing Comment

by:UniqueData
ID: 40424807
I will go ahead and go this route.  Thanks for your time.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question