Serial values based upon a criteria

[iamthegrey]

Hi all!

I am new to the boards. but was told this was the place to come if I want to learn more about Filemaker. I have been using Filemaker since version 4 for basic things, some forms, some invoice-type calculation layouts, inventory management etc. But now I am working on something more involved and I have hit a wall.

Here is my problem. I have a customer who wants a asset management system developed (which is no problem - done a couple of these), but the problem arises when it gets to serial numbers. The customer is a graphics and printing company who inventories printed goods from 1000's of different customers. They would like each customers inventory to have it's own set of serial values.

For example, Company A has printed booklets in stock and printed calendars, Company B has printed booklets, printed calendars, and printed direct mail pieces. My customer would like these items to each have their own unique serial number within a unique serial list.

In other word, company A's iinventory report would like this:

CA001 - booklets

CA002 - calendars

Company B's would looik like this

CB001 - booklets

CB002 - calendars

CB003 - direct mail pieces.

So what I need really is a serial value field that can check against a company name and add one to the last record for that company only, not for the whole database.

I have no idea how to do this. I have tried to tackle it via a script, but my gut says this is a calculated field of some type, but for the life of me I don't know how to define the field. Any help would be greatly appreciated. Thanks!

Wade

[Oldfogey]

You could define a self-join, called ToMe, on CompanyName->CompanyName.

Then script -

Set Field (ItemNumber, 1 + Max(ToMe::ItemNumber))

Set Field Unique Number = CompanyName & numtoText(ItemNumber)

etc.

[iamthegrey]

Hey Paul:

Thanks! That worked great as a starting point on a new approach. I appreciate it!

[ayurgal]

I need exactly the same sort of thing, but I tried this in FileMaker 7 and couldn't get it to work. I got an error that it was expecting an operator. What am I doing wrong?

[-Queue-]

Oldfogey's script should read:

Set Field [itemNumber; 1 + Max(ToMe::ItemNumber)]

Set Field [unique Number; CompanyName & GetAsText(ItemNumber)]