5 Replies Latest reply: May 15, 2014 9:59 AM by clemente _ RSS

    Multiple line items report

    clemente _

      HI I have a question about a report like the following

       

      Clerk 12345                         

      Inv     6788

      Date          7/24/2003

      Oil          23.25

      Subtotal     23.25

      Tax           1.41

      Total          24.86

      Clerk 12345                         

      Inv     6789

      Date          7/24/2003

      Vinegar     20.00

      Salt          10.00

      Subtotal     30.00

      Tax           1.80

      Total          31.80

       

      I want to export it as a DBF for manipulation in a customized audit program.

       

      When I get the database it looks like this

      Fld1     fld2     fld3            fld4    fld5

      12345     6788     07/24/2003      Oil     23.25

      12345     6788     07/24/2004     Subt     23.25

      12345     6788     07/24/2005     Tax      1.41

      12345     6788     07/24/2006     Total     24.86

      12345     6789     07/24/2007     vinegar     20.00

      12345     6789     07/24/2008     salt     10.00

      12345     6789     07/24/2009     Subt     30.00

      12345     6789     07/24/2010     Tax      1.80

      12345     6789     07/24/2011     Total     31.80

       

      If I create a summary, with a key field across, I get up 36 fields across or more depending on which key field I select across. This is fine for one or two record but if I have thousands or millions the database program or the report program cannot handle it.

       

      I want a final report/database to show one instance of the clerk, date, invoice, subt, tax and total. Then on the same line/record show each individual item followed by its cost. The order is not important as long as each complete invoice is in one single line.

      ex:

      fl1   fld2fld3 fld4 fld5 fld6 fld7 fld8  fld9 fld9

      clerk inv date sub  tax total oil  $$.cc salt $$.c

       

      and so on. Can this be done with Monarch 5.2?. We probably won't be upgrading until sometimes next  year. Thanks

        :cool:

        • Multiple line items report
          Grant Perkins

          Hi,

           

          This looks like one where the detail of the report layout might be useful - perhaps there is something that does not appear to be important but could help us separate the data as required.

           

          The format of sample looks interesting. Does the Clerk number really repeat at the start of each section? That would be unusual I would think but of course it all depends upon where the report originates in the programmer's mind.   

           

          Also I am not sure how your wished for solution really differs from your 36 fields across summary solution. If you have, say, 15 products each with a price you will need 30 fields across plus the basic record header and subtotals. You could probably concatenate all of them into a single field, for example, but the data size for the field might then be a problem anyway.

           

          What exactly are the constraints that you must avoid for the solution to work?

           

          I would be happy to look at a sample of the real original file if you are able to release it. Send me a Private Message and I will respond with my email address.

           

          Best regards,

           

          Grant

           

          Originally posted by clemente:

          HI I have a question about a report like the following

           

          Clerk 12345                         

          Inv     6788

          Date          7/24/2003

          Oil          23.25

          Subtotal     23.25

          Tax           1.41

          Total          24.86

          Clerk 12345                         

          Inv     6789

          Date          7/24/2003

          Vinegar     20.00

          Salt          10.00

          Subtotal     30.00

          Tax           1.80

          Total          31.80

           

          I want to export it as a DBF for manipulation in a customized audit program.

           

          When I get the database it looks like this

          Fld1     fld2     fld3            fld4    fld5

          12345     6788     07/24/2003      Oil     23.25

          12345     6788     07/24/2004     Subt     23.25

          12345     6788     07/24/2005     Tax      1.41

          12345     6788     07/24/2006     Total     24.86

          12345     6789     07/24/2007     vinegar     20.00

          12345     6789     07/24/2008     salt     10.00

          12345     6789     07/24/2009     Subt     30.00

          12345     6789     07/24/2010     Tax      1.80

          12345     6789     07/24/2011     Total     31.80

           

          If I create a summary, with a key field across, I get up 36 fields across or more depending on which key field I select across. This is fine for one or two record but if I have thousands or millions the database program or the report program cannot handle it.

           

          I want a final report/database to show one instance of the clerk, date, invoice, subt, tax and total. Then on the same line/record show each individual item followed by its cost. The order is not important as long as each complete invoice is in one single line.

          ex:

          fl1   fld2fld3 fld4 fld5 fld6 fld7 fld8  fld9 fld9

          clerk inv date sub  tax total oil  $$.cc salt $$.c

           

          and so on. Can this be done with Monarch 5.2?. We probably won't be upgrading until sometimes next  year. Thanks

             :cool:  /b[/quote]

          • Multiple line items report
            Mike Urbonas

            This will get you most of the way:

             

            1. In Report View, define a Footer trap, highlighting an instance of Subtotal, Tax and Total (3 rows).  You can trap based on the presence of the word Subtotal in the first line.  Define fields for Subtotal, Tax and Total.

            2. Create a Page Header (or Append) trap, highlighting an instance of Clerk and Inv (2 rows).  Define fields for Clerk and Inv.

            3. Now create a Detail trap, highlighting ONLY a Date line.  You can trap based on the presence of the word Date.  Define a field containing the word "Date" AND the date itself (ie, your field will contain "Date 7/24/2003"). Call this field: DATE AND ITEM(S).

            4. Double-Click or Right-Click on this field to open the Field Properties wiondow.  Click on the Advanced tab.  You want to End Field On "Minimum Action only"-when another data field is encountered. (For V7 you click the "None of the Above" End Field On option.

             

            This is what your Monarch Table will look like:

            [font="courier"]   CLERK      INV SUBTOTAL     TAX    TOTAL    DATE AND ITEM(S)                            

               12345     6788    23.25    1.41    24.86    Date 7/24/2003 Oil 23.25                    

               12345     6789    30.00    1.80    31.80    Date 7/24/2003 Vinegar 20.00 Salt 10.00      /font[/quote]That gets you 85% there...      smile.gif[/img]    

             

            Now you can define some Calculated FIelds to (1) Pull the Date into its own column/field and (2) Use, most likely, the LSPLIT command to break apart the rest of this field into individual columns/fields.

             

            Be sure to look at the upcoming Monarch Report newsletter, coming out later this week, which also utilizes the Advanced Field Properties to include in a desired field data that spans more than one row of the report.

             

             

            Let me know if this helps,

            Mike

             

            [size="1"][ August 19, 2003, 11:49 AM: Message edited by: Mike Urbonas ][/size]

            • Multiple line items report
              clemente _

              Thanks it looks like I can use the "minimun action only" option .

              There are only a couple of things.

              Sometimes there is no Tax line. The problem is that Monarch shifts this field down depending on when it encounters this line.

              Then the entire database is wrong since each invoice amount is shifted. invoices cannot be verified. Is there a procedure for making sure that each

              • Multiple line items report
                Grant Perkins

                Clemente,

                 

                Did you try the models I sent by email?

                 

                Did you find a problem, either with a model or with the process I suggested?

                 

                Grant

                 

                 

                Originally posted by clemente:

                Thanks it looks like I can use the "minimun action only" option .

                There are only a couple of things.

                Sometimes there is no Tax line. The problem is that Monarch shifts this field down depending on when it encounters this line.

                Then the entire database is wrong since each invoice amount is shifted. invoices cannot be verified. Is there a procedure for making sure that each /b[/quote]

                • Multiple line items report
                  clemente _

                  Grant your models were beautiful and I was able to complete the project. BTW did I say thanks. If I did not please accept my delayed "Thanks". I was just trying someone else's reply but when I try, the numbers do not line up correctly. I also noticed that if you try to capture both the subtotal, the tax and the invoice number then the numbers do not lineup correctly. I am not sure if it is something I am doing or if it is a Monarch thing.

                   

                  Again Thanks   smile.gif[/img]