Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

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

Recommended Posts

Posted

i have a tricky problem, but one that many people must have come across before.

my solution requires that i create a unique code for all my contacts.

previously, i allowed my users to choose their own code and used 'validation' to ensure that there were no duplicates.

i now wish to automatically generate this code.

generally, the code is set to the first three letters of the contacts name and a numer starting with "01". the "01" is concantenated from a standard default number field. for example: if you enter "coca cola" as the contact name, the unique code becomes "COC01".

as a matter of interest, i have set the code to ignore the word "the" in contact names so that the entry "The National Brewery" would return "NAT01".

my real problem starts when i have duplicate entries. for example "John Smith" and "Johann Guttenberg" would both return "JOH01".

i have managed to script an alert that informs the user that the second entry is a duplicate. i have also given them a button that will set the number field to "02" (and they can continue to click over to "03", "04", and so on as long as there is a duplicate).

but, i cannot set the counter to automatically move up to "02" (or "03, 04" etc).

this would remove the task of manually clicking on the button until the entry is unique and would automatically give the user a unique code.

i suspect that a recursive solution might help me, but i do not have a developer edition. there must be an elegant workaround that i can employ.

many thanks for any input here.

Posted

I guess I don't see why this type of serial numbering is desired. I know this type of question comes up from time to time (there are similar threads about this somewhere in the forums,) but it seems like more trouble than it's worth (I generally use regular sequential serial numbers.) For one thing, what happens when a name changes?

I suppose one might use this type of serial numbering if they wanted to make it easier for the user to remember an ID or something. But it may be easier for the user to not know anything about the IDs at all. Simply have them find by the name or phone number.

If you have thousands of Contacts, then exposed IDs ARE useful for Finds as there may be many duplicate or similar names. But in this case, I still think regular numbers are better. First because your suggested numbering system would quickly become tangled with multiple contacts with the same prefix. Second, for Finds; typing all numbers is easier than typing letters and numbers.

This may not be the solution you were looking for, but hopefully you will consider it. If you DO have a compelling reason to use your numbering system, perhaps you can share what that reason is.

Posted

yes, it is simplt because my client service people need to access their contacts quickly and each may have a number of clients. it is far easier to remember a contact by a code that has some logic.

thanks

Posted

Okay, setting the issue of *why* aside, I don't think I would handle this with a CF. I would consider building the desired result as a concatenation made up of a text field populated from the first three characters of the name (except "the" etc as the case may be), and a number field that you store in the record which is populated with the max + 1 based on a self join by the text field.

Of course exactly what triggers the max + 1 would be contingent on your business rules. While an auto enter calc is one option, it would be most robust IMHO if the field were poplated by a script which would give you all kinds of validation options and control of error handling. Also, if the code is built from these two "source" fields by a script, you can store them all and not worry about any performance hits that you might have if they were live calc fields. Note that if you decide to allow users to "refresh" the code by running the script again, the script should clear the number field before recalculating max + 1.

Does that make sense, or should I explain in more detail?

Posted

jeremiah

thank you, i am not familiar with some of the terms you have used. if you have the time to be more specific - even give an example, i would greatly appreciate it.

in the meantime i will attempt to work through your advice

Posted

jeremiah - sorry but must add the following:

i am already getting the first three letters from the contact name and concantenating it with the number field. the number however is a static number field. the real issue is the "max + 1 based on a self join by the text field".

i have used a self join to identify the duplication, but i am not familiar with the "max + 1" concept and how the self join would work in this context.

thanks again

Posted

Okay, sorry for the long delay. (been a busy month!) Hope you can still use the advice.

Since you already understand the concept of a self join to find duplicates, you are half way there. What I am suggesting is that you make a self join relationship using the three letter text field as the key field. This will return the set of all records that have the same three letter prefix. Once you have established this set of records, it is then possible to calculate the maximum value of the number field using the calculation Max ( field ) + 1.

What Max of the self join does is return the highest value in the number field of all the records that share the same three letter prefix. If you simply add one to that value, you are ensured a unique value.

Posted

Max can be slow, depending on the number of records. You could also sort the relationship by field descending. Then the next number is relationship::field + 1.

This topic is 7182 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
×
×
  • Create New...

Important Information

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