Complicated Concatenate

[lolitafontaine]

Hi!

Each person in my database has a title, and that title determines their eligibility for various important stuff. Each "component" of a title must be tracked seperately, but the official title needs to be a concatenation or amalgamation of the individual components.

For example - here are two titles:

1) University / Professor

2) Albert Weatherhead / Honorary / University / Professor / of / Philosophy / and History / and / Former President / of the / School of Public Health / , / Emeritus

Each "/" indicates a seperate field, and there are 18 of them. Even simple Example #1 above is problemmatic - "University" is not Field#1 - it is Field#3 (in order, reading left to right), so if I write formula: fieldCONCATENATED_TITLE = Field#1 & " " & Field#2 & " " & Field#3 etc., I'll get two blank spaces before the title, which is bad. For Example #2 above, out of the 18 fields only 9 are being used. (I intend to insert the prepositional phrases (underlined) as dropdown fields themselves, so there are really 10 additional fields).

How do I do this neatly? I know I'll have to override plenty of the titles I insert, but there should be some way I can write a formula which can pull everything together for me, more or less. Like: If Field#1 is valid, then print Field#1 and insert a space after it and move forward to Field#2, if Field#1 is empty, skip it and move on to Field#2, etc., etc.

Any ideas?

[mr_vodka]

Try something like:

Trim ( 

       Case (not IsEmpty (field1), field1 & " ") &

       Case (not IsEmpty (field2), field2 & " ") &

       Case (not IsEmpty (field3), field3 & " ") &

       Case (not IsEmpty (field4), field4 & " ") &

       Case (not IsEmpty (field5), field5 & " ")

     )

*Edit

oh I forgot you could also use

TrimAll ( field1 & " field2 & " " & field3 & " " & field4 & " " & field5; 0; 0 )

[Vaughan]

The Trim() function is handy for removing blank space.

If you are using FMP 7 and later look for the custom function Trim4() at Brian Dunning's site.

[lolitafontaine]

Thanks, guys! I'll give this a try!!

[lolitafontaine]

Well - that worked, almost entirely, but I am having one little problem. Here's the formula as I use it:

Trim (

Case (not IsEmpty (a_t_Prefix1_Exception); a_t_Prefix1_Exception & " ") &

Case (not IsEmpty (a_t_Prefix2_Endowment); a_t_Prefix2_Endowment & " ") &

Case (not IsEmpty (a_t_Prefix3_Modifier); a_t_Prefix3_Modifier & " ") &

Case (not IsEmpty (a_t_Prefix4_Qualifier_Ranking); a_t_Prefix4_Qualifier_Ranking & " ") &

Case (not IsEmpty (a_t_Appointment_Role_Position); a_t_Appointment_Role_Position & " ") &

Case (not IsEmpty (a_t_Detail); a_t_Detail & " ") &

Case (not IsEmpty (a_t_Preposition1); a_t_Preposition1 & " ") &

Case (not IsEmpty (a_t_Dept_Committee_Division); a_t_Dept_Committee_Division & " ") &

Case (not IsEmpty (a_t_Preposition2); a_t_Preposition2 & " ") &

Case (not IsEmpty (a_t_Department1); a_t_Department1 & " ") &

Case (not IsEmpty (a_t_Preposition3); a_t_Preposition3 & " ") &

Case (not IsEmpty (a_t_Department2); a_t_Department2 & " ") &

Case (not IsEmpty (a_t_Preposition4); a_t_Preposition4 & " ") &

Case (not IsEmpty (a_t_House_Center_Office); a_t_House_Center_Office & " ") &

Case (not IsEmpty (a_t_Preposition5); a_t_Preposition5 & " ") &

Case (not IsEmpty (a_t_School); a_t_School & " ") &

Case (not IsEmpty (a_t_Preposition6); a_t_Preposition6 & " ") &

Case (not IsEmpty (a_t_Endowment_Suffix); a_t_Endowment_Suffix & " ") &

Case (not IsEmpty (a_t_Preposition7); a_t_Preposition7 & " ") &

Case (not IsEmpty (a_t_2nd_Qualifier); a_t_2nd_Qualifier & " ") &

Case (not IsEmpty (a_t_Add_Appointment_Role); a_t_Add_Appointment_Role & " ") &

Case (not IsEmpty (a_t_Preposition8); a_t_Preposition8 & " ") &

Case (not IsEmpty (a_t_Add_Department); a_t_Add_Department & " ") &

Case (not IsEmpty (a_t_Preposition9); a_t_Preposition9 & " ") &

Case (not IsEmpty (a_t_2nd House_Center_Office); a_t_2nd House_Center_Office & " ") &

Case (not IsEmpty (a_t_Preposition10); a_t_Preposition10 & " ") &

Case (not IsEmpty (a_t_2nd School); a_t_2nd School & " ") &

Case (not IsEmpty (a_t_Emeritus); ", " & a_t_Emeritus)

)

The problem lies with the last "Case" line - Emeritus. This is the only place in the title where I need punctuation, so I've inserted the comma and space there before instructing the system to insert the value in field:a_t_Emeritus. Even though everything is trimming properly up to this point, the system is inserting a space /i/before/i/ the comma, too. How do I get rid of that? For example, the result of the formula should read:

Charles Bullard Professor of Forestry, Emeritus

but is reading:

Charles Bullard Professor of Forestry , Emeritus

Any suggestions?

Thanks! Lisa

[mr_vodka]

Hi Lisa,

Try reversing the order of the field and the " " space then such as.

Case (not IsEmpty (a_t_Prefix1_Exception); " " & a_t_Prefix1_Exception )

Also since the last time you posted, I had made an edit to my post. I had forgotten about the TrimAll function.

Therefore, you can simply try:

TrimAll ( a_t_Prefix1_Exception & " " &

           a_t_Prefix2_Endowment & " " &

           a_t_Prefix3_Modifier & " " & 

           a_t_Prefix4_Qualifier_Ran king & " " &

           a_t_Appointment_Role_Posi tion & " " &

           a_t_Detail & " " &

           a_t_Preposition1 & " " &

           a_t_Dept_Committee_Divisi on & " " &

           a_t_Preposition2 & " " &

           a_t_Department1 & " " &

           a_t_Preposition3 & " " &

           a_t_Department2 & " " &

           a_t_Preposition4 & " " &

           a_t_House_Center_Office & " " &

           a_t_Preposition5 & " " &

           a_t_School & " " &

           a_t_Preposition6 & " " &

           a_t_Endowment_Suffix & " " &

           a_t_Preposition7 & " " &

           a_t_2nd_Qualifier & " " &

           a_t_Add_Appointment_Role & " " &

           a_t_Preposition8 & " " &

           a_t_Add_Department & " " &

           a_t_Preposition9 & " " &

           a_t_2nd House_Center_Office & " " &

           a_t_Preposition10 & " " &

           a_t_2nd School

         ; 0; 0 

        )

&

Case (not IsEmpty (a_t_Emeritus); ", " & a_t_Emeritus)

[comment]

Please state your exact version. It doesn't seem like you are using version 6 as your profile indicates.

In any case, you could end by substituting " ," with ",".

Edited July 24, 2007 by Guest

[comment]

In version 8.5 and above, you could do:

Substitute (

List ( 

a_t_Prefix1_Exception ; 

a_t_Prefix2_Endowment ; 

a_t_Prefix3_Modifier ; 

a_t_Prefix4_Qualifier_Ranking ; 

a_t_Appointment_Role_Position ; 

a_t_Detail ; 

a_t_Preposition1 ; 

a_t_Dept_Committee_Division ; 

a_t_Preposition2 ; 

a_t_Department1 ; 

a_t_Preposition3 ; 

a_t_Department2 ; 

a_t_Preposition4 ; 

a_t_House_Center_Office ; 

a_t_Preposition5 ; 

a_t_School ; 

a_t_Preposition6 ; 

a_t_Endowment_Suffix ; 

a_t_Preposition7 ; 

a_t_2nd_Qualifier ; 

a_t_Add_Appointment_Role ; 

a_t_Preposition8 ; 

a_t_Add_Department ; 

a_t_Preposition9 ; 

a_t_2nd House_Center_Office ; 

a_t_Preposition10 ; 

a_t_2nd School ;

Case ( not IsEmpty ( a_t_Emeritus ) ; ", " & a_t_Emeritus )

) ;

[ ¶ ; " " ] ; [ " ," ; "," ] )

[mr_vodka]

Please state your exact version. It doesn't seem like you are using version 6 as your profile indicates.

In any case, you could end by substituting " ," with ",".

Ah. Now I remembered why I shunned away from TrimAll. That was right they were on FM6 according to the profile of the orig post so a FM6 solution was I posted.

Also I agree with you that using the List() would be better if on FM8.5 or 9. :)

[lolitafontaine]

Hey all,

OK - I tried the Substitute (List) idea and that does remove the extra space in front of the Emeritus, but now I have another wrinkle (of course!) I need to make some of the fields "non-printing". Specifically, I need to assign a school to each title, but I don't always want the name of that school to carry over into the calculated text.

For example, John's fields include:

a_t_Appointment_Role_Position = "Professor"

a_t_Preposition1 = "of"

a_t_Department1 = "History"

a_t_School = "Faculty of Arts and Sciences"

a_t_Emeritus = "Emeritus"

I'd like the calculated text to read only "Professor of History, Emeritus". Using the original suggestion above, I created field:a_t_School_printflag, made it into a checkbox, and then wrote that line in the formula to read:

Case ((not IsEmpty (a_t_School) and IsEmpty (a_t_School_printflag)); a_t_School & " ") &

That worked, but now I don't know how to do it with the Substitute (List) formula. Will you continue to teach me, o gurus of Filemaker? I really really appreciate it!

Thanks - Lisa

OH - and how do I change my user preferences to indicate my new version of Filemaker? I looked under "My Profile" but can't seem to find it...

Edited July 24, 2007 by Guest

[mr_vodka]

Just put a calc to check if it needs to display that field based on the flag field in the order of where you want it to be, within the List function.

Case (IsEmpty (a_t_School_printflag); a_t_School ) ;

[lolitafontaine]

That worked! Thanks so much for all your help!