1 Reply Latest reply: May 15, 2014 10:10 AM by Grant Perkins RSS

    Field Breaks for variable length data

    MGradyLBP _

      Is there a way to tell the report to split a field when there is a space.  The data I am trying to import into excel has a field that will put the customer number/vendor number and then the customer name/vendor name.  When it is a customer number the length can vary, therefore the start of the name will vary.  I want the customer number and the name to be two different fields.  Is there a way to handle splitting this properly when setting up the fields?  This is a footer in my report.  Here is an example of the data.

       

      I am open to getting it to only pick up the account because I can add that to my file using vlookup.  However, since the customer number varies i am not sure how to get only the customer number or seperate it from the name.

       

      600* H&H Home Improv        

      190512* Detailed Carpen     

      1000101987 RICH/Cash 0104   

      1000101985 RICH/Cash 0102   

      100000174744* A Plus Exterior

      1000101985 RICH/Cash 0102   

      100000126983 ALLEN DR       

      188551* ZT Home Improve     

      1085! Memmer Jim/CASH        

      117! Custom Construction

       

      [SIZE="1"]01/03/11  111  3980952     1 01/03/11    1  oeet  OE Rcvd on Acct 3980952                      568.99                     10078.08 

                                                        777* Handyman The             6242557-0                                          

      01/03/11  111  3980952     2 01/03/11    1  oeet  OE Entry Jrnl 3980952                       1081.77                     11159.85 

                                                        100000187682 Southern Va. Co  6242565-0                                          

      01/04/11  111  3981589     1 01/04/11    1  oeet  OE Rcvd on Acct 3981589                     2361.34                     13521.19 

                                                        100000177148 Weldon Renovati  6242578-0                                          

      01/04/11  111  3981607     1 01/04/11    1  oeet  OE Entry Jrnl 3981607                         67.00                     13588.19 

                                                        100000140016* A Step Above De 6242579-0                                          

      01/04/11  111  3981607     1 01/04/11    4  oeet  Chg. Due Jrnl 3981607                                       10.43       13577.76 

                                                        100000140016* A Step Above De 6242579-0                                          

      01/04/11  111  3981607     2 01/04/11    1  oeet  OE Entry Jrnl 3981607                        579.10                     14156.86 

                                                        100000153476 ernie stevens    6242584-0                                          

      01/04/11  111  3981607     3 01/04/11    1  oeet  OE Entry Jrnl 3981607                         52.50                     14209.36 

                                                        100000170256 design gutter    6242585-0                                          

      01/04/11  111  3981607     4 01/04/11    1  oeet  OE Entry Jrnl 3981607                        259.70                     14469.06 

                                                        1085! Memmer Jim/CASH         6242586-0                                          

      01/04/11  111  3981607     5 01/04/11    1  oeet  OE Entry Jrnl 3981607                        166.13                     14635.19 

                                                        100000169924 cummins          6242587-0                                          

      01/04/11  111  3981607     6 01/04/11    1  oeet  OE Entry Jrnl 3981607                         24.56                     14659.75 

                                                        1000101985 RICH/Cash 0102     6242590-0                                          

      01/04/11  111  3981607     7 01/04/11    1  oeet  OE Entry Jrnl 3981607                        289.80                     14949.55 

                                                        1000101985 RICH/Cash 0102     6242604-0                                          

      01/04/11  111  3981717     1 01/04/11    1  oeet  OE Entry Jrnl 3981717                         34.95                     14984.50 

                                                        276! Va Alum Prod             6242592-0                                          

      01/04/11  111  3981717     2 01/04/11    1  oeet  OE Entry Jrnl 3981717                         72.16                     15056.66 

                                                        100000187717 RDR & Associate  6242609-0                                          

      01/04/11  111  3981717     3 01/04/11    1  oeet  OE Entry Jrnl 3981717                        448.18                     15504.84 

                                                        100000187621 Northstar Devel  6242461-0                                          

      01/04/11  111  3981717     4 01/04/11    1  oeet  OE Entry Jrnl 3981717                        252.89                     15757.73 

                                                        1313! Melani Bros Inc         6242622-0   /SIZE                                       

      /code

        • Field Breaks for variable length data
          Grant Perkins

          Hi MGrady,

           

          The normal reliable way to approach this is to thinkk of the information line from the report as a single text field when extracting it.

           

          Once you have the extract in the table you can create calculated fields to break it up (Slice and Dice) as you wish.

           

          The simple slice, which would seem to work for you here, would be to use the LSPLIT function to break the data string into two parts using the space at the end of the  customer number as the break point.

           

          I see the customer number also has characters at its right hand side and I assume these may also mean something and perhaps you need to do something with them as well. Monarch has many more functions to help with that too but we can discuss that once you have the initial split to your liking.

           

          Check out the embedded help files for LSPLIT and RSPLIT functions as in initial brief about the functions.

           

           

          Grant