Need help to create an automatic Ref. id

[malagasy]

Hi,

I am all new to Filemaker Pro.

I have my own business and developing a database with my customers informations.

I have a questions I will probably laugth about in a few weeks after some experience but:

I want my customer reference id to be automatically created using the following informations:

-a set of letters (in this case, "NC")

-the first 3 letters of the last name of client

-the first 2 letters of the first name of client

For instance, if customer is named Mr David Jones. The id of customer should be NCJONDA .

Do u get it ? I hope u'll be able to help.

Cheers

David

[Ender]

Welcome David,

I think you'll be happier in the long run if you abandon this ID scheme now. There are too many ways it can fail, and the workarounds are not pleasant. For example, what happens when Danielle Johnson moves into the neighborhood and becomes your customer? What happens if she later moves to another state or changes her name?

No, any scheme that uses the customer data as part of the ID numbering is bound to cause you headaches. Instead, keep your relational IDs completely independant of the record data. I recommend using a simple auto-entered serial number.

If your thought was to make searches on the customer info easier, then just put those relevant fields on a search layout. If your thought was to make it easy to tell where a customer is from, then just include the City and State on the layouts or reports.

[GC GYM]

Yes David you have just been given good advice. I made this mistake at first too.

However, I still create a similar type of field sometimes to make a single field containing a few key identifying words to make it easy to search on multiple possible criteria from one field. e.g. Name, Phone 1, Phone 2, Address line 1 etc.

As you have been corectly advised - do not use this field to create a relationship, just as you wouldn't use FirstName or LastName.

Note that the inclusion of spaces as I've shown in the second example will enable a search to recognise each section of your 'identifying data field'. If you really just want the string of characters as you posted, use the first version.

Example as requested

1. Create a new field called customer reference id

2. Define it as a calculation

3. Define this calculation as

"NC"&Left(Lastname,3)&Left(FirstName,2)

Example to include spaces in the result

1. Create a new field called customer reference id

2. Define it as a calculation

3. Define this calculation as

"NC "&Left(Lastname,3) & " " & Left(FirstName,2)

Hope this helps

Phil

[malagasy]

Thanks for your insights guys.

I'll take good note of it and just make a random serial happening then.