I need to code in a case number that will be like this: Name: Jane Doe, Phone Number: (555) 555-1211, and the number of the investigation = "DOEJ1212-001" for the first record. If there is 5 records on this same person it would show "DOEJ1212-005". My issue is how to make the field called Case Number reflect this code.

Thanks,

Where does the 1212 come from in "DOEJ1212-001"? Are you taking that from the phone number (meaning a typo in the phone number posted) or is that a unique number that is assigned to each case? I'm guessing the phone number which is going to cause problems when her husband John Doe has a case or Janice Doe at phone number (123)456-1212 has a case.

My suggestion is to use a unique, auto-enter serial number for the case number. You can show how many case numbers a person has using the Count function. You could also have a portal listing all of the cases for that person.

1212 is a typo...it was the last 4 of the phone number. Using the count function is an idea, I will try that and have the result entered in to the case number calculation. Let you know how it works...

Unfortunately if I do a count of client id, which is a unique number to each contact, it counts the "total" client id's and not just the unique ones...

I guess I need a field to count the number of records that have the same "Full Name" entries in the "Full Name" field, as long as the "Full Name" contain the exact text. So if, Full Nmae = Jane Doe, and 5 records have "Jane Doe" the field will show "005". But if there are 6 records and one is John Doe, his field will show 001 and hers will show 005.

I'd suggest you explore this thread:

http://www.fmforums.com/forum/showtopic.php?tid/191695

See if the attached example helps you any.

Also I want to point out that I am using the Address in the dropdown list for selecting a contact as a quick and dirty way to identify the contact. In a real world file you would want to use their SSN. Else you will end up with 2 Bob Smiths at the same address (father & son) or 2 Jane Does at the same address (no relation, just a happy coincidence).

MyFile.zip

Edited December 12, 200718 yr by Guest

sbg, that was better than I was trying to do. Rather than have a complicated Case Number, just list all cases for any specific client. Perfect. Thanks, that did the trick.