Serial Numbers

[Tom England]

On our client db we currently have a field ClientRecordNumber which auto enters a serial number upon record creation. My bosses now want 'potential' clients putting on the db, but I want these people to have a seperate serial number.

So basically I want a serial number for clients and one for potential clients. Can anyone suggest the best way to go about this??

Thanks

[danjacoby]

Off the top of my head:

Create to "SerialNumber" fields, one for clients & one for potential clients. Then create a radio button field to determine the status of each record. Finally, create a field that enters the appropriate serial number.

[Kurt Knippel]

What possible reason do you have for needing a secondary serial number? It should be enough to have a field that designates them as either a client or a potential client. I can think of all kinds of problems that a second serial number brings up, but I cannot think of any advantages.

[danjacoby]

What you can also do is have three fields -- a serial number field, a field that determines (via a radio button) whether the record is a client or a potential client, and a calc field that returns, say a letter ("C" or "P") depending on the status, plus the serial number. I'd put the serial number first; that way, you can still sort by the serial number, regardless of status.

Wonderful thing about FMP, there are often so many possible solutions.

[Kurt Knippel]

Although I still disagree with the need for the two numbers, Dan's suggestion is how I would approach it as well.

[Tom England]

The reason we want two serial numbers is as follows:

We have only just switched to Filemaker for our client database from an old OMNIS db system. For the time being, the OMNIS db still needs VERY minimal data as it is currently being used as a related db by a 'client policy info db'.

Therefore, I still need the client record number to tie in with that on my OMNIS db. Any 'potential clients added to the FM db would cause these numbers to go out of sync.

[Kurt Knippel]

Is the OMNIS system still have new data added to it?

[RussBaker]

I use something similar in one of my Mountain Bike Club membership systems. We have Full Members, Social Members and Admin Members. Depending on the membership type, the individual is allocated a membership number, so Full Members are 1 to 6999, Social Members 7000-8999 and Admin members are above 9000.

New membership numbers are created automatically by using a self-relationship based on membership type and then making the new member number as the maximum of the Relationship::Member Number + 1.

This way, everyone is still a "number" and its only one field, but its easy to determine from the number what their status is for race payments, insurance, colour of membership card etc.

[Tom England]

CaptKurt,

Yeah, as the OMNIS system is still running policy data - this will be added to, and hence some Client data still needs to be added to it - name & dob.

Russ Baker,

Could you please go into a little more detail about how you actually achieve this.

Thanks

[RussBaker]

Tom,

Below is the script I use to automatically set the Membership Number.

The fields in use are:

. MembershipType - a text field containing the Full, Social or Admin membership

. MembershipNumber - a number field which will be set to the members number

. LastName - a text field containing the members last name

. g_Full Membership Start Number - global number field containing the category start number (1)

. g_Social Membership Start Number - global number field containing the category start number (7000)

. g_Admin Membership Start Number - global number field containing the category start number (9000)

The relationship in use is:

. Self_MembersOfTypes - where MemberType is the key field and the relationship is sorted by descending MembershipNumber

The Script: (The show Message steps only have an "OK" button)

# Auto Assign membership number

Freeze Window

# Check that the Membership Type has been entered

If [ IsEmpty(MembershipType) ]

. Show Message [

[Tom England]

Thanks a lot.

It wouldn't work to start with, just kept starting each category with 1 then incrementing each with +1. But after fiddling around and generally changing things, then changing them back again, it is now working!!!

So it's not just Harry Kewell you've sent us thats of great use!!! Thanks