Stop User entering a "Bad" value. Serial Numbers (1 Viewer)

Jordonjd

Member
Local time
Today, 03:26
Joined
Jun 18, 2020
Messages
78
Afternoon Everyone,

My colleague has a Repair Database, items are booked in by serial, booked out by serial.

Sometimes multiple items (normally non OEM) have generic duplicate serial numbers. For receiving the items this is ok, but when sending the devices back out
they need to have genuine unique serials. (serial in > serial out)

My colleague asked me if there is a way of stopping someone from entering one of these "Bad" serials when shipping the units.
A user will populate a "Serial Out" field, either individually or by pasting multiple serials into a datasheet form.

One challenge is that we do not know these "bad" serials until we come across them and the serials received can my any number of lengths. formats and characters etc.

My first thought is to build a "bad serial table" something that is manually populated when the bad serials are found, maybe some query to review weekly to try and highlight the potentially bad serials. (something like looking for multiple repair id's from the same day sharing the same serial) for example.

Then I would try and use this table as a reference to stop any updates in these fields if they appear in this "Bad Serial Table"

My Questions are:

Does this seem like a plausible idea?
Is there possible a better direction i could tackle this from?
If it seems plausible does anyone have any advice for a datasheet friendly approach that would almost use a "in list" event as opposed to a "not in list"?


As always any feedback or thoughts are appreciated
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:26
Joined
May 21, 2018
Messages
5,902
What constitutes a 'bad' serial number? If simply a duplicate you normally just do a dcount to see if that number was already used.
 

Jordonjd

Member
Local time
Today, 03:26
Joined
Jun 18, 2020
Messages
78
What constitutes a 'bad' serial number? If simply a duplicate you normally just do a dcount to see if that number was already used.
Unfortunately there is no solid rule for "bad" serial number.
If a single unit or a mixed batch of units are received for repair, in that instance the serials would be unique, and historically those serials could have been received and shipped multiple times before, even under a different part number potentially.

The bad serial numbers are potentially found in the process of repair etc, but in reality it will only be the admin user that is either aware or finds them as and when.

this is why my first idea of a solution was a manually updated "Bad serial table" to then be used in some way to try and block these serials from being entered into the Serial Out field
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:26
Joined
Feb 19, 2013
Messages
13,770
you can also set the serial field to indexed, no duplicates which will stop duplicates coming in via a multiple paste. Basically a complete backstop. A not very helpful error will be generated along the lines of 'x number of records were not created due to duplicate values' - but won't tell you which ones.

Have to say adding records by copy paste may be convenient but is not a good way of adding records in a production ready application - it may be 'datasheet friendly' but has limited capability to manage the process. All sorts of potential issues as a result of wrong datatype, columns in a different order, values out of range - duplicate values, etc
 

Jordonjd

Member
Local time
Today, 03:26
Joined
Jun 18, 2020
Messages
78
you can also set the serial field to indexed, no duplicates which will stop duplicates coming in via a multiple paste. Basically a complete backstop. A not very helpful error will be generated along the lines of 'x number of records were not created due to duplicate values' - but won't tell you which ones.

Have to say adding records by copy paste may be convenient but is not a good way of adding records in a production ready application - it may be 'datasheet friendly' but has limited capability to manage the process. All sorts of potential issues as a result of wrong datatype, columns in a different order, values out of range - duplicate values, etc
Im afraid it cant be indexed, All historical serials from previous repairs are stored and there is always the possibility that the in that "Booking out" or "Shipping" that serial would be unique
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:26
Joined
May 21, 2018
Messages
5,902
Im afraid it cant be indexed,
You may likely need a composite index. A serial may not be unique, but maybe a serial is unique for any specific "shipping".
but in reality it will only be the admin user that is either aware or finds them as and when.
There has to be some rule that the admin user applies to say the SN is bad. What is that process?

What you suggest is technically very doable, but not sure if that is very foolproof. It may be if you constantly are getting a known set of bad SNs, but if this list is growing faster then you finding bad data then not that helpful.

Sometimes it is hard to come up with a single rule, but requires a nice User Interface to help search the data in lots of ways. Maybe if you can describe the process of finding and identifying a bad SN we can come up with something to speed up the process.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:26
Joined
May 21, 2018
Messages
5,902
So this thread (not related to your problem) demonstrates that a single rule to find bad data may be near impossible to find, but you can make a powerful UI that makes finding and fixing super fast. Sometimes that is the way to go. You may have to have features to check the data in many ways.
The OP had an import where the field was sometimes partially duplicate
Example
Carton 28 76 Carton 28 7
Glass 12 12 Glass 12 12

Coming up with a single rule to find and clean up would be near impossible. However, the interface makes it very easy to identify potential problems and clean them up:
Carton 28 76
Glass 12 12
 

Jordonjd

Member
Local time
Today, 03:26
Joined
Jun 18, 2020
Messages
78
Thankyou for the replies MajP,

Sorry about the following essay

This was a new question brought to me by my colleague this morning, and to my understanding the scenario is mainly:

example, items we purchase and use for repairs:
x100 non oem parts/units, their SN (serial number) is "abc123" , and all x100 have that same SN - This could be any format, unknown to us
There is either an ESN (electronic serial number) which is genuinely unique for each one, or a unique serial number can be assigned to them by us.

At this point we may be aware of our purchase having Generic duplicate SN's but it will only be at the point of repair or "booking out" that the new SN will be entered into the "Serial out" field. If this is missed and the Generic SN is used, it would only be stopped by a composite index if more than one was used within the same batch/shipment of repairs. If they are used in different repair batches individually then as below a composite index would catch it:

If the below were an example batch of repairs then the generic SN would not be picked up by a composite key:
Repair BatchJob IDShipment RefSerial OutComposite Key
51015abc12315-abc123
51115S1052333485815-S10523334858
51215H1JKl0015815-H1JKl00158


Example, items received from customer for repair:
x5 units are received for repair, 5 different items, one of which has a generic SN "abc123"
We are unaware that this item has a generic SN, also because it is the only unit having this generic SN in this batch of repairs it would unique and not be captured by a composite index.

---
Sadly in the second example there is almost not opportunity to identify this bad serial number unless it is very obvious like "xxx1" for example, otherwise the only way would be to review frequency or sn's being received and possible duplicates sn's within the same batch of repairs.

The format of these Generic serial numbers is random as well, so we cannot lookout for "xxx" or "abc"

Also for genuine products from different brands, the SN format is quite repetitive so a lot of items could look like "close to being duplicates"

Also i agree about the Datasheet and copying and pasting but it is how that is currently set out, and I agree some UI to try and locate these SN's is needed but it seems it will mostly be reviewing recent data to try and identify these bad SN's

Thanks to anyone who reads all this
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:26
Joined
Feb 28, 2001
Messages
21,542
The problem will always be how the computer can identify one of these "bad" serial numbers, followed by what it should do about it. But remember, Access is dumber than a box of rocks. YOU are the subject-matter expert. If you can't tell Access the rule, it has no way of knowing. So the first step is to lay out on paper the set of rules to apply to identify a bad serial number. IF and ONLY IF you have valid rules, THEN you can proceed to the step of coding up a solution.
 

Jordonjd

Member
Local time
Today, 03:26
Joined
Jun 18, 2020
Messages
78
The problem will always be how the computer can identify one of these "bad" serial numbers, followed by what it should do about it. But remember, Access is dumber than a box of rocks. YOU are the subject-matter expert. If you can't tell Access the rule, it has no way of knowing. So the first step is to lay out on paper the set of rules to apply to identify a bad serial number. IF and ONLY IF you have valid rules, THEN you can proceed to the step of coding up a solution.
This is the trouble, after speaking to my colleague i cant think of any rules to pick these up, other than review and add to a "Bad Serials Table" and somehow use that, otherwise i'm stumped, even then i wouldnt be sure how to reference this bad list as a way of stopping these entries on the Serial out field
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:26
Joined
May 21, 2018
Messages
5,902
x100 non oem parts/units, their SN (serial number) is "abc123" , and all x100 have that same SN
If I am following. You enter parts/units as they come in. Some of these will have generic SNs some will have unique. Without seeing your tables and what data you store I may be guessing. But if I get a bunch of x100 and enter them into the database I should easily be able to identify they have a generic SN so you could automate adding to the generic SN table.

PartID
x100 abc123 Generic
x200 def456 Generic

So in the after update of the "Serial Out" it should be able to alert me that abc123 is a generic SN and you need to update it. That is a simple dlookup. So I think you are on the correct track. The trick is to make it user friendly to identify the Generic SNs and add to the table.
Then you could even add a counter to the generic. Lets say you have shipped out 55 abc123 previously. You could automate your Serial Out to be abc123-56
 

Jordonjd

Member
Local time
Today, 03:26
Joined
Jun 18, 2020
Messages
78
If I am following. You enter parts/units as they come in. Some of these will have generic SNs some will have unique. Without seeing your tables and what data you store I may be guessing. But if I get a bunch of x100 and enter them into the database I should easily be able to identify they have a generic SN so you could automate adding to the generic SN table.

PartID
x100 abc123 Generic
x200 def456 Generic

So in the after update of the "Serial Out" it should be able to alert me that abc123 is a generic SN and you need to update it. That is a simple dlookup. So I think you are on the correct track. The trick is to make it user friendly to identify the Generic SNs and add to the table.
Then you could even add a counter to the generic. Lets say you have shipped out 55 abc123 previously. You could automate your Serial Out to be abc123-56

I see what you mean, we might be able to provide some automation on purchased items in that way.

The actual repair items is different but I'm sure like you said about the UI, there will be something that can be done there.

I did think about a dlookup but the form is datasheet and i think i remember crashing a few datasheets in the past attempting something along those lines. I was trying to think along the lines of marking the bad entries on the form and then looping through the controls somehow to check them and maybe make them enter "" somehow.

But to be fair i think with the scenario as it is we might need to rethink the whole booking out process and forms instead of trying to patch around it.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:26
Joined
May 21, 2018
Messages
5,902
Should be no issue with checking for a SN in the generic table/bad table using a dlookup or dcount. The trick is throughout the DB to be able to quickly add a value to the table when you identify it. You do not want to always pop open a form and type the item name and generic SN. You probably want a dbl click or some button to automatically add the values to the table. As you said it may be worth relooking at the process. If you have to alter some Serial Out, then maybe all records get a uniqe identifier on the way out.. As long as you and the customer and always reference that ID you should be fine.
 

Cronk

Registered User.
Local time
Today, 13:26
Joined
Jul 4, 2013
Messages
2,578
@Jordonjd Is it feasible to add your own unique ID number to each item received for repair?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:26
Joined
Feb 19, 2002
Messages
34,426
I would not create a separate table, I would add a new field to the existing one. Call it InvalidYN with a default of No. Then if someone identifies a SN as invalid, they check the box. That will prevent the item from being shipped out until the SN is repaired.

Since this is such a problem, I'm not sure why you are not validating the SN as you accept the device for repair. Wouldn't it be better to know that you have a bad SN up front?
 
Local time
Today, 02:26
Joined
Jul 25, 2020
Messages
58
I believe the solution is simple and its procedural as mentioned above.

" Since this is such a problem, I'm not sure why you are not validating the SN as you accept the device for repair. Wouldn't it be better to know that you have a bad SN up front?"
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 02:26
Joined
Sep 12, 2006
Messages
14,732
If the SN has been recorded incorrectly, why not change it, and document that the number was changed from x to y.
If you have recorded the part serial number accurately, how can it be "bad". The fact that it has already been issued before is not your problem, is it?

When the user keys in the Serial Number, just see if it is a duplicate, and ask the user to confirm it is correct.

The same part having the same serial number might be an issue, but if flange A and widget B have the same serial number, why would that matter.

Where you mention the composite key, that's an error I think.
You can use the shipment ref, and serial out separately, and then you might pick up duplicate serials, but it might not be an issue anyway. You can concatenate these fields for display purposes, but there's no benefit from storing the concatenated values.

I had a system where the supplier issued a unique 6-figure ticket number, and we used supplier + ticket number as a unique index.
About 15 years later we started getting problems, and it turned out that 1million tickets had been issued, and numbers were now repeating.

@MajP
So in the after update of the "Serial Out" it should be able to alert me that abc123 is a generic SN and you need to update it. That is a simple dlookup. So I think you are on the correct track. The trick is to make it user friendly to identify the Generic SNs and add to the table.
Then you could even add a counter to the generic. Lets say you have shipped out 55 abc123 previously. You could automate your Serial Out to be abc123-56

You need to think about this. If you store a part as abc123, and then store a second incidence as abc123-56, you will never think to check for abc123 with a suffix. The point is that if both abc123 values are correct, then you can't have the serial number as a unique value, and you need a way to manage the system with both instances being genuine. If you want to use a suffix, then I would add the suffix every time. And is it ever possible that the same job could have two parts that both share the same serial no, and then this strategy fails as well.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:26
Joined
May 21, 2018
Messages
5,902
@gemma-the-husky,
As described by the OP it would work (not saying I agree with the process).
Sometimes multiple items (normally non OEM) have generic duplicate serial numbers. For receiving the items this is ok, but when sending the devices back out
they need to have genuine unique serials. (serial in > serial out
If I understand correctly the serial in field does not have to be unique. So each x100 has the same serial in. On the way out the serial has to be unique. And there may be some other rules beyond uniqueness to say if the serial can be used. On the way out if the user enters the serial in as the serial out it could determine that is a generic serial (more than 1 in the serial in) and how many Serial Ins of Abc123 have been shipped. Then assign a unique suffix on the way out.
 

Jordonjd

Member
Local time
Today, 03:26
Joined
Jun 18, 2020
Messages
78
incredibly sorry for the late reply everyone,

Strangely I was dropped into two days of meetings with a partner company which happened to involve offering to potentially tailoring a bespoke mini erp for our company.

Ignoring that, MajP, Gemma-the-Husky, Jason-lee-hayes, Pat Hartman, Cronk and the Doc Man, i really appreciate your replies.

Stopping duplicated SN on entry would cause too much of an operational delay (xxx amount of devices are received throughout the day within the repair centre and to retrieve the genuine or assign a unique to the hardware takes time)

The validation of serial numbers is not necessarily information we have to hand currently

I was trying to lean away from querying the existing data too much as it turns out the database is very horizontal and is pretty much a very very large single table and querying the data normally seems to be quite slow,

The procedures and current set up are what they are at the moment, Iappreciate its not ideal but it is the reality, it is essentially a patch for the time being (again i know, not ideal)


I will make a copy the db and see what can be achieved, again thank you all very much and i will keep you all posted
 

oleronesoftwares

Registered User.
Local time
Yesterday, 18:26
Joined
Sep 22, 2014
Messages
668
I would not create a separate table, I would add a new field to the existing one. Call it InvalidYN with a default of No. Then if someone identifies a SN as invalid, they check the box. That will prevent the item from being shipped out until the SN is repaired.
Exactly my thought, then you can access it via a select query whenever you need to know the bad or good SN
 

Users who are viewing this thread

Top Bottom