6 Replies Latest reply: May 15, 2014 10:01 AM by Grant Perkins RSS

    Newbie who needs some assistance trapping data

    akouri _

      I am very new to Monarch and needed some assistance understanding how to trap data.  I understand the basic concepts, but thats about it.

       

      my sample data: (From Syspro)

                                                 Order Line    Order    Ship      <---  Stocking units  -


      > To ship    To B/O                              

      </boldoff></hdgline>ription         Br Wh number  no.    date     date        Ship qty     B/o qty U/M  value      value Day Stat                                                                               

      Customer : 0000334 - SAMPLES                                                                               

      -


                                                                                       

      1033111         Motrin PM .05L Liquid Geltabs                                                                               

      TX TX 011232    1    06/06/05 06/09/05       1           0     CA    0.00       0.00   4 Shipd                     

      25305B          Acetaminophen Solid Tablets                                                                               

      TX EX 023223    1    06/09/05 06/09/05       3           0     CA    0.00       0.00   1 Shipd                     

      23437B          Vitimins 15 count red                                                                               

      TX EX 012323    2    06/09/05 06/09/05       1           0     CA    0.00       0.00   1 Shipd                     

      234332A         Vitimin 34 Count orange                                                                               

      TX EX 021232    4    06/09/05 06/09/05       1           0     CA    0.00       0.00   1 Shipd                     

      22132A          Acetaminophen Liquid Gel                                                                               

      TX EX 023232    3    06/09/05 06/09/05       1           0     CA    0.00       0.00   1 Shipd                     

      23232A          Acetaminophen Non Solid                                                                               

      TX EX 032323    5    06/09/05 06/09/05       3           0     CA    0.00       0.00   1 Shipd                     

      8888309         Vitimin C           TX ** 032322    1    06/03/05 06/03/05       1           0     CA    0.00       0.00   7 Shipd                     

      7777777         Vitimin B12         TX ** 077774    1    05/05/05 05/15/05       1           0     CA    0.00       0.00  33 Shipd                     

                                          TX ** 045454    1    05/28/05 06/11/05      64           0     CA    0.00       0.00  13 Shipd                     

      1234567         MISC                TX ** 034545    1    06/03/05 06/13/05       1           0     CA    0.00       0.00   7 Shipd                     

      7777777         Free Samples        TX ** 045455    1    06/04/05 06/13/05       1           0     CA    0.00       0.00   6 Shipd                     

      7777777         Misc                TX ** 045454    3    06/05/05 06/05/05       1           0     CA    0.00       0.00   5 Shipd                     

                                          TX ** 023434    3    06/05/05 06/13/05       1           0     CA    0.00

       

      -


         

      /CODE

      1.  Sometimes the product name is too long and splits the entry to another line.

       

      2.  (See entry 1234567)  Sometimes more then one item is entered.

       

      Can anyone help or point me in the correct direction.

       

      Thanks

        • Newbie who needs some assistance trapping data
          Grant Perkins

          Hi akouri and welcome to the forum.

           

          Looks like you have a bad case of 'Newbie Syndrome' in which you have been presented with a task that is by no means straightforward. This seems to happen quite a lot! :eek:

           

          There will surely be a solution but it's not obvious from a first glance.

           

          Can you tell us what the first column is (the heading seems to have gone missing) and how that needs to be used.

           

          Also whether the 7777777 values are part of the (eg) 1234567 code above, taking the case of the lower lines.

           

          The reports sort of suggests that any line with a line number should be interpreted as detail but I'm not sure how the lines without Descriptions and/or first column values need to be dealt with in relation to the detail. Do you assume the value form the most recently populated line above?

           

          The split lines are also interesting ...

           

           

           

           

          Grant

            • Newbie who needs some assistance trapping data
              akouri _

              The first column appears to be a customer number.  (No clue why they are not in a uniform format)

               

              From what is currently being explained to me..... The below line:

               

              7777777         Vitimin B12         TX ** 077774    1    05/05/05 05/15/05       1           0     CA    0.00       0.00  33 Shipd                     

                                                  TX ** 045454    1    05/28/05 06/11/05      64           0     CA    0.00       0.00  13 Shipd                      /CODE

               

              Should be interpreted as followed:

               

              7777777         Vitimin B12         TX ** 077774    1    05/05/05 05/15/05       1           0     CA    0.00       0.00  33 Shipd                     

              7777777         Vitimin B12         TX ** 045454    1    05/28/05 06/11/05      64           0     CA    0.00       0.00  13 Shipd                      /CODE

               

              Thanks for your assistance

                • Newbie who needs some assistance trapping data
                  Grant Perkins

                  Ah!

                   

                  Ok, from your initial description I wondered if the 7777777 lines were some sort of special re-encoding of the 1234567 type lines preceding them.

                   

                  Thankfully it seems not.

                   

                  On the other hand since the sample seems to suggest that the whole section is pre-selected by Customer Number I'm note entirely sure that the advice you have been given is entirely correct - unless they are sub-account numbers within a master customer.

                   

                  Do you want to check that while I put some words together?

                   

                   

                  Grant

                    • Newbie who needs some assistance trapping data
                      akouri _

                      i was incorrect...  It represents Stock code not customer number.

                       

                      Version  : 6.0.015                                         Order Backlog by Customer Report

                       

                                                                 Order Line    Order    Ship      <---  Stocking units  -


                      > To ship    To B/O

                      Stock code      Description         Br Wh number  no.    date     date        Ship qty     B/o qty U/M  value      value Day Stat

                      /CODE

                        • Newbie who needs some assistance trapping data
                          Grant Perkins

                          OK, I decided that is what it might be just after I posted my question.

                           

                          This is looking a little easier I think. However I don't have a working V5 system available to me at this moment so I'm playing from memory.

                           

                          Give me about an hour or so.

                           

                          Meantime build a DETAIL template that ignores the first two columns and traps on the Quantity column containing a numeric value. If you need a second trap go for an always populated value column decimal point.

                           

                          Then, using one of the LONGER descriptions where a second line is created, create an APPEND Template and  trap for a character in the first column ans a character in the first column of the DESCRIPTION field. Make the Desc. field long enough to  capture the longest possible description.

                           

                          Some lines will contain non-description  - I will tell you how to fix those a little later.

                           

                          Add an APPEND template for the CUSTOMER number and name.

                           

                          More later.

                           

                           

                          Grant

                            • Newbie who needs some assistance trapping data
                              Grant Perkins

                              The concepts above will necessitate some tidying of the Description field form the Append for records that have NOT been split into two lines.

                               

                              To consider the best way to achieve that we need to establish what the maximum length of the Description field needs to be to capture the longest description you may find in the report.

                               

                              If you can let me know what that value is I will try to work out what the simplest approach to tidying the description field (when necessary) would be.

                               

                              Grant