13 Replies Latest reply on Sep 21, 2012 12:45 PM by comment

    Reporting

    Jason_Farnsworth

      Looking for some advice on creating a report.

       

      I have data in 3 tables

       

      1. Jobs

      2. Invoice

      3. Invoice Line Items

       

      I am trying to set up a report that will print the invoice properly but getting stuck.

       

      I have customer and job information with in jobs

       

      I would like this info in the Header

       

      I have Invoice details

       

      I would like this also on the header

       

      I have the invoice line items

      I would like this line itemed out in a report showing each one listed

       

      I have total information (currently I have this stored in invoice line items)

       

      I would like to total all the line times

       

      What would be the correct report layout?

       

      Header

      Sub-Header Leading

      Body

      Footer

       

      or

       

      Header

      Body

      Sub-header following

      Footer

       

      Or maybe another alternative? Which table would I based the report on? Jobs, Invoice, or Invoice Lineitems? Which would I link back to to sort the Sub-Header?

       

      Any help would be great thanks,

       

      Jason

      Midland, Tx

        • 1. Re: Reporting
          comment

          Assuming one Job to many Invoices.

           

          Print from the LineItems table. Use a sub-summary part (by InvoiceID) to show the invoice and job data. Start a new page at every occurrence of the sub-summary, if you intend to print multiple invoices.

          1 of 1 people found this helpful
          • 2. Re: Reporting
            Jason_Farnsworth

            Michael,

             

            Thank you for helping,

             

            This is a interesting thing about this invoice, I will only have one invoice per job. Would the only difference then be to just not start a new page at every occurence?

             

            It would look like this?

             

            Layout from lineitems table

             

            Header - what ever data

             

            Sub-Summary - Add all customer data,

             

            Body - Add lineitems

             

            Footer - Add what ever

             

            Thanks again,

             

            Jason

            Midland, Tx

            • 3. Re: Reporting
              comment

              Jason_Farnsworth wrote:

               

              I will only have one invoice per job.

               

              Why then is it necessary to have two separate tables for them (assuming each invoice is for one job only)?

               

              ---

              Or, if this assumption is wrong - is each line item associated with a specific job?

               

               

               

              Message was edited by: Michael Horak

              • 4. Re: Reporting
                Jason_Farnsworth

                Hmm.

                 

                Good question I guess I got off track while needing to total the line items to have a grand total.

                 

                I could easily get the look that I want with a

                 

                Header

                Body

                Footer

                 

                But how to address the grand total of all line items and where to put it?

                 

                Jason,

                • 5. Re: Reporting
                  comment

                  You haven't really answered my question. Usually, you'd have a structure of:

                   

                  Customers -< Invoices -< LineItems

                   

                   

                  Then the print layout might look something like:

                   

                  Header (optional)

                   

                  Leading Sub-summary (by InvoiceID):

                  • Customer details

                  • Invoice details (e.g. date, invoice number, etc.)

                  This part should be set to start a new page, so that multiple invoices can be printed at once.

                   

                  Body:

                  • Line item details

                   

                  Trailing Sub-summary (by InvoiceID):

                  • Total of line items (summary field in LineItems, or calc field in Invoices)

                  • Additional invoice details (e.g. tax, shipping, payment terms, etc.)

                   

                  Footer:

                  • Page number

                  • 6. Re: Reporting
                    Jason_Farnsworth

                    Michael,

                     

                    Ok I am clearly missing a key component here. I have taken a few snaps of the database and it's structure. I appear to be making a fundamental mistake somewhere, just not quite sure where at this point.

                     

                    Here is how I have set up the relationships, and the data structure - I have a script that goes out to the Customer database and pulls in the bill to and ship to info. Our customers have multiple locations and multiple billing address. I keep the parent information within the Customer table and when they are creating an invoice I pop up a window that allows them to select which bill to and ship to address this particular job gets. I pull the info over into the Invoice table in this case there is only one invoice per job in the jobs table but each invoice can have multiple line items in it.

                    Relationships.PNG

                    Here is the invoice screen where the data gets populated.

                    Data Point.PNG

                    Here is how I have been trying to set up the print layout to just get it showing the proper data. Once I have the data being presented correctly I will go through and make it look as it should.

                    Layout.PNG

                    I hope I might have given you the answer to your question.

                     

                    If you could explain my where I have broken a rule or why it is not functional It would take me a long way down the road.

                     

                    Best Regards,

                     

                    Jason

                    Midland, TX

                    • 7. Re: Reporting
                      comment

                      Jason_Farnsworth wrote:

                       

                      why it is not functional

                       

                      AFAICT, it should work. Can you explain in what way is it "not functional"?

                       

                       

                       

                      ---

                      BTW, you might want to have a look at the demo posted here:

                      http://fmforums.com/forum/showpost.php?post/309136/

                       

                      Message was edited by: Michael Horak

                      • 8. Re: Reporting
                        Jason_Farnsworth

                        Michael,

                         

                        This is the result of the report

                        Result.PNG

                        I do not have any of the customer data, at the top and only one related line items, is this case there should be two.

                         

                        I thought I had a reasonable understanding of this report until I decided I needed this one.

                         

                        Have I properly organized the data? Are my relationships correctly handled?

                         

                        Thanks, again,

                         

                        Jason

                        Midland, Tx

                        • 9. Re: Reporting
                          comment

                          Are your records sorted by the _kf_INVID field? Also, how many records do you have in the found set?

                           

                          Have you looked at the demo I linked to earlier? If you still can't make it work, why don't you post your file or (preferably) a simplified version.

                          • 10. Re: Reporting
                            Jason_Farnsworth

                            Michael,

                             

                            Yes I have looked at the demo you linked I think I am very simlar in setup, I will take a deeper study of it however to verify.

                             

                             

                            Currently at the moment I have, the layout based on Invoice

                             

                            I have the first sub set sorted by Jobs_Invoice_Lineitems - _kf_INVID field

                             

                            I have 1 record in the found set,

                             

                            The file is active at the moment and I can not post, but will try it is rather large.

                             

                            Jason

                            Midland, Tx

                            • 11. Re: Reporting
                              Jason_Farnsworth

                              Michael,

                               

                              Ok I have studied that sample in depth and have a few questions concerning it.

                               

                              I only see one the printed layout

                               

                              Header - Body - Footer

                               

                              The information contained in both Sub-Summarys are not present on the printed form.

                               

                              I have aligned my to match the same data results as the same and it is preforming in the same matter.

                               

                              Why is the data now shown in the Sub-Summarys?

                               

                              Jason

                              Midland, Tx

                              • 12. Re: Reporting
                                Stephen Huston

                                Be sure you have:

                                1. the correct line items in the found set
                                2. the found set sorted based on the same field as that used for defining the sub-summary layout part
                                3. the layout is being viewed in List View.
                                • 13. Re: Reporting
                                  comment

                                  Jason_Farnsworth wrote:

                                   

                                  The information contained in both Sub-Summarys are not present on the printed form.

                                   

                                  Please note the script called Print One Invoice - call it from the Invoices layout. Sub-summary parts do not show unless records are sorted by the breakfield.