Multiple serial numbers

[B. Keith]

I need to have a data base which hooks up to the accountants inventory module. They need to have unique product ID numbers for each product line.

I would like to be able to generate multiple serial ID numbers i.e. B1 through B1000, P1 through P1000 etc. I figured out how to use many small files to generate a serial numberand then suck it back intot he main database but the solution isn't very elegant and doesn't allow me to apply the 'unique' option.

Is there a way to just write a calculation that performs the serial function rather than using the built in one which only allows me one stream?

Bruce

BestCalculator001.zip

BestCalculator001.zip

[BobWeaver]

Most likely, but we need more info. When do you want to generate a "P" number and when do you want to generate a "B" number? Do they go in the same field or different fields?

[Gerd Muller]

Hi Bruce

First, you need a text field where you can enter the product line ("B", "P" and so on...). Make a self relation to that field.

create a number field. Now, every time you create a new record, you can set the text field to your product line. set the number file to Max([relation]::number)+1 . So, the next number for your productline will be entered.

Unfortunately, you can't set the number while you create a new record, because the product line field is empty, but with a script it would be possible, or you use wavemotions "oazium events", where you can trigger a script when you are changing the product line field...

The uniqe ID is easy then: ID = product line & number.

Have fun

Gerd

[B. Keith]

Thanks !

Okay,

so I wrote a script:

New record/Request

Set field (Section, "perennial")

Set Field (Product ID, "P" & (Max(To Itself::Product ID)+1)

Now when I trigger the script it sets the Product ID to P1. I wrote a script for several product lines and it works great. I now have my Product ID as B1 etc. for Bedding Plants and P1 etc. for perennials.

So now how do I trigger the scripts? I currently have 8 buttons lined up the side of the layout and you hit the appropriate one for a new product. This seems less than elegant. Can I put them in a value list and have different scripts trigger depending on the selection? THis will work better when I go to include the entire product line (about 40 different ones -- which will be unwieldy as buttons)

The end result is a customized method of entering new products with descriptions and additional info with specific fields that can be exported to tab delimited files with a unique product ID so that Accounting can get them for their inventory modul without all the extra stuff.

I'm halfway there, but this multiple product line stuff keeps making me think I should have 40 different DBs.

Thanks again

[BobWeaver]

No, no, no. The idea of a database is to keep similar information in one file. That's why you have fields to distinguish categories. When you break things into separate files, you run into serious problems when producing reports. For example, if you had a different database for every category, and then you wanted to generate an inventory total report, you would have to write a frightful script to search and total through every file, rather than a simple find/sort/go to layout in a single file.

attach_test.zip

[B. Keith]

Thanks !

Okay,

so I wrote a script:

New record/Request

Set field (Section, "perennial")

Set Field (Product ID, "P" & (Max(To Itself::Product ID)+1)

Now when I trigger the script it sets the Product ID to P1. I wrote a script for several product lines and it works great. I now have my Product ID as B1 etc. for Bedding Plants and P1 etc. for perennials.

So now how do I trigger the scripts? I currently have 8 buttons lined up the side of the layout and you hit the appropriate one for a new product. This seems less than elegant. Can I put them in a value list and have different scripts trigger depending on the selection? THis will work better when I go to include the entire product line (about 40 different ones -- which will be unwieldy as buttons)

The end result is a customized method of entering new products with descriptions and additional info with specific fields that can be exported to tab delimited files with a unique product ID so that Accounting can get them for their inventory modul without all the extra stuff.

I'm halfway there, but this multiple product line stuff keeps making me think I should have 40 different DBs.

Thanks again

[Gerd Muller]

Bruce,

don't search too far! I guess you made the self relation based on the field "section". So if you select a section, e.g. from a value list, you can always trigger the same script! It doesn't matter how many sections you have already entered. When you need a new section, just change the value list. The serial will begin at 1 for this section.

You could have one single button beside the section field, that you have to hit when you enter or change the section.

Keep in mind, that relations based on fields with "human meenings" are dangerous. What happens, when you have to change a name of a section?? better create a new db for the sections with two fields: section_id and section_name. Then you can base the relation on the section_id and relate to the name, which you can change any time.

Gerd