1 2 Previous Next 17 Replies Latest reply on May 25, 2017 4:09 PM by tinhx7

    Sum calculation with conditions?

    tinhx7

      Screen+Shot+2017-05-07+at+9.15.26+AM.png

       

      Hello guys,

       

      My invoices layout is based on Line Items table.

      The Tips amount is added on the Invoice.

      One invoice can have multiple technicians perform for a client.

      I"m try to add a subtotal of tips for specific week or day range for a specific tech on my Sales Reports.

      Sales Reports are base on Line Items table.

        • 1. Re: Sum calculation with conditions?
          keywords

          It seems to me that a tip should be just another line item. Further, if you need it to be tied to a specific technician you will have to link the line item to the tech.

          • 2. Re: Sum calculation with conditions?
            philmodjunk

            Is that your invoice layout in the screenshot or the sales report?

             

            Assuming that you've shown an invoice and that you sales report really is based on line items, you can add a summary field that computes the total of of your line item cost. If you then add a sub summary layout part when sorted by line item category and put the summary field into that sub summary part, sort by technician, then by category, you can get total tips for each technician if tips is a lot ne item category.

             

            Please note, however, that this would not work for the layout shown your screen shot.

            • 3. Re: Sum calculation with conditions?
              tinhx7

              Tip is not base on any specific tech, but the invoice.

              Tip field is belong in invoice, not Line Items table.

              • 4. Re: Sum calculation with conditions?
                keywords

                In your first post you said: "I"m try to add a subtotal of tips for specific week or day range for a specific tech"

                Now you say: "Tip is not base on any specific tech"

                That ain't gonna work.

                • 5. Re: Sum calculation with conditions?
                  tinhx7

                  My bad, I mis-explained the issue.

                  If I use this SQL code, it adding a grand total of tips: ExecuteSQL("Select Sum (Tips) FROM Invoices";"";"")

                  I only want it sum of tips amount for specific invoices (records) when I perform the Find criteria.

                   

                  Here's the sales reports.

                  Screen Shot 2017-05-21 at 5.45.09 PM.png

                  • 6. Re: Sum calculation with conditions?
                    tinhx7

                    Anybody?

                    • 7. Re: Sum calculation with conditions?
                      erolst

                      Simply create a calculation field that references the Tips field from Invoices, and a summary field to total that calculation field.

                      • 8. Re: Sum calculation with conditions?
                        tinhx7

                        If I add the total Tips using GetSummary(Tips...), then that will give the the grand total amount of Tips.

                        Is that what you meant?

                        • 9. Re: Sum calculation with conditions?
                          tinhx7

                          I just want to add the total Tips of the invoices such as invoice 1, invoice 2, when I do a search criteria in the Sales Report that based on Line Items.

                           

                          Right now invoices are display tips separately.

                          • 10. Re: Sum calculation with conditions?
                            erolst

                            No. If that had been what I meant I would have written it.

                             

                            You want to total the tips of the Invoices of the LineItems of the Found Set. You cannot create a summary field in LineItems that aggregates Invoices::Tips because the field must be in the same table.

                             

                            So you could bring over the Tip into LineItems, though I just realise that my previous suggestion would yield a wrong result because you may have (and sum) the Tip of one Invoice multiple times ...

                             

                            You can try to:

                             

                            • create a List of: summary field for the invoice foreign key

                             

                            • try a calc like:

                             

                            Let (

                              theArray = Substitute ( sListOfInvoiceForeignKey ; Char(13) ; "','" ) ;

                              ExecuteSQL ( "

                                SELECT SUM ( Tips )

                                FROM Invoice

                                WHERE invoicePrimaryKey IN ('" & theArray & "')

                              " ; "" ; ""

                              )

                            )

                             

                            This works if you have keys of type text (eg UUID). If the keys are numerical, delete the misc. single quotes.

                            1 of 1 people found this helpful
                            • 11. Re: Sum calculation with conditions?
                              tinhx7

                              I assuming you want me to create the Summary Invoice FK list, and the calculation fields in the Line Items table, right?

                              Anther thing is that how do I create the list of summary invoice fk?

                               

                              The Invoices ID are numerical.

                              I think the single quote you mentioned is this line: '" & theArray & "'

                              • 12. Re: Sum calculation with conditions?
                                erolst

                                tinhx7 wrote:

                                I assuming you want me to create the Summary Invoice FK list, and the calculation fields in the Line Items table, right?

                                Yes. If the result depends on the found set, then that is the place.

                                 

                                tinhx7 wrote:

                                Anther thing is that how do I create the list of summary invoice fk?

                                Create a new field, type summary, select List of:, and select the foreign key.

                                 

                                tinhx7 wrote:

                                The Invoices ID are numerical.

                                I think the single quote you mentioned is this line: '" & theArray & "'

                                Those *two* quotes, and in the second line change "','" to ","

                                • 13. Re: Sum calculation with conditions?
                                  tinhx7

                                  Thank you.

                                  I will try it tonight.

                                  • 14. Re: Sum calculation with conditions?
                                    tinhx7

                                    My calculation result is '?'.

                                    I don't know what I'm missing.

                                     

                                    S_InvoiceID_FK_List   Summary  = List of InvoiceID_FK

                                     

                                    Sub_Total_Tips            Calculation  Unstored =

                                     

                                    Let (

                                    theArray = Substitute ( S_InvoiceID_FK_List ; Char(13) ; "," ) ;

                                    ExecuteSQL ( "

                                    SELECT SUM( Invoices::Tips )

                                    FROM Invoices

                                    WHERE Invoices::ID IN (" & theArray & ") " ; "" ; ""))

                                    1 2 Previous Next