Create auto Client CODE

[emncwundy]

I have a client database which is related to the contacts database by __pkClientID

 

Clients can be one or more individuals with the same last name (eg Gemma and Ben Smith) OR

two or more individuals with different surnames (eg Gemma Kay and Ben Smith)

 

I want to have a calculation that automatically creates a ClientCODE eg:

Smith_G&B  (in the case of individuals with the same last name) OR

Kay&Smith (in the case of indviduals with different surnames).

 

Can anyone help me with such a calculation, where filemaker needs to firstly determine if the surnames in the related contact records are the same, and if so, then to go with Smith_G&B and if they are different, then to go with the second option, ie Kay&Smith??

[Lee Smith]

I hope you are not considering using this code as a Relationship Key.

 

Read about concatenating fields using the & operator.

 

I would need to see the names of your fields such as Last name, First Name, Mi, etc.

 

If the names are not separated, then you have a lot of more problems.

 

Your calculation will look something like this LName & "_" & Left FName

[LaRetta]

Hi emncwundy! 

 

As Lee says, this kind of code should not be used in relationships because if someone changes their name it will break your relationships but if you just need a quick code to give to Clients as a short-cut key or for quick finds etc, here you go.

 

Let ( [
unique = ValueListItems ( Get ( FileName ) ; "ContactLastName" ) 
] ;
Case ( 
ValueCount ( unique ) > 1 ; Substitute ( unique ; ¶ ; "&" ) ;
Contacts::LastName & "_" & Substitute ( List ( Contacts::cFirstInit ) ; ¶ ; "&" )
)
)

oh heck, here's a file (fmp12) showing it which is easier than explaining.  You need a value list, a calculation in Contacts to isolate the first initial (you could use custom function I suppose) and you need another table occurrence.

clients.zip

Edited March 18, 2013 by LaRetta

[emncwundy]

Hi Lee and LaRetta

 

Thanks for your suggestions. No i'm definitely not using it as a relationship key. That is set as a unique auto enter serial number.

 

This field will be used as LaRetta suggests, just as a short cut for finds, so ill have a sqiz at the sample file. thanks again

[emncwundy]

Hi Lee and LaRetta

 

Thanks for your suggestions. No i'm definitely not using it as a relationship key. That is set as a unique auto enter serial number.

 

This field will be used as LaRetta suggests, just as a short cut for finds.

 

LaRetta ive followed your instructions to create the c.FirstInitial in the contact table.  Ive also created another version of the contacts table and related it in the same way as the sample. However I am having some trouble with the result for those individuals with different surnames. It seems to be calculating the same was as  for people with the same surname. Perhaps ive got the context wrong? could you have a look at the attached screen shot to see if there are any glaring omissions?  thank you

c.ClientCODE.tiff

[LaRetta]

Your calculation is wrong on the value list.  You put Client_Contacts::Contact_Surname and it should be only the name of the value list itself in those quotes.

[emncwundy]

aha!  its working perfectly now thank you.

 

one more question - we have a very small number of clients who aren't individuals as such but companies with individuals who work for them.

 

I would like to be able to differentiate the c.ClientCODE for businesses or companies to reflect the business name instead of a combination of the individuals names.

 

Could i do this using a boolean style check box for Company, and then another field for the CompanyName, and have another line in the calculation that checks this parameter first, and if company is checked then it takes the company name as the code instead?

[LaRetta]

Yes, create a boolean number in Contacts called IsCompany (1 being yes) then wrap the calculation with Case() to test whether to use company or whether to use the calculation with the individuals, similar to this:

Case (
Contacts::IsCompany ; Contacts::CompanyName ;

Let ( [
unique = ValueListItems ( Get ( FileName ) ; "ContactLastName" ) 
] ;
Case ( 
ValueCount ( unique ) > 1 ; Substitute ( unique ; ¶ ; "&" ) ;
Contacts::LastName & "_" & Substitute ( List ( Contacts::cFirstInit ) ; ¶ ; "&" )
)  // end case
) // end let
) // end case

[emncwundy]

Hi there

 

I was having trouble with the formula above - so tried the following:

 

Case (Client_CONTACTS::IsCompany ;

 

Let ( [

unique = ValueListItems ( Get ( FileName ) ; "ContactSurname" )

] ;

Case (

ValueCount ( unique ) > 1 ; Substitute ( unique ; ¶ ; "&" ) ;

Client_CONTACTS::ContactSurname & "_" & Substitute ( List ( Client_CONTACTS::c.FirstInitial ) ; ¶ ; "&" )

)

)

 

)  // end case) // end let) // end case

 

 

But for some reason the above does not calculate for two individuals with differing surnames (not a company).

 

Please help! What am I doing wrong?

[LaRetta]

You dropped out the company name. Also make sure you matched spelling on value list within those quotes - you will not receive an error if it does not match.

Case (Client_CONTACTS::IsCompany ; CONTACT COMPANY NAME ;

Let ( [

unique = ValueListItems ( Get ( FileName ) ; "ContactSurname" )

] ;

Case (

ValueCount ( unique ) > 1 ; Substitute ( unique ; ¶ ; "&" ) ;

Client_CONTACTS::ContactSurname & "_" & Substitute ( List ( Client_CONTACTS::c.FirstInitial ) ; ¶ ; "&" )

) // end case

) // end let

) // end case

If still no go then i suggest that you create an empty clone, zip and attach your file.

[LaRetta]

I cannot use code while on iPad so if reading this from email it will not be right because of the brackets in the Let().

It also ran all lines together ( a bug from latest FM Forums upgrade ) and had to be corrected so what you received via email was probably junk. Neither can I colorize from iPad ( for clarity ).

Please sign in to get the correct calculation.

RANT: I hate auto-spell on iPad.

// END RANT

[LaRetta]

Also, Client_CONTACTS::c.FirstInitial calculation should NOT evaluate if it is a company!  So that should be changed to something like:

 

Case (

not IsCompany ; .... the regular first initial calc here ...

)

 

Sorry, I should have caught that before.