3 Replies Latest reply: May 15, 2014 10:11 AM by elginreigner _ RSS

    Reformatting Addresses

    Jim Weeks

      Hello all.

      I suspect this is simple but my head is not wrapping around it.....all too frequent an occurance but I digress.  What I am looking for is essentially the reverse of of the Address Block Template function.  I have the various components of an address (Name; Address 1; Address 2; CSZ) in one row, seperate columns, in a Monarch Table.  What I would like to do is re-order them so they would appear vertically (seperate rows but one column) in a Monarch Summary:

       

      Name

      Address 1

      Address 2

      CSZ

       

      I realize I may have to export and re-import but its still not clicking for me.  Can anyone help?

       

      Thanks in advance.

      Jim Weeks

        • Reformatting Addresses
          Olly Bond

          Hello Jim,

           

          There's two approaches to consider here. Assuming the names are all distinct, then you could define the different lines of the address as key fields in the summary, and hide the key fields (while showing a header row containing the value) which has the effect of collapsing columns.

           

          A more elegant solution was suggested by Kruncher, which glues the lines of Address that you want back into a Character field as:

           

          Address = Line1 + chr(10) + chr(13) + Line 2 + etc[/CODE].

           

          You can then make a summary with this character field as the key.

           

          Hope this helps,

           

          Olly

            • Reformatting Addresses
              Jim Weeks

              Thanks for the suggestions, Olly.  I went right to the elegant one and I'm afraid it didn't work correctly unless I'm missing something.  I created a calculated field with the various address fields tied togther: char(13)chr(10)]char(13)chr(10) etc.  (I think your original suggestion had the 13/10 reversed).  In the calcuated field in the table, I get one line with all of the elements whcih I expected. But that is also what I get in the summary when I list the calculated field as a Key Field.  (In both cases, the "Chr" elements of the calculation do NOT appear.)  I then tried to output the summary to a fixed text file and read it back in but again all I see is one line per customer with each of the individual adress elements.  However, if I use the TextLine() function to extract a specific line, the result is correct (TextLine(,3) returns the second address line after name and address 1) so at least at that level, the char(13)chr(10) is being recognized.  But for some reason its not positioniing the various address pieces "vertically" in either the summary or in a .txt output.  Am I doing something incorrectly or are there other suggestions you can think of?  Any help is always GREATLY appreciated!

               

              Thanks!

              Jim Weeks