Jump to content
Sign in to follow this  
lolitafontaine

Complicated Concatenate

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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 )

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)



Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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 )

) ;

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

Share this post


Link to post
Share on other sites

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. :)

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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 ) ;

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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