3 Replies Latest reply: May 15, 2014 9:54 AM by Grant Perkins RSS

    multiple n-lines to 1 single line wanted

      Hello,

       

      i`m using V6 and working on this report:

       

      [font="courier"]    15  CUSTOMERNAME                ADDRESS                         TEL. 

         V    D-ZIP  CITY                 STREET                                                   FAX                                                                               

      TYP     ANZAHL  DATUM     KOMMENTAR                       DRUCK                                                             

           -


                                                      

                 SO05        20  03.06.05  PER OHL KW23 / BK                 J                                                               

                 KIDS05      20  03.06.05  PER OHL KW23 / BK                 J                                                                               

                                                                                                                                                                                                                                                                            •                                                                                 

      30  CUSTOMERNAME                 ADRESS                               TEL.                                                

         V    D-ZIP  CITY                 STREET                                                FAX                                                                               

      TYP     ANZAHL  DATUM     KOMMENTAR                       DRUCK                                                             

           -


                                                      

                 SO05        20  12.04.05  PER OHL KW16/SCHE                 J                                                               

                 SUN05       20  12.04.05  PER OHL KW16/SCHE                 J                                                               

                 KIDS05      20  12.04.05  PER OHL KW16/SCHE                 J                                                               

                 KULT05      20  12.04.05  PER OHLKW16/SCHE                  J                                                                               

      /font[/quote]What i want to reach is something like this in one line:

       

      15,CUSTOMERNAME,ADRESS,D-ZIP,CITY,STREET,SO05,20,03.06.05,KIDS05,20,03.06.05

      30,CUSTOMERNAME,ADRESS,D-ZIP,CITY,STREET,SO05,20,12.04.05,SUN05,20,12.04.05,KIDS05,20,12.04.05,KULT05,20,12.04.05

       

      To trap the fields is not the problem, i`ve no clue to get it in one line by summary.

       

      Thank you in advance.

       

      Regards

      ROlf

        • multiple n-lines to 1 single line wanted
          Grant Perkins

          Hi,

           

          2 questions first.

           

          Do you have V6 Pro?

           

          Do you need the commas between each of the TYP,   ANZAHL and DATUM  fields? ( I assume you do but wanted to check.)

           

          Either way I think you need a 2 stage process. If you have Monarch Pro we can consider the external Lookup option. If Standard then we need to re-format the report to a format suitable for use with a model that will give the potential for a csv file output from either the table or the summary option.

           

           

          Grant

          • multiple n-lines to 1 single line wanted

            Hi Grant,

             

            i`m using V6 Standard.

             

            And i need it exported as excel sheet. The commas only should show the fields.

             

            Best regards

            ROlf

            • multiple n-lines to 1 single line wanted
              Grant Perkins

              Originally posted by starpower:

              Hi Grant,

               

              i`m using V6 Standard.

               

              And i need it exported as excel sheet. The commas only should show the fields.

               

              Best regards

              ROlf /b[/quote]OK, no external lookup then ...

               

              So here is the outline of my suggestion.There are a number of ways to implement the detail and you can work with those you feel most comfortable using if you prefer.

               

               

              Create a Detail template for the TYP, ANZAHL and DATUM text area. You may as well select these all in one field for now. You can separate them if you wish, it won't make much difference. Make this a Multi Line field of a size large enough to hold the biggest selection of rows you are likely to see on any report.

               

              Create an Append template for the customer details.

               

              Make sure you have everything you need on the table.

               

              Now you have a choice.

               

              You could export to Excel with the very large field (provided it will not exceed Excel maximum cell capacity). Then use the Excel utility to convert text to columns to give you what you want.

               

              Or you could use Monarch to SPLIT the large field and create calculated fields for each discrete string in the field. In this case the split wouold need to use the "Space" as the splitting character and there can be many spaces so the process will need frequent use of TRIM,LTRIM or RTRIM to get the correct results. Once the first formula is set up the rest shouls be easy though.

               

              You would need some idea of the maximum number of fields that are likely to be created.

               

              Hide the original multiline field and then export what is left and the job should be complete.

               

              An alternative would be convert the multi-line field from its fixed format to a csv field, then export the entire table, other than the temporary work fields, as a csv file. Excel could then import the csv file directly.

               

              Either way I think the important thing is to have some control or knowledge about the number of detail lines that might appear in the report. If there could be, say, 200 lines (600 fields) a completely different approach will be needed.

               

              The work required to reformat the multi-line field with commas instead of spaces would be MUCH simpler using Version 8 by the way. (For those lucky enough to have V8 to help them.)

               

               

              I hope this helps.

               

              Grant