5 Replies Latest reply: May 15, 2014 9:59 AM by Grant Perkins RSS

    Model Creation

    Miles Baidack

      We have an invoice report that contains line item detail for all items and serial numbers only for line items that have serial numbers. The serial number info is offset under the line item it belongs to and the number of serial numbers and lines of serial numbers varies by the number of serial number items sold.

       

      We can design the model we need to report normal line items (non-serial number) but including serial numbers doesn't return the desired results.

       

      In simple terms we would like to see: Invoice No., Customer No., Item No., Item Quantity and Serial Number when a serial numbered item is sold. Each Item No./Serial No. item can/should be its own record.

       

      I would post a report sample here but don't know how. How do I do this?

       

      Thanks & Aloha,

      Miles

        • Model Creation
          Grant Perkins

          Hi Miles,

           

          If you can cut and paste a sample of your report using the CODE button from the "Instant UBB Code" just under the text entry window it should come out OK. The button gives 'start' and 'end' identifiers and you simply paste your sample in between them. It should look something like this:

           

          [font="courier"]

          Sample report

           

           

          /font[/quote]It might also be useful to know which version of Monarch you have so that we know which options we can consider (or not as the case may be!).

           

          Grant

           

           

          Originally posted by Miles Baidack:

          I would post a report sample here but don't know how. How do I do this?

           

          Thanks & Aloha,

          Miles /b[/quote]

           

          [size="1"][ February 03, 2004, 08:58 PM: Message edited by: Grant Perkins ][/size]

          • Model Creation
            Miles Baidack

            Grant: Thanks for the instructions. Here goes...

             

            [font="courier"]----


            Tick-# Printed  Usr Drw  Cust-#       Taxcd    Sale-amt    Cost-amt  Disc-amt    Txbl-amt Pmt     Amount

                   Posted   Slp Loc  Cust-name              Pft-ctr       G.P.%  Misc-amt     Tax-amt Type  Rcvd/Refnd Validation/Messages

                   P.O. #            Ship-to                Order-#

            -


             

            318194  2/02/04 PK  999  2700         1           57.40       41.66       .00       57.40 A/R        57.69 Release: Order #  9053

                            41  1    F. K. S. RENTALS, INC    00200       27.42       .00         .29

                   136173            F. K. S. RENTALS, INC     9053

             

                Item-#          Loc     Quantity Unit        Price   Description                                                          Reason

                SHI20070-81012  1              1 EACH        57.40   carburetor TK

             

            318195  2/02/04 JS  999  1600         2           41.30       24.89       .00       41.30 A/R        43.02 Release: Order #  9078

                            02  1    DEPT.OF ENTERPRISE SE    00100       39.73       .00        1.72

                   BV1082            WEST LOCH GOLF COURSE     9078

             

                Item-#          Loc     Quantity Unit        Price   Description                                                          Reason

                PAA203-40       1             10 EACH         4.13   DRAIN PLUG W GASKET

             

            318196  2/02/04 PK  999  97220        2          104.14       49.08       .00      104.14 A/R       108.48 Release: Order #  9079

                            60  1    K.N. LAWN SERVICE        00100       52.87       .00        4.34

                                     K.N. LAWN SERVICE         9079

             

                Item-#          Loc     Quantity Unit        Price   Description                                                          Reason

                TOR44-6260      1              2 EACH        52.07   V BELT                    1/2IN X 123IN DBL V

             

            318197  2/02/04 GS  999  10140        1        1,980.00    1,548.32       .00    1,980.00 A/R     1,989.90 Release: Order #  9069

                            40  1    TRANSAMERICA COMMERCI    00200       21.80       .00        9.90

                   ED SR.            M KAWAMURA FARM ENT       9069

             

                Item-#          Loc     Quantity Unit        Price   Description                                                          Reason

                SHI488C         1              6 EACH       330.00 * CHAINSAW 18 IN

                                                                     Ser#:3078295         3078296         3078297         3078298

                                                                          3078309         3078310

                SHI38490-18     1              6 EACH          .00 * "18"" Pro Lite DG"

                SHI33SL0-66X    1              6 EACH          .00 * "Chain Loop -- .325""     .050 Ga."

                SHI99909-35110  1              6 EACH          .00 * Chainsaw Case

                 /font[/quote]Cool, it worked.

             

            Please note Item No. SHI488C on Ticket No. 318197. This is a serial number item and six have been sold on this ticket; each serial number is listed.

             

            I am working with Monarch V7 Professional.

             

            Mahalo for your assistance.

            Miles

            • Model Creation
              Grant Perkins

              Hi Miles,

               

              OK, here we go.

               

              I am assuming you probably report the DESCRIPTION of the product as well so try this:

               

              Set up your detail as you already have them.

               

              Add an APPEND (2 line selection with the first line of the serial number array on the second line of the template window) using exactly the same trap as for the detail line. Paint a field for the full possible width of your serial number line (my guess is 64 chars - 4 x 16 character fields?)

               

              Edit the field properties and make it a character field (or Memo if you are ever likely to have a very large number of serial number which would lead to exceeding the maximum character field size). Go to the Advanced tab and set the 'Start Field On' value to 'Preceeding String <ser#:>[/b] in current line'.

               

              Set the 'End field on' value to something suitable. 'Non blank preceeding string of 2 characters' seems likely but you may need to check that this works for every possibility in a full report.

               

              This should give you an append with a possible multi line field ONLY when the report provides a serial number output.

               

              (If you don't need the product description field I looks like you could put the serial number field in its place in the detail record. You don't need a 2 line template - just paint a field in the correct horizontal position and apply the start and end rules mentioned above. Should work fine.

               

              You will end up with a single field with the array of serial numbers. I was not sure if you wanted to break these out into separate serial number fields. If you need to do that you can use the Substr[/b] function since you seem to have fixed field sizes for each serial number BUT[/b] the multi-line field will have TRIMmed trailing spaces at the end of each line so, if there are more than 4 serial numbers, the 4th serial number and everything after it will usually need a different method of string manipulation to make the extract work.

               

              I figured we could come back to that if you need to look at those options and need some suggestions. Just let us know.

               

              I hope this helps you progress.

               

              Good luck.

               

              Grant

               

              Originally posted by Miles Baidack:

              Grant: Thanks for the instructions. Here goes...

               

              [font="courier"]----


              Tick-# Printed  Usr Drw  Cust-#       Taxcd    Sale-amt    Cost-amt  Disc-amt    Txbl-amt Pmt     Amount

                     Posted   Slp Loc  Cust-name              Pft-ctr       G.P.%  Misc-amt     Tax-amt Type  Rcvd/Refnd Validation/Messages

                     P.O. #            Ship-to                Order-#

              -


               

              318194  2/02/04 PK  999  2700         1           57.40       41.66       .00       57.40 A/R        57.69 Release: Order #  9053

                              41  1    F. K. S. RENTALS, INC    00200       27.42       .00         .29

                     136173            F. K. S. RENTALS, INC     9053

               

                  Item-#          Loc     Quantity Unit        Price   Description                                                          Reason

                  SHI20070-81012  1              1 EACH        57.40   carburetor TK

               

              318195  2/02/04 JS  999  1600         2           41.30       24.89       .00       41.30 A/R        43.02 Release: Order #  9078

                              02  1    DEPT.OF ENTERPRISE SE    00100       39.73       .00        1.72

                     BV1082            WEST LOCH GOLF COURSE     9078

               

                  Item-#          Loc     Quantity Unit        Price   Description                                                          Reason

                  PAA203-40       1             10 EACH         4.13   DRAIN PLUG W GASKET

               

              318196  2/02/04 PK  999  97220        2          104.14       49.08       .00      104.14 A/R       108.48 Release: Order #  9079

                              60  1    K.N. LAWN SERVICE        00100       52.87       .00        4.34

                                       K.N. LAWN SERVICE         9079

               

                  Item-#          Loc     Quantity Unit        Price   Description                                                          Reason

                  TOR44-6260      1              2 EACH        52.07   V BELT                    1/2IN X 123IN DBL V

               

              318197  2/02/04 GS  999  10140        1        1,980.00    1,548.32       .00    1,980.00 A/R     1,989.90 Release: Order #  9069

                              40  1    TRANSAMERICA COMMERCI    00200       21.80       .00        9.90

                     ED SR.            M KAWAMURA FARM ENT       9069

               

                  Item-#          Loc     Quantity Unit        Price   Description                                                          Reason

                  SHI488C         1              6 EACH       330.00 * CHAINSAW 18 IN

                                                                       Ser#:3078295         3078296         3078297         3078298

                                                                            3078309         3078310

                  SHI38490-18     1              6 EACH          .00 * "18"" Pro Lite DG"

                  SHI33SL0-66X    1              6 EACH          .00 * "Chain Loop -- .325""     .050 Ga."

                  SHI99909-35110  1              6 EACH          .00 * Chainsaw Case

                   /font[/quote]Cool, it worked.

               

              Please note Item No. SHI488C on Ticket No. 318197. This is a serial number item and six have been sold on this ticket; each serial number is listed.

               

              I am working with Monarch V7 Professional.

               

              Mahalo for your assistance.

              Miles /b[/quote]

              • Model Creation
                Gareth Horton

                Grant

                 

                If the numbers are fixed length, then you could use strip to remove all the spaces, check the string looks good, then you can substring out easily from there.

                 

                Gareth

                • Model Creation
                  Grant Perkins

                  Hi Gareth,

                   

                  Yep, but I suspect that Miles will have have different length serial numbers on the report, though perhaps consistent length numbers for a single product. However, checking that such an assumption would be consistent for all possible future occurrences might be a little tricky!

                   

                  I think there are a number of "slice and dice" solutions available but I don't know if Miles needs them. Indeed he may be quite familiar with the concepts anyway.

                   

                  A "Substitute" function would be good here - just swap all double space occurrences for single space (or a space string of any length to a single space)and then use lsplit (or rsplit).

                   

                  The other option would be one we discussed way back when I joined the forum related to <CRLF> inclusion in [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000099#000000"]6.01 functionality on multiline fields[/url] where the automatic TRIMming had been changed to include <CRLF>. As I recall I proposed for the retention of no trimming (though understanding the benefits of the trim) whilst others had a problem with the change of function.

                   

                  A field level 'switch' to disable the TRIM would be useful in this case. The serial number source field looks like a 15 (with a space allowed) or 16 (with no space allowance!!!

                   

                  There are ways around this (I think) but the potential for data variability just makes things a little more complex.

                   

                  So Miles - what have you got for us to play with?   :cool:  

                   

                  Grant

                   

                   

                    Originally posted by Gareth Horton:

                  Grant

                   

                  If the numbers are fixed length, then you could use strip to remove all the spaces, check the string looks good, then you can substring out easily from there.

                   

                  Gareth /b[/quote]

                   

                  [size="1"][ May 18, 2006, 05:09 PM: Message edited by: Todd Niemi ][/size]