Using Two Numbering Systems

[Russell Barlow]

I've currently hit a roadblock I can't seem to get past in a solution revision of a past build.

The past build that is being revised is really old, was designed in Filemaker (5-6) era and has only been thrown together. The obvious answer is the solution needs to be rebuilt, but the folks in charge don't want to work on that currently so it needs to be retrofit.

We use a numbering system that might be slightly odd.

Job Number: This is the number that is assigned to any job that enters house. This number can be duplicated for each re-order as long as their isn't physical file changes which causes it to receive a new job number.

Each classification of work has its own number range and format that is used.

Work1: 300000

Work2: 800000

Work3: 100000

Work4: 500000

Currently, there is a database for each of these number ranges and when new numbers are needed they have to open these databases and create a new record to "reserve" a number. This causes extra steps for data entry and I am curious if there is another way that I haven't thought of that will allow that step to be removed from the system.

Is there any way that a storage field can hold the current number for each of those Work classifications and it can be incremented upon request? Global storage would work great if it wasn't restricted to host only when on our network.

Thank you for any advice.

[Fitch]

You could set up self-joins based on work type and then use e.g. Max(relatedWork1::number) + 1.

Re: global storage, create a one-record table for that.

[LaRetta]

Tom, I would be concerned if this number was then used for relationships since it could break in multi-user and one could end up with duplicates. Am I misunderstanding your suggestion?

[Fitch]

If you commit the pref number first and trap for errors, you can exit the script if it's unable to update the number, or keep trying until it is able. The record locking would only happen in that split second.

Pseudo script:

error capture on

set a $variable to new ID

loop

- set pref field to var

- exit loop if success

- exit loop if too many tries (increment a counter)

end loop

go to job

new record

set id to var

[LaRetta]

Understood.

or keep trying until it is able.

And while a User sits there with their cursor in a field holding up the process, your other Users come unglued. And record locking is STILL possible. I do not think it possible using that method go guarantee it could not happen or hang up a process. Much depends upon the importance of the data, I suppose, but I've still heard of this type of process breaking. I would rather use a record number table explicitly. :wink2:

[Fitch]

The user would have no access to the field, it would ONLY be updated via script.

[LaRetta]

Alright, that makes sense then, Tom. Would Anthrax, if I hadn't have questioned, come up with your script for protections? I'm unsure the purpose of your graphic.

[Fitch]

I'm not promoting a perfect answer, just offering one possible solution. It's not completely clear to me what the OP really wants, which is why I kept my original answer brief.

You brought up a valid point about record locking. You brought up another valid point regarding "keep trying until it is able," but I had noted in the script that a counter should be used and the script should exit after a set number of attempts. It's just a pseudo-script though (the counter is not actually implemented), so maybe that wasn't clear at first glance. Hanging the user in an endless loop is Bad, no argument there.

What is the purpose of the graphic? THERE'S A WINDMILL IN HIS BEARD!

[LaRetta]

What is the purpose of the graphic? THERE'S A WINDMILL IN HIS BEARD!

Oh. I guess it's similar to the flying pig humor! I just couldn't see why the words were on there (telling my the argument was invalid which had nothing to do with a windmill). :wink2:

Edited April 16, 2009 by Guest
Corrected typo

[mr_vodka]

THERE'S A WINDMILL IN HIS BEARD!

I must say I wonder how much gel he had to put in his beard to get it to stand like that...

[Fitch]

It's just one of those internet memes, like icanhascheezburger. The meaning is.

The flying pig is another story. The point of it isn't humor, it's skepticism, as in "when pigs fly." I think I got the idea from James "the Amazing" Randi, and in fact you do see it still in at least one place on his site:

The Pigasus Awards

[Fitch]

By the way, Anthrax, welcome to FM Forums! Please rest assured, the majority of discussions here do not concern themselves in any way with pigs (flying or otherwise) or windmills (in beards or etc.)

[Lee Smith]

I think I got the idea from James "the Amazing" Randi, and ..snip...

And all of this time I thought you were quoting Flo, of [color:blue]Mel's Dinner fame.

Lee