Category and Sub-Category or Exclude empty relationship

[Vartan]

Hello everyone,

I read a lot of topics but didn't find the solution to my problem.

I'm about to build an inventory database. I'm using one table in wich I'm adding records. Each record has

- A category Name

- A category Number (wich must be calculated regarding name)

- A sub-category Name

- A sub-category Number (wich must be calculated regarding name and unique within each category)

- A product number wich must be unique within cat and sub-cat

If two records have the same category name they must have the same category number. Same within a category with sub-categories.

The solution I want to find is quite near serialisation by category (using self relationships), but not so near...

I also want to avoid skiping numbers in Category and sub-category numbers (or IDs) if ever I delete a record.

- Category numbers must be unique.

- Two sub categories can have the same number as far as they don't have the same Category number

- Two products can have the same number as far as they don't have the same Cat and Sub-Cat number.

- No problem if some Prod numbers are skiped.

I've tried to do something using lookups and calculations. Everything is fine and works if I type new records manually.

The problem comes when I import records. I'm using a Max function

AutoEnterCalculation NextCatNumber=Max(RelatedCatName::CatNumber)

that was not enough because of the case where there's no related records. So the auto enter calculation for CatNumber is 

AutoEnterCalculation ExternalRef ( RelatedCatName::CatNumber ; Max ( RelatedCatName: CatNumber )  + 1) 

to find the next Cat number, but this calculation is running all along the records (when I import some) making numbers higher and higher, not limited to the records wich have a category name.

I tried to make a relationship to exclude records with empty Cat name so that the Max calculation is made only on records with a Category name. To do that I added an "Empty" field and made a self relationship where CatName ≠ Empty. But that either doesn't work. It seems that FMP can't exclude records with an empty field in a relationship.

I'm not giving more details on my research so that the ones who read this can find an original solution.

Of course it would have been much more easier to make three tables and to enter sub-categories in a category portal, and products in a sub-category portal. But I can't do that because the products are in a shop and not sorted as in the database...:-)

Thanks to take a look at this.

Regards

Vartan

[Vartan]

Ok, so I've contnued to try another method. You'll find itn in the attached file.

And you'll see why I don't understand why it doesn't work.

I entered records sequentially, it works ....And don't

Try to enter some records and then delete one (not the last one) and re-enter and you'll se the ID numbers going wrong.

If someone has an explaination I would be glad to here it.

thanks for help

regards

Vartan

InventoryTest.fp7.zip

Edited July 27, 2006 by Guest