4 Replies Latest reply on Dec 8, 2009 9:04 AM by TSGal

    Format Fields for Address mailing lables

    jamie.hunt

      Title

      Format Fields for Address mailing lables

      Post

      Hey guys, I hope someone can help me out with this.

       

      I have 5 text fields for an address:

       

      Address1

      Address2

      Address3

      AddressCounty

      AddressPostCode

       

      I need a calculation to format each field so that I have the address in one field.

       

      What function can I use to look at each field and if there is not a comma at the end, to display the field with a comma on the end.

      What function can I use so that if the field is empty it wont display the field in the address.

       

      I have already used the Proper function and added carriage returns. 

       

       

      Basically at the moment some look like this:

       

      House Name,

      Street

      Town

      County,

      Postcode

       

       

      House Name

      Street, 

                          <<empty field

      County

      Postcode 

       

       

       

       

      They all need to look like this

       

      House Name,

      Street,

      Town,

      County,

      Postcode

       

      or if empty field is present:

       

      House Name,

      Street,

      County,

      Postcode 

       

       

       

        • 1. Re: Format Fields for Address mailing lables
          philmodjunk
            

          Use merge fields instead of concatentating all the fields in a single calculation.

          For each address field that needs to end in a comma,

           

          Define the following auto-enter calculation:

          If ( Right ( Trim ( Self ) ; 1 ) = "," ; Left ( Trim ( self ) ; Length ( Trim ( self ) ) - 1 ); Trim ( self ) )

          Clear the "Do not replace existing value, if any" check box so that this expression will update the field after any edits done to it.

           

          This expression will remove any commas from the end of the field. (For existing records, you'll have to update them to strip out the commas). I'm recommending you remove the commas from the fields so that they can be consistently added as layout text instead.

           

          In layout mode, use Use the Layout text tool to start a block of layout text and select Insert | merge field... to add each field to your layout. Add the commas and carriage returns as part of the block of layout text. When done, it'll look something like this while in layout mode:

          <<YourTable::Address1>>,

          <<YourTable::Address2>>,

          <<YourTable::Address3>>,

          <<YourTable::AddressCounty>>,

          <<YourTable::AddressPostCode>>

          • 2. Re: Format Fields for Address mailing lables
            jamie.hunt
               Thanks for your help, thats great!
            • 3. Re: Format Fields for Address mailing lables
              shirleycherry
                

              Hi guys

               

              I'm just starting out with Bento - all the address book headings are in US style but they appeared to be locked. How do I go about changing them?  I want to be used to insert more address lines.

               

              Shirley 

               

               

              • 4. Re: Format Fields for Address mailing lables
                TSGal

                shirley cherry:

                 

                Thank you for your post.

                 

                If you click on the pop-up for type of Address ("work", "home", "other", etc.), select the last option "Change Address Format", and select the style you want.

                 

                In the future, since this is a FileMaker Pro forum, please post your questions to the Bento forum at http://forums.filemaker.com

                 

                TSGal

                FileMaker, Inc.