Consecutive Product ID number based on Product Type

[Shanksta]

Hi -

In a database I am trying to build I came upon a problem and am stuck trying to figure it out. In the database each product type (ex: toys, clothes, food) has a differnt ID number formatted in a different way. So that

toy 1 has a id number of 4012
toy 2 has a id number of 4013
and so on, with...
food 1 having a id number of 65013
food 2 having a id number of 65014

As you can see different products have a different format for their ID numbers.

I would like users when selecting the "product type" from a drop down to have the "product id number" automatically filled in with the correct format as well as the consecutive manner so that if someone added another toy the id number would automatically read 4014 for toy 3.

Whats the best way to do this?

Thanks

 

[Schlogg]

I don't know about "best" but here is a very clumsy solution.
Set up a separate file for each product type (feasible?) and link to them using the product type (Toy, Food, Thingie, etc.)
In these files set up the codes as serials - possibly a calculation of the type Base*100 + Serial.
In the master, set up a heap of overlapping portals - one for each product type and all transparent. The link for each portal needs to from Holder to Product Type in each sub-file. Holder is a (dynamic) field containing the Product Type you want to view. The only portal which will show wil be the one for the particular product type.
You could try a self-join version of this with a calculated auto-enter for each product type -
Number = Base * 100 + max(ProdType::Sequence) + 1

If nothing else, the above might give you some better ideas.


Cheers,
Paul J.

 

[lebisol]

"another toy the id number would automatically read 4014 for toy 3."
concepts to look into:
----------------
related files,portal, Lookup field (or calculated field=::that_other_field)
------------------

relationship:
file1====relationship====file2



portal in file1:
==============================================
select toy:[Toy 3]
"auto-enter-appear"[lookup field from file2 field Tag_ID]
==============================================

if u need samples and help: lebisol2@_NOSPAM_hotmail.com
all the best

 

[lebisol]

also: define the toy_id to be auto-enter and it will be incremented along with the creation of a new record!