11 Replies Latest reply on Jun 7, 2012 1:17 PM by karendweaver

    merge fields with no data do not slide up

    rbogdanoff

      I am using merge fields for names and addresses in a layout. I have two names per record, not all records contain two names. When the record contains only one name, the address prints out with a blank line were the second name should be, rather than sliding up:

       

      <<Name_one>>

      <<Name_two>>

      <<address>>

       

      The above appears like this when there is no data in Name_two:

       

      John Smith

       

      123 Main Street

       

      Tried using the Sliding Up tool but with no success. How to solve this seemingly simple problem?

        • 1. Re: merge fields with no data do not slide up
          karendweaver

          Hi rbogdanoff,

           

          The reason the merge fiels are not sliding up is that you likely have returns in the text box - and the returns, while invisible, prevent the sliding.

           

          I usually create an "address_label" calculation field - that also really helps with placing the fields on the layout especially for mailing labels where the space is small and getting it right is difficult.  And it has the added advantage of removing spaces while in Browse mode as well, while sliding only works in Preview mode.

           

          In your case, the List function will work.  So the calc would be

           

          List ( Name_one; Name_two; address ).

           

          The list function returns only the fields that have data.  So with a second name, the result would be

           

          John Smith

          Mary Smith

          123 Any Street

           

          But without a second name, the result would be

           

          John Smith

          123 Any Street

           

          If you have FileMaker Pro Advanced, there are several custom functions at Brian Dunnings web site:  www.briandunning.com/customfunctions for address labels that include the city, state, zip, country, etc. etc.  That way you have ONE merge field and all the formatting is done by the custom function.

           

          If you don't have FMPA, then you'll have to build a calc.

           

          Hope that helps.

           

          Karen

          • 2. Re: merge fields with no data do not slide up
            rbogdanoff

            Thanks that did the trick :-)

            • 3. Re: merge fields with no data do not slide up
              comment

              karendweaver wrote:

               

              you likely have returns in the text box - and the returns, while invisible, prevent the sliding.

               

              I am not at all convinced that is so.

              • 4. Re: merge fields with no data do not slide up
                karendweaver

                Okay, Michael, it could be something else, for sure, such as a space in the "empty" field.  But what do you think it might be?  I have seen this specific behavior all the way back to FM5.  Removing returns usually fixes the sliding if it is returns causing the problem.  I am interested in what else you might do to troubleshoot if there was a different issue.

                 

                 

                 

                Karen

                • 5. Re: merge fields with no data do not slide up
                  comment

                  karendweaver wrote:

                   

                  Okay, Michael, it could be something else, for sure, such as a space in the "empty" field. 

                   

                  It must be something else, because returns alone won't do it. Probably a space within the text object itself, if I had to take a guess.

                  • 6. Re: merge fields with no data do not slide up
                    rbogdanoff

                    I do have spaces in the text box between the merge fields:

                     

                    <<firstName>> <<lastName>>

                    <<firstName2>> <<lastName2>>

                    <<address>>

                    <<city>>, <<state>> <<zip>>

                     

                    So when I don't have a second name I get:

                     

                    John Smith

                     

                    123 Main Street

                    Anytown, CA 12345

                     

                    So that space is most likely what is causing that blank line, but you don't see it because it's a non-visible character.

                    • 7. Re: merge fields with no data do not slide up
                      karendweaver

                      So in that case, you'll need a more complicated calc for an address label -

                       

                      Let ([

                      name1 = Case ( isEmpty (firstName1) or isEmpty (lastName1) ; "" ; firstName1 & " " & lastName1;

                      name2 = Case ( isEmpty (firstName2) or isEmpty (lastName2) ; "" ; firstName2 & " " & lastName2;

                      citystatezip = city & ", " & state & " " & zip ] ;  // note - this assumes you have another test for blanks in these fields)

                       

                      List ( name1 ; name2; address; citystatezip )

                       

                      ])

                       

                      Or a custom function....

                       

                      Karen

                      • 8. Re: merge fields with no data do not slide up
                        karendweaver

                        I stand corrected!  ;-)  It has been a long time since I have used merge fields for an address label!

                         

                        Thanks Michael, for catching that one.

                         

                        Karen

                        • 9. Re: merge fields with no data do not slide up
                          comment

                          How about =

                           

                          List (

                          Trim ( FirstName & " " & LastName ) ;

                          Trim ( FirstName2 & " " & LastName2 ) ;

                          Address ;

                          City & ", " & State & " " & Zip

                          )

                          • 10. Re: merge fields with no data do not slide up
                            Stephen Huston

                            Yes, the space between FN & LN is the culprit. I have sometimes implemented a Full_Address field which runs all the tests for empty fields and adds or omits spaces, and even the comma punctuation, returning only the address as it should appear, then use that field on the layout. Give one a lot more control.

                             

                            As an aside, the USPS prefers addresses on mail to include NO puctuation between City and State for cleaner info in its sorting machines. In fact, they prefer ALL punctuation be omitted unless it is essential for separating info into meaningful blocks on the Street/Box line as well. So PO Box is better than P.O. Box.

                            • 11. Re: merge fields with no data do not slide up
                              karendweaver

                              Yes, much simpler and cleaner with the same result.

                               

                              I just tend to use the Let because usually I do more error trapping than this - sometimes I add formatted messages to labels with missing data (such as "CITY MISSING" in red, or other warnings,) or I put in a flag so the user can find all the records they want to print address labels for, but get a warning message if any of the labels are missing critical data, such as "5 of these 5000 labels are missing the city, continue or cancel?" before printing.  It's easier for me to start with a Let statement so when I add those elements, it is easier to read from the get go.  But the Trim function is definitely a better solution (assuming there are no carriage returns or other invisibles in the field)

                               

                              I also do not use any punctuation - I just included it because rbogdanoff had it in his example.  But I agree - it's usually a bad idea on labels, although it is okay to include it if you are formatting a letter or report that displays the address.

                               

                              Karen