How to autoenter a serial number based on a condition

[anoctopus]

You'll have to forgive me because this is my first foray with FileMaker and I've been asked to create a database for a film library.

I've tried searching for similar topics but unfortunately my total newness to FileMaker is preventing me from really even knowing what to look for.

So this is the situation: I have a Categories field with 9 options, and I need help with my Call Number field, and adding serial numbers. I would like to assign each category to a prefix # and then auto generate a serial number suffix. For example, a video in Category A will have the prefix 1xxx, Category B 2xxx, etc. I want to have the prefix generated when I select a category, and then have the serial number come in for the last 3 digits and generate consecutive numbers. Here's the tricky part, I would like the serial numbering to reset back to zero for each different category.

I was futzing around and I think I can figure out the first part of my problem in sort of a sloppy way with some throw away fields, but I would love any suggestions on how to do that in a neater and simpler way, and I have no idea how to restart the numbering in each category. Any help will be immensely appreciated. Thanks for your time!

[sbg2]

This comes up very often.

The best solution is not to do it:

http://www.fmforums.com/forum/showtopic.php?tid/191695/post/273995/#273995

[anoctopus]

Thanks, after some more poking around I found a few related threads. Sorry for repeating the topic.

I'm not concerned about duplicate serial numbers, this isn't going to be a database used by anyone but myself and after the initial entering of the existing library, additions will be sparse and seldom.

I have one question from the related threads I was reading. Creating a relationship is frequently mentioned but I'm not working with multiple databases so I don't understand what this is in reference to if not a relational database.

Again, thanks for the help.