How do I write this in a calcution, so that it works?

[Shanksta]

I posted this a while back but still cant seem to be able to get it working.

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

I want to automagically generate the next product id when I select the product type from a drop down box.

In code I guess it would look something like this:

IF ProdType = "Shoes"
THEN ProdID = >ProdID WHERE ProdType = "Shoes" +1

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 DO I DO THIS?

 

[lebisol]

"id when I select the product type from a drop down box"
= add a new record
then
just set the field_ID to be autoincrement in any format u like
eg. ABC1001 ABC1002....or ABC-1001 ..whatever
All the best!

> need more info?
:: don't click HERE ::

 

[Shanksta]

I dont get it.

 

[lebisol]

[shoes_id]1001....
[pants_id]2001 ....

under Define Fields create (u have) fileds above and set them to be an "number" and then double click on it and set the autoienter option to be the serial number from 1000 and incremented by 1.....any new record added will have a +1
on the same spot at the Validation tab set the range (max and min of 10001 --199999)
All done.....set the error message if user attemts to assign shoes mroe than 199999
TADA!
All the best!

> need more info?
:: don't click HERE ::

 

[Shanksta]

Ok so:

I made fields for each product line, with the autoincrement and validation set to be in each one's range...

Now how do I script it so that when I tell the database that I am adding a shirt item, the ID automatically becomes the shirt ID?

And what if I want to search do I have to put the item Id im searching for in that particular field, so that if I am searching for shirt item 1034, I have to enter that in the shirt ID box? I would rather have just one field called Product ID that is automatically generated based on the product I pick.

Maybe I just dont understand what you're trying to say.

 

[lebisol]

is this all in one file?
better yet
(I hope that) or do u have separate files for each category/product (shits.fp5,shoes.fp5 etc.) that are related?
if not then we have to do some scripting on the value list u spaeak of...and also the "auto-increment" concept would not work since it will auto increment all the fileds any time u add a new record.....

> need more info?
:: don't click HERE ::

 

[Shanksta]

Its all one file...

Here is what I have so far though:

Relationship to the same file setting PRODTYPE=PRODTYPE

PRODTYPE: Text Field, Indexed, Based on Value List
NextSerial: Calculation Field, =Max(products:serial) + 1
Serial: Number Field, Lookup to Next Serial through relationship

Everything works but the serials are just 1,2,3,4...
How to I add the 1000, 3000, or 5000 based on the product type?

For example if the product type was Shoes I would like to add 1000 and that would give me the new Product ID.

 

[lebisol]

http://www.fmforums.com/threads/showflat.php?Cat=&Number=78606&page=0&view=collapsed&sb=5&o=&fpart=1

;-)

> need more info?
:: don't click HERE ::

 

[Shanksta]

hehe

2 is better than 1 right?

 

[Mark817]

create a relationship with the same file based on the TYPE field. Then use the Sum(type::number)+1