1 2 3 Previous Next 31 Replies Latest reply on May 12, 2017 5:15 AM by tinhx7

    Multiple Cases Statments

    tinhx7

      Hi guys,

       

      I'm try to use the case conditions statements to calculate the sales, but I got stuck.

      S_Regular Price is a summary of Regular Price.

      S_Discount is a summary of Discount.

      S_Custom Price is a summary of Custom Price.

       

      Regular Price is hard coded.

       

      If the cashier enter a Custom Price, then the Regular Price will be negate.

      So the Custom Price add the S_Discount to the Subtotal.

      If not, Subtotal = S_Regular Price - S_Discount.

       

      Here's my current calculation:

      Case(S_Custom Price = ""; S_Regular Price - S_Discount; Custom Price  ≠  ""; Regular Price - Regular Price + S_Custom Price - S_Discount + S_Regular Price)

       

       

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

        • 1. Re: Multiple Cases Statments
          Jason Wood

          I'm lost as to what you're trying to accomplish with that calculation, but I think I know what you need...

           

          You do not need any summary fields.

           

          Add a calculation field to the line items table. Field Name: "Line Price"

           

          If ( Custom Price = "", Regular Price ; Custom Price )

           

          Then, in your parent table, the "Total Discount" is Sum ( Lines::Discount ), and the "SubTotal" field is Sum ( Lines::Line Price )

           

          Edit: for clarification

          1 of 1 people found this helpful
          • 2. Re: Multiple Cases Statments
            tinhx7

            Do you meant I need to create an extra field call 'Line Price'?

            • 3. Re: Multiple Cases Statments
              Jason Wood

              Yes - in the line items table.

              • 4. Re: Multiple Cases Statments
                siplus

                what is the idea behind adding then subtracting the same field in a formula ?

                 

                Here's my current calculation:

                Case(S_Custom Price = ""; S_Regular Price - S_Discount; Custom Price  ≠  ""; Regular Price - Regular Price + S_Custom Price - S_Discount + S_Regular Price)

                • 5. Re: Multiple Cases Statments
                  tinhx7

                  If the cashier enter the custom price, then the regular price for that service will not be used, but use custom price instead.

                  But my calculation statements didn't add the other line services that don't have custom price.

                  • 6. Re: Multiple Cases Statments
                    siplus

                    Case is nice when it makes sense, in your case I see a better alternative in

                     

                    FinalPrice = If (  IsEmpty(S_Custom Price);  S_Regular Price;  S_Custom Price) - S_Discount

                    • 7. Re: Multiple Cases Statments
                      tinhx7

                      I'll try you guys solutions later, and post my feedback.

                       

                      Thank you all.

                      • 8. Re: Multiple Cases Statments
                        Jason Wood

                        siplus, the fields starting with "S_" are summary fields, which I don't think you intended to use.

                         

                        If you switch to the regular fields and put this calc in the line items table, then the difference compared to my solution is that you are deducting the discount at the line level, so that it will be excluded from SubTotal. OP should bear this in mind and be clear about whether he wants SubTotal to be net of discounts, but I think you're probably right about this.

                        • 9. Re: Multiple Cases Statments
                          tinhx7

                          Hello Jason,

                           

                          Your solution is working for me, you're good.

                          Somehow the Sum ( Lines::Line Price ) didn't working for me.

                           

                          I made a slight modification to the Subtotal price is that I have to use the Summary result of Line Price.

                          SubTotal = S_Line Price - S_Discount

                           

                          Another question: Is there a way I can count the unique number of the Technicians on the invoice?

                          • 10. Re: Multiple Cases Statments
                            Jason Wood

                            The summary fields are unnecessary. You could eliminate 2 fields by changing your SubTotal Calc to:

                             

                            SubTotal = Sum ( Table::Line Price ) - Sum ( Table::Discount )

                             

                            (Replace "Table" with the name of the child line items table occurrence)

                             

                             

                            To define a field that shows the count of the unique technicians on the invoice, try this...

                             

                            TechnicianCount =

                            ExecuteSQL ( "SELECT COUNT ( UNIQUE technician ) FROM LineItems WHERE foreignKeyInvoiceNumber = ?" ; "" ; "" ; InvoiceNumber )

                             

                            Replace "technician" with the name of the field containing the technician name or number.

                            Replace "LineItems" with the name of a table occurrence of the line items table

                            Replace "foreignKeyInvoiceNumber" with the name of the field in the line items table that is the invoice number

                            Replace "InvoiceNumber" with the Invoice Number field from the Invoices table (which is where this calculation will be defined)

                            • 11. Re: Multiple Cases Statments
                              tinhx7

                              SubTotal = Sum ( Table::Line Price ) - Sum ( Table::Discount )

                              This line gave a wrong SubTotal result.

                              And I couldn't get the Unique Technician to work, it displaying a "?".

                               

                              Screen Shot 2017-05-07 at 5.56.52 PM.png

                              • 12. Re: Multiple Cases Statments
                                Jason Wood

                                It's interesting to see your graph... am I understanding correctly that "Register" refers to what would commonly be called an "Invoice", and "Invoices" refers to the line items belonging to that invoice? But if that is the case then is not InvoiceIDFK the primary key?? and ID in "Invoices" is the foreign key???.... very confusing! Can you post your original layout but show it in layout mode?

                                 

                                 

                                For technician count, I wrote "UNIQUE" when I should have wrote "DISTINCT". knowing the field names and the above assumptions I believe it should look like this:

                                 

                                TechnicianCount should be defined in the Register (?!?) table and =

                                ExecuteSQL ( "SELECT COUNT ( DISTINCT Technician ) FROM Invoices WHERE ID = ?" ; "" ; "" ; InvoiceIDFK )

                                • 13. Re: Multiple Cases Statments
                                  tinhx7

                                  At the beginning, the Technician was in the Register DB, but if I do that, then every time I add a new technician with new services, it'll override the previous technicians.

                                   

                                  Therefore, I store the tech's name in the Invoice db.

                                  In this layout, everything is belong to Invoices db, except Technicians, and InvoiceNumber fields.

                                  They belonging to Register db.

                                  Screen Shot 2017-05-07 at 6.30.58 PM.png

                                  • 14. Re: Multiple Cases Statments
                                    Jason Wood

                                    This is really bizarre - there are so many problems with this it's hard to know where to start.

                                     

                                    Normally you would have a table called "Invoices" which would contain fields such as invoiceNumber, date, time, subtotal, total discounts, tips, grand total, tendered, change, due, payment type, customer information, etc.

                                     

                                    And you would have a table called "Line Items" that would contain a foreign key (e.g.: invoiceNumber), description, price, discount, quantity, technician, etc.

                                    1 2 3 Previous Next