agedpom Posted January 28, 2005 Share Posted January 28, 2005 I am having trouble with some concatenation logic. I have 6 fields dealing with names, company titles and company names. They are Personal title First name middle initial Last Name Company position Company name. Any or most of these fields may be empty and the concatenation includes spaces and, in two cases, a comma. eg Personal Title First name Middle Initial last name, Company position, Company Name. To do a complete concatenation is easy but it leaves spaces and commas when there is no data. How do I omit the fields without data and the spaces? Link to comment Share on other sites More sharing options...
-Queue- Posted January 28, 2005 Share Posted January 28, 2005 Which version of FM are you using? Link to comment Share on other sites More sharing options...
SurferNate Posted January 28, 2005 Share Posted January 28, 2005 Set each part of the concatenation with eith an IF or a CASE statement. Since I only have experience with FM7 I'm not sure what would be appropriate for other cases. Basically you want a calc that says: Concat = IF(not IsEmpty(Field1) ; Field1 & ", " ; "") & IF(not IsEmpty(Field2) ; Field2 & " " ; "") etc..... The syntax I used for the above calc is not the best but the idea should be clear. Link to comment Share on other sites More sharing options...
bikergeek Posted January 28, 2005 Share Posted January 28, 2005 You'll want to use the Substitute function to get rid of the commas and Trim to get rid of the trailing spaces. The whole calc would be like this, allowing for source fields to have spaces in them but not the data you want: Substitute( IF(Trim(Personal title)≠""; Trim(Personal title)&" ";"")& IF(Trim(First name)≠""; Trim(First name)&" ";"")& IF(Trim(middle initial)≠""; Trim(middle initial)&" ";"")& IF(Trim(last name)≠""; Trim(last name)&" ";"")& IF(Trim(Company position)≠""; Trim(Company position)&" ";"")& IF(Trim(Company name)≠""; Trim(Company name)&" ";"") ;",";"") Link to comment Share on other sites More sharing options...
bikergeek Posted January 28, 2005 Share Posted January 28, 2005 Substitute the "not equal to" sign for "≠" in the above reply. Link to comment Share on other sites More sharing options...
agedpom Posted January 28, 2005 Author Share Posted January 28, 2005 I am using v7.3 Link to comment Share on other sites More sharing options...
agedpom Posted January 28, 2005 Author Share Posted January 28, 2005 I think I have set you off on the wrong road here. It is not the data itself which needs trimming, it is OK, but when I concatenate the data (which will rarely have data in all the fields) I don't know how to tell the calculation to ignore both the data and space in the calculation. eg a straight concatenation would be Personal title&" "&first name&" "&middle initial&" "&last name&", "&company title&" , "&company name. If there is no data in middle initial, I want the calculation to ignore both "middle initial" and "&" "&" Link to comment Share on other sites More sharing options...
agedpom Posted January 28, 2005 Author Share Posted January 28, 2005 Yes your suggestion works well. I was just a matter of moving the closing brackets one place to the left. Thanks Link to comment Share on other sites More sharing options...
-Queue- Posted January 28, 2005 Share Posted January 28, 2005 This one is a bit cleaner. Substitute( TrimAll( Personal Title & " " & First Name & " " & Middle Initial & " " & Last Name & Case( not IsEmpty(Company Position); ", " ) & Company Position & Case( not IsEmpty(Company Name); ", " ) & Company Name; 0; 0 ); " ,"; "," ) Link to comment Share on other sites More sharing options...
By Soliant Consulting,
By Soliant Consulting,
By Soliant Consulting,
Recommended Posts
Archived
This topic is now archived and is closed to further replies.