Combine multiple fields into one, but ignoring blanks

[Bmiller4evr]

I have a series of fields pertaining to physical addresses:

they are Street Prefix, Street Name, Street Suffix, Post Directional

Situs_St_Prefix - ex: North, South, NW, ect or blank

Situs_St - Main, Elm, FM 157, County Road 6, Keller Elliott, SW 5th

Situs_Sufix - Road, Street, DR, Circle

Situs_Dir - N, S, E, W

These need to be combined into a full address, but I can not simply use &" "& because often some of these fields are blank and that generates extra blank spaces next to non-existant fields.

My next thought was to use a series of If statements, to create rules for combining the fields into one, but it does not seem to be working properly, whether I run the following function forwards or backwards, It is still generating extra spaces in my full street address.

If ( IsEmpty ( Situs_St_Prefix ) ; Situs_St&" "&Situs_Sufix&" "&Situs_St_Dir ;

If ( IsEmpty ( Situs_St ) ; Situs_St_Prefix&" "&Situs_Sufix&" "&Situs_St_Dir ;

If ( IsEmpty ( Situs_Sufix ) ; Situs_St_Prefix&" "&Situs_St&" "&Situs_St_Dir ;

If ( IsEmpty ( Situs_St_Dir ) ; Situs_St_Prefix&" "&Situs_St&" "&Situs_Sufix ;

If ( IsEmpty ( Situs_St_Dir and Situs_Sufix ) ; Situs_St_Prefix&" "&Situs_St ;

If ( IsEmpty ( Situs_St_Prefix and Situs_St_Dir ) ; Situs_St&" "&Situs_Sufix ;

If ( IsEmpty ( Situs_St_Prefix and Situs_Sufix ) ; Situs_St&" "&Situs_St_Dir ;

If ( IsEmpty ( Situs_St_Prefix and Situs_Sufix and Situs_St_Dir ) ; Situs_St ;

Situs_St_Prefix&" "&Situs_St&" "&Situs_Sufix&" "&Situs_St_Dir))))))))

I am stumped on this one. Perhaps I need a mechanism for this calculation to end after a suitable value is found, I think this calculation is always running all the way through. Is there an If, then do and stop, else keep going... kind of statement?

[LaRetta]

but I can not simply use &" "& because often some of these fields are blank and that generates extra blank spaces

Wrap your concatenated string with TrimAll ( field1 & " " & field2 ... ect ; 0 ; 0 ) which will remove all but single space between the fields. :-)

[Bmiller4evr]

So,

TrimAll ( Situs_St_Prefix&" "&Situs_St&" "&Situs_Sufix&" "&Situs_St_Dir ; 0 ; 0 ) will do this for me?

[LaRetta]

If any of those fields are empty, you would get double spaces. But because it is wrapped with TrimAll() one of those spaces will be removed because its purpose is to remove spaces. Read up on it in FM Help for all its options. :-)

Calculations evaluate from the inside out (within certain order of operations called PEMDAS). So the concatenation takes place first and then the TrimAll() goes to work on the RESULT. "Please Excuse My Dear Aunt Sally" tells order of eval - parenthesis, exponent, multiply etc

Edited October 3, 2012 by LaRetta

[Bmiller4evr]

Many Thanks :)

[Ocean West]

I would do this:

Substitute ( List ( Trim (Situs_St_Prefix ) ; Trim ( Situs_St ) ; Trim ( Situs_Sufix ) ; Trim ( Situs_St_Dir ) ) ; Char (13) ; Char (32) )

Trim strips fields with only a space

List results in a return separated list omitting blanks



Char (13) is the return or ¶

Char (32) is a space



(yes you can use the ¶ or " " too.)



Substitute converts returns to a space.



I also suggest that you ensure your data entry layouts for these fields prohibit the use of the return by making the RETURN key operate like the TAB key



OR

Substitute ( TrimAll( List ( Situs_St_Prefix ; Situs_St ; Situs_Sufix ; Situs_St_Dir ) ; 0 ; 3 ) ; Char (13) ; Char (32) )

[LaRetta]

Hey Stephen!! :-) I forget about using Char() this way!

As for performing cleanup on a concatenated calculation, I would prefer to handle it on the original field, prohibiting carriage returns if the field should be only one line (as this example seems). I would rather do cleanup once ( and only on the field which is modified ) than to re-clean that full string any time any one of those fields change. It seems that would be more efficient ... but maybe not.

I too stop returns by checking 'return' and 'enter' on moving through field. It also avoids those awkward moments when User hits return thinking they will move on (or execute) like most browsers nowadays only to be left backing it out like a dork. Thanks for the additional approaches! I think calculations are beautiful creatures.