Serial Number questions

[Nestor]

I don't know if this is possible, but hopefully it is.

what im trying to do is create a record and have a selection that will prompt me to select a Product Line such as "COR". So if i select COR and i comit to the record i was my Serial Number to be COR001. and if i create another record with the same Product LIne "COR" i would want my serial number to be COR002 and so on, NOW the problem that im having is. If i have 3 Product Line that i can choose from, and instead of selection "COR" i select "MIM" i would want my serial number to be MIM001, so i would want my serial number to follow the next consecutive number within my Product LIne. IS this possible? Any help or ideas would help me so much.

thanks

[Ocean West]

you could create a table that Manages your serial numbers.

Thru a script.

Grab the type of record your you need in your case "COR" put that in a variable

go to layout based on a table that has two fields: LINE and NUMBER.

find all records based on your variable from the LINE field.

Create a new record in this table. Putting the current found count number in to the NUMBER field. and set the LINE field what is in your variable.

Grab the concatenation of the two fields to a variable go back to your original table

and set the field where ever you need.

The benefit of this is that your numbers will always increment and they are all stored in one table.

[Nestor]

im trying it but nothing happens. Any way you can show me the script how it would look.

Thanks

[Ocean West]

here is a sample

SerialSample.fp7.zip

[comment]

Beware of conditional serial numbers - they could easily fail in a number of scenarios. Consider what happens when a record is deleted, or when a record is mistakenly assigned to the wrong category and needs to be re-assigned, or when two or more users are creating records at the same time.

I don't know of any reliable method to ensure conditional serial numbers are both unique and consecutive.

[comment]

I am very much puzzled by your method, Stephen. The additional table has fields for LINE and NUMBER. And so does the "real" table. What is the advantage of keeping duplicate records in another table? Couldn't you do the same thing in the original table?

[Ocean West]

well the only reason was that a separate table to keep ALL sequences of products then you can avoid issue with DELETED records in the data tables, and SN's being incremented and creating duplicates since data is missing. As far as multi-users crating records at the same time they would never get an identical record as your creating a new record, in a table.

The other benefit is that if you have to export import data into a cloned database you don't have to remember to reset your serial number settings as the next serial number is simply the found count for the current found set based on the script.

Plus you can control when the number is generated and not necessarily when the data record is created the serial number could be created at different point in time or even be stored in a child table.

[Ocean West]

this "serial number" i only recommend as an attribute of a record and don't advocate it to being a KEY for any relationships.

[comment]

you can avoid issue with DELETED records

OK, that makes sense.

As far as multi-users crating records at the same time they would never get an identical record as your creating a new record, in a table.

They could, if they are doing this more or less at the same time, i.e. if User 2 does the find before User 1 had a chance to commit a new record.

[Delights]

I believe you are trying to do the same thing that i have done on my database.

I created 2 more Tables, one is called "Serial Numbers" and the Other "Categories"

Categorys i made about 5 fields, Category_1, Category_2, Category_Name, Serial_Prefex, Serial_Number, Serial_Complete.

The Serial Prefex holds the First part of the Serial number, for example Adult games = AG.

Serial_Number holds the number generated, and then Serial complete puts the two part together.

For example Adult Games = AG0001.

Under Categories you make Fields called,

Category_ID, Full_Category_Name, Category_1, Category_2, Serial_Prefex.

link the two tables together.

Make drop down boxes on the Category_1 and Category_2 under the table Serial. Make Serial:Serial_Prefex lookup from Category:Serial_Prefex.

Set Serial_Number to be Serial Number,

Right a script that will make new records for you, personally i made mine make 500 records on the Category.

Only problem with this system is that you have about 35000 records just for serial numbers and also atm it dosnt seem to want to find active serial numbers if there is only a entery in the first field and nothing in the second field.

[Nestor]

Thanks for all the info. The example definitely does what i need. Thanks again.