Unique Sequential Serial Numbers

[Guest]

Thanks in advance to anyone who can help me with this problem:

I am using FileMaker 4.1. I am setting up a videotape database with three types of categories, Masters, Show Reels, and Development Reels. I have a pop-up CATEGORY FIELD (text) for those three. I need to create a unique sequential serial number for each category; Masters starting at 100001, Show Reels at 200001, and Development Reels starting at 300001.

I know that I need to create self-relationships to count the number of each separate category and add 1 to a serial number based on that. I have tried to use examples that I found from others on the web, but I just can't seem to get it right. Can anybody help? I know my way around FileMaker pretty well, but when it comes to this type of complex programming, I get lost.

If anyone could give a precise step-by-step for at least one category or send a template that I can build off of, I can do the rest.

Thanks again to anyone who can help.

Joe

[email protected]

[Chuck]

First of all, is this a single-user or multi-user setup?

If it's single user, then you can use a global field to keep track of what the next unique serial number should be. For instance, you might have a field called gNext_Master_Num and set it to 100001. Then in your script for creating a new record, have the following type of code:

New Records/Request

Set Field [ Masters_ID, gNext_Master_Num ]

Set Field [ gNext_Master_Num, gNext_Master_Num + 1 ]

This won't work in a multi-user situation. If you have that, I'll go over how to get around that limitation.

Chuck

[Guest]

Chuck,

Thank you so much for your quick reply. Unfortunately, this database will be used in a multi-user scenario. I also want to avoid having to create any scripts for creating records, if possible. I think this is possible because I found an article (copied below) which describes exactly what I need, but the steps and the syntax are slightly unclear and possibly incorrect, and I can't get it to work. I have yet to hear back from the person who wrote this:

RE: MISSION POSSIBLE

Serial Numbers By Contact Type

Passwords & Privledges - a new ongoing series

By Dwayne Wright - LeSaux Media Services

[email protected]

Your mission...is to create a sequential serial number based on contact type.

In this case we have 2 types (Consumer or Corporate). Our goal is increment

the serial number for Consumers that starts with a 8 character and Corporates

with a 9 character. To allow for growth, we are going to use 9 characters to build

the code...

...The idea is to create a self-relationship by the contact type. Then see what

was the last number given (via an aggregate function - max) and

increment it by 1. The recipe is as follows...

=============================

1) Create a text field called Contact Type. Create two fields. Call one

calculation field called SelfRelationship Consumer and have it equal

"Consumer". Call one calculation field called SelfRelationship Corporate

and have it equal "Corporate".

2) Create 2 self relationships. One called SelfRelationship Consumer which

uses the key fields of SelfRelationship Consumer and Type. The other is

called SelfRelationship Corporate which uses the key fields of

SelfRelationship Corporate and Type.

3) Create another field called "Calculated Serial Number." Set the

calculation up as follows..

If(IsEmpty(Type), "",

If(Type = "Corporate", Max(SelfRelationship Corporate::Contract #) + 1,

If(Type = "Consumer", SelfRelationship Consumer) + 1, "")))

[end quote]

Sorry for this long posting, but this is exactly what I need, but I can't get it to work and I have not heard from the author of this post and am desperate.

Thanks for any help.

[This message has been edited by jpriv (edited December 03, 2000).]

[Chuck]

The most I can suggest right now is that you change the calculation to the following:

Case(

[LiveOak]

I think the basic method is sound, but I would make the serial number just a number field (not a calculated field) and use scripts for "New Corporate" and "New Consumer". This works fine and avoids the issue of circular references. -bd