Jump to content

Need help to create an automatic Ref. id


malagasy
 Share

This topic is 5792 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5792 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.