Product ID Incremental Based on Product Type

[shanksta]

I have serveral product lines (ex: shoes, shirts, and pants). Each product line has its own product number series, shoes are the 1000 series (with id numbers being 1001, 1002, 1003...), shirts are the 3000 series (with id numbers being 3001, 3002, 3003...) and the other product lines follow suit.

When I create a new record I want FileMaker to automagically generate the next product id that is applicable when I select the product type from a drop down box.

In the backgound FileMaker should be doing something that I guess it would look like this:

Look at the PRODTYPE field and see that it is "Shoes"

Find the highest PRODTID where the PRODTYPE is also "Shoes"

Add 1 to that PRODID and place it in the one for the new record.

So therefore if I selected Shoes as the type but already has 2 records for shoes, filemaker would recognize 1002 as the highest number associated with the product type shoes and add 1 to give me 1003.

How can I go about doing this?

[LaRetta]

Hi shanksta,

There is a perfect example of this on the NightWing website. It's:

Independently Incrementing Serial Numbers

The demo uses Clients IDs combined with serial numbers but can be used for Product Type as well.

Cheers,

LaRetta

[shanksta]

Thanks that was a great link.

Im kind of new to FM, so I really dont know how to convert that example into something I can use.

As you said the client IDs are combined in the serial. I am looking at how to have those incremental serials based on ID - just like that example, which I kind of underdstand. However is there a way to make that ID part of the series I posted about?

Meaning if I used that as is, I would have "SHOES-0001" as a product ID, where I would rather have FileMaker see shoes immediately add the series to it.

After some thougt I see that the unique ids are good to implement, so now I have ID 1 for SHOES, but how can I add 1000 to that based on the product type. I think it would be easier to tell FileMaker to add 3000 to the id whenever it sees "SHIRTS: - but how do I do that?

[Leb i Sol]

looking at the sample file from above...any time u enter in ClientID (or for you "product name" being shoes,pants etc. ) you will generate what you need

shoes-001

just make a value list(custom) for the ClientID (esentially Product_Name for u) that will hold the names....

http://mostarnet.com/CustomSerial.zip

same file a bit edited.....

All the best!

[shanksta]

Thanks Leb -

Right now I basically have it so that there is now "pants" or "shoes" designation just a number that is incremental based on the product type.

I want to just add one CRUCIAL thing - add 1000, 3000, or 5000 based on the product type. This basically means that instead of starting at 1 for shoes I want to start at 1000, instead of 1 for pants I want to start at 3000, and so on...

How to I get different product types to start at different numbers? Or, if its easier, how can I add 1000, 3000, or 5000 (based on the product ID) after the serial has been generated.

[Leb i Sol]

look into Define Fields....and see that "000" is taken as a starting point that can be changed to 1000......perhpas a script with an IF statement...I will have to look into it if get the time.....perhaps more expereienced users will join...

until then

All the best!

[shanksta]

Im playing around with that "000" right now.

[CobaltSky]

Hello Shanksta,

Rather than playing around with the '000', you would be better off looking at having the starting reference range defined in a calculated prefix field which picks up the type field (the equivalent of the clientID field in the demo referred to above) and appends the apropriate starting range number. Eg:

Case(ProductType = "Shoes", "SHOES-1", ProductType = "Pants", "PANTS-2", ProductType = "Hats", "HATS-3")

Which you then reference your calculation in the serial number calc, viz:

YourCalc & "-" & Right("000" & serial, Max(3, Length(serial)))

You will end up with a final string that looks like SHOES-1001, SHOES-1002 or PANTS-2007 etc - which is what I gather you are after?!