Sequential Numbers within Categories

[CRHawk]

Hey Guys,

I am trying to set up a catalog system that groups sheet music based on the following criteria:

1) Category (Concert, Marches, Christmas, Jazz Band)

2) Grade (anything from 0.5 to 5.0 in increments of .5)

3) A sequential 3 digit number within each Category and Grade

To keep like categories and grades organized on the shelf, I would like to auto create a Catalog No. that is formatted as XX-00-000 whereas the first two letters are the first two letters of the category, the 2nd two numbers are the grade (multiplied x 10 to remove the decimal) and the last three digits is automatically generated after Category and Grade are selected, so that each piece within each grade & within each category starts at 001.

For example:

Concert Pieces at grade 0.5 would be catalogued CO-05-001, CO-05-002, etc.

Concert Pieces at grade 1.0 would be catalogued CO-10-001, CO-10-002, etc.

Marches at grade 0.5 would be catalogued for MA-05-001, MA-05-002, etc.

Right now I have the following fields:

Catalog (with drop down menu)

Grade (with drop down menu)

CatNo

For CatNo, I selected Text, Auto-Enter, Specify Calculated Value and put in the following:

Upper (Left ( Category ; 2 )) & "-" & Grade * 10 & "-"

That seems to work for the first two parts, but I’m clueless as to getting the 3 digit sequence at the end to do what I want.

I appreciate any help you can lend. I'm new to FM and haven't come close to wrapping my head around a lot of the terminology.

Chad

[David McQueen]

Create a categories table with one record per category. One of the fields you want is a numeric counter field.

Then each time you create a record in the catalogue table, after you pick the category for that record, you want the catalogue number to pick up the current value of counter for that category and then increment that value by one in the category table.

I used this technique in a multi location invoicing system with a few twists so that each center's invoices had meaningful (to the client) numbers attached to them.

[CRHawk]

David,

Thanks for responding. I think I've got it to work.

Edited June 22, 2010 by Guest