recycling serial numbers

[stanley]

The database I'm working on is for a silk-screening firm. Each design they use gets a 4-digit (soon to be 5-digit, as they are up to 9600 now) number, which is a legacy of their old (paper) system. On their new FMP system, when a sales rep takes an order for new art, the DB goes through the design DB, finds the highest number, adds one, and that's the new 4-digit number for the new design. This is fine. HOWEVER, when a sales rep then goes to cancel an order (which happens far too often for my hair line) this means that the 4-digit number will go unused. The owner of the company has just told me that this is no good, and that they want to make sure that all the numbers get used. Is there any way for me to go about this without rewriting the way the numbers are assigned in the first place?

The way that works is:

1. On a new order:

show all records in design DB

sort by design number

go to last record

new record # = (last record #) + 1

Thanks in advance

=Stanley

[stanley]

Additional info on above:

I forgot to mention that the record created for the design in the above case would naturally be empty. However, even the valid records are empty at first, and are filled slowly as the art creation process winds through the company.

thanks again

-Stanley

[djgogi]

Use some flagging scheme, like 0=deleted, 1=new, 2=closed ect.

Than first perform find on "deleted" and sort in descending order ect ect

Dj

[LiveOak]

Boy, do I understand this one. The problem is really in the owners head. If one of his business systems depends on not leaving unused numbers (I doubt), it needs to be changed. I have to deal with "numbers meaining things" all the time with clients. It is a symptom of a transition from an all paper system.

You can run a two part script. The first part looks for "unused" numbers (if "unused" can be defined) and uses them, if found. The second part uses the next highest number, if no "unused" numbers are found.

-bd

[stanley]

bd:

That's the kind of thing I had in mind. The problem is resolving what, exactly, constitutes an unused number. Because the record, when first created, has only the number in it, all records are (essentially) empty at first. Some time after the order is taken & the number is created, the art department adds an image to the record - that's the first data put in after the record is created. However, that could take weeks. Or minutes.

If a script is going to check and see if the number is lying dormant, the only way to go is to let it lie there for a month or something. I suppose the client will just have to live with that as the solution.

thanks

oh, and of course, it doesn't really matter that the numbers are currently going unused. It's all in the owner's head, as you say.

Stanley

[Vaughan]

Discuss with your boss the impact that two records with the same serial number would have: talk in dollar terms.

Alternatively (if possible) have a real serail number field that FMP uses internally to trck the records -- used for relationships etc.

[stanley]

Okay, it just gets weirder. I now discover that there have been a few occasions when the computer has assigned the same number twice. The method it uses is to show all records, sort by design number, go to the last record, take that design number, add 1, then create a new record and put the new number into the record. Seems simple enough. But it doesn't work perfectly. Is there another way to do this?

The problem with using a serial # is that the sales reps frequently cancel orders, and the owner wants the design numbers to be perfectly consecutive, with no jumps.

And of course, the client is always...

-Stanley

[BobWeaver]

If this is a multi-user database, then generating serial numbers by adding one to the maximum can occasionally create duplicates if two users try to generate a new number at the same time.

Regarding the client's requirement that numbers get recycled, if he used to have a paper system, it seems likely that the numbers would have been preprinted on the forms. So, if an order was cancelled, did he send out to the printer to have them print him up one new form with that serial number on it? Not likely. There is absolutely no good reason to re-assign serial numbers. There are just too many things that can go wrong.

[goostree]

If you can get your client to live with gaps between the serial numbers, the best way to assign these numbers is by using the auto-enter option for the serial number field. Go to Define Fields, select your serial # field, and click the options button. From there you can tell it to enter your new serial number when the record is created, and then you won't have to use a script and have duplicate serial numbers.

[Fitch]

A simple way to avoid "gaps" is just [color:red] keep the canceled orders in the databasecolor=red> . You could even sell this idea to your... customer... as a benefit: he'll be able to see how many orders are getting canceled. Whattaya think?

[Vaughan]

Ahhh, but he wasn't adding 1 to the maximum, he was adding 1 to the Record Count. This way lies much pain and suffering. Changing the script to work off the maximum will practically kill the duplicate problem.

However I like the idea of keeping the cancelled records as a way of seeing how much time and money is being wasted on dead-end jobs. Sell *that* to the boss. Just don't recycle serial numbers.

[per]

Hi.

How late is the orders normally cancelled?

In my travel agent system I have two serial number fields.

1 Is automatic generated and works as connecting fields for portals.

2 Is scprit generated when the order is finally and connected to the print button.

I got a lot of inquires which need an suggestion itinerary. I import this with a script into system. Give the client a "Offer-Print".

First when the client has confirmed the reservation he got the "Confirmation-print" with serial numbers.

Every time you go to the print button the script ask you if you would like to get an serial number - or wait.

You need some svripts inside the print scripts to do this.

If an order is cancelled after it has got the serial number you can print out a "Cancellation-Print" for your records together with the cancellation script.

Its also possible place two fields atop eachother:

The bottom field shows the serial numbers and the top fields is open for manually entrance.

Here you need 2 fields:

Serial number 1: Script generated: If Serial number 2 is Empty: +1

Serial number 2: Manually entered number there you can use the cancelled numbers.

You can also make a new database which works together with your order base:

When cancelling an order your script create a new row in the "Used Seriual Numbers DB" and put the cancelled serial number there.

A scpript in your order DB could search in this DB before it create a new serial number and at the same time delete the number from "old Serial DB.

If your boss will use the orders as statistics for day-sale this could be done with some extra caolculation fields to get an print with all registered and not registered orders sold that day.

Mail me yor fax-number and I will help you with the scripts if you want.

Good Luck!

-Per

[stanley]

Okay. There are some good ideas in there, but none of them address the issue I'm facing quite head-on. The problem isn't with a serial # - I only wish that was the problem. The problem is that each design which goes through the firm gets a unique number which customers can refer to, which is assigned when the order for the design is first taken. Part of the problem here is that this is a lousy model. Instead of waiting until the order for a new design reaches the art department (where separations are made, etc, at which time you can safely assume the firm will be using the design, and that it deserves a number) the design is assigned the moment the order is taken. This is so that customers can get the number immediately, when they place the order. Unfortunately, orders do get cancelled, in which case the number goes out the window.

One of the recent posts gave me this strange idea. What if I built a DB of possible design numbers. When an order is taken, it sorts that DB by number, takes the lowest one, and applies it to an order. When an order is cancelled, it creates a new record in this design number DB and posts the now-unused design number into it, to be found by the next created order. It is a weird way to do it, but it would work...

Once again, though, I must reiterate. The owner of the firm insists that the numbers be contiguous, that there are absolutely no gaps in the series of design numbers. He has his own reasons for this, and I can't argue with it. So, as the title of the post says, this is all about recycling - there needs to be a number when the order is taken, and if the order is cancelled, that number needs to be recycled and used again for another order. Yes it is insane, but it's the job.

thanks for all the help

Stanley

[BobWeaver]

I've used the system you described: having a separate file of available serial numbers. It works, but you have to be extremely careful how you set it up in a multi-user system or you can have a problem with the same number being simultaneously assigned to two designs.

Remember, you can get anything to work if you spend enough time and money on it. As Vaughan mentioned earlier, make sure to tell your customer that there are significant costs and reliability issues involved with making the thing behave the way he wants. Also, be prepared for other dumb ideas. When you get one impractical demand you usually get a bunch more. There are times when things get too ridiculous, you just have to walk away from a job.

[stanley]

Hopefully I'm avoiding the possibility of having a number assigned simultaneously to two different records. In the script that grabs the number from the numbers database, the very next script step deletes that record. I think two users would have to literally hit the "finish" button at the same time for them both to end up with the same number. Or am I wrong somehow?

-Stanley

[BobWeaver]

Generally, it's not a matter of "if" it will happen, but "when" it will happen. That is exactly what happened on a database that I had to fix. Everything was fine until they hired several new sales staff. Then, script collisions started and duplicate numbers resulted. The most reliable method I've found to prevent mult-user script collisions is to make sure that each user has some unique ID so that when a script is run, you can tell who is doing what. Plus, you need a field in the file that indicates whether the record is in use by a particular user. This is how I would write the script:

* Go to the first available serial number record

Go to record/request [first]

Loop

* Check the in_use field to see if another user's script

* has already claimed this record

Set Field [in_use, in_use & Status(CurrentUserName)]

Exit Loop if [Left(in_use,Length(Status(CurrentUserName))=Status(CurrentUserName)]

* Another user claimed the record a split second before this user, so abandon it,

* and go to the next record

Go to record/request [next, exit after last]

end loop

if [Left(in_use,Length(Status(CurrentUserName))<>Status(CurrentUserName)]

* at this point no unclaimed records were found

* so, give an error message and halt the script

Show message ["Sorry, no serial numbers available"]

Halt Script

end if

* If we have reached this stage, then the current serial number record

* is claimed by this user and we can do what we want with it.

* So, continue with script here....

Note that I use a set field statement to concatenate the current user name to the in_use field in order to claim the record. Normally, one might just check the field to see if it's empty, and if so, enter the current user name in order to claim it. But, that takes several steps and two users executing the script almost simultaneously could still get into a conflict.

Example: User A and User B execute a script almost simultaneously:

User A Script: Checks to see if the in_use field is empty and finds that it is empty. So, it is available to be used by user A.

User B Script: Checks to see if the in_use field is empty and finds that it is empty. So, it erroneously assumes that it is available to be used by user B.

User A Script: Inserts User A's name in the In_use field to claim it.

User B Script: Inserts User B's name in the In_use field to claim it, overwriting User A's name.

User A Script: Assuming that it has exclusively claimed the record, uses the serial number.

User B Script: Assuming that it has exclusively claimed the record, uses the same serial number as User A. Problem!!

Using a Set Field to concatenate the user name onto the end of the current contents of the in_use field, two scripts can be executed simultaneously and still correctly resolve who owns the record.

[Vaughan]

Most people would expect that a record with serial number xx2 would have been created after xx1. When serial numbers are recycled this will no longer be the case, frequently.

[BobWeaver]

I agree that recycling serial numbers is still a bad idea. But, the customer is always right. Although, through devious methods, it's sometimes possible to get the customer to come around to your point of view. The trick is to make him think it was his idea in the first place. I've managed to do this occasionally by simply asking some seemingly innocent questions of the form: "How do you want it to handle the situation where blah blah blah happens?" Of course you have to think up a situation that makes the customer's idea look utterly ridiculous, but don't actually make it look so obvious that that is your intention. Customer then says "Um, I'll get back to you on that," and the next time you talk to him he has a much better idea.

On large projects, there is always a project manager. "Project Manager" is the job title, but it disguises the true function which is actually client manager (ie, keeping the client in line). A good project manager is worth his/her weight in gold.

[stanley]

Bob:

Thank you. I had been trying to come up with something like that, but am also ankle-deep in debugging the system after it was brought on-line a week early - that is, the scheduled week of on-site debugging was skipped due to the client being in a hurry. But your script is right on the money.

I've got another facet to this system, where users search through the design DB to print out thumbnails of designs for clients. I knew I'd experience user collisions on the selected sets, and hadn't bothered to work out how to avoid them - concatenating the username to the selection status ("selected" or "not selected" thus becoming "selecteduser1" or selecteduser2" or "not selected", etc.) will do just fine. Thanks.

Regarding that later post about a project manager, I agree completely. Unfortunately I am wearing two hats on this one - project manager & programmer - which I will NEVER do again. As a programmer, my first response to "can it be done?" is "certainly." On this job (especially) I really need the project manager's answer, which is "that's going to cost you a bundle, but if you really want it..."

Thanks again

-Stanley