6 Replies Latest reply on Apr 10, 2010 11:51 AM by shellas

    Additional Calculations

    shellas

      Title

      Additional Calculations

      Post

      I created a layout (Commission Calc) with information for salesman commission.  Everything is correctly calculated including the sum of monies received and the commission to be paid out.  However, I also have 2 fields in the Commission Calc layout that allows for inputting of deductions from the salesman (for in case he brings in monies short & for deductions from the credit account if he had borrowed some money).  These are to be subtracted from the total commission to be paid out.

       

      On the printout layout, I have summary fields for the total amount of monies collected and the total amount for commission paid.  These fields are placed on the trailing grand summary part, and everything on the print layout is displayed properly. But where do I place the fields for the deductions from the total commission paid?  And where do I place the calculation field for the Grand total?

       

      Thanks.

        • 1. Re: Additional Calculations
          philmodjunk

          It's hard to say without knowing the current structure of your database and your report. Can you post a more detailed description of both?

          • 2. Re: Additional Calculations
            shellas

            Sorry for the delay response.  Our business was on 4 days Easter break and we're just back to work today.

             

            I have a table with the fields:

             

            1) percentage (value list 1%, 2%, 3%, 5%)

            2) amount (for the user to enter the amount of monies brought in by the salesman that qualifies him for a specifice percentage of commissions)

            3) commission calc (percentage x amount)

            4) subtotal (sum of all commissions to be paid out)

             

            For example, the salesman collects 1700.00 and we may  assign the following:

             

            2% x   500.00  =       10.00

            3% x 1000.00  =       30.00

            5% x   200.00  =       10.00

             

                       subtotal  =       50.00

             

             

            Now, after this part we want to deduct any monies short brought in by the salesman as well as whatever he might have borrowed.   So, if he brought in 1695.00 instead of 1700.00, we want to deduct 5.00 from the commission, and if he had borrowed 10.00 prior to his going out on his run, we also want to deduct this 10.00 from the commission also.  Thus, he slip will also show these deductions leaving him with a payment of 35.00.

             

              So:              subtotal = 50.00

              minus      shortage =    5.00

              minus    deduction =  10.00

             

                                       total =   35.00

             

            On the print layout, I place the fields percentage, amount, and commission in the body, and the subtotal in the trailing grand summary.  But where do I place the fields for the shortage & deduction, and the total?  

             

            I tried placing these fields in the trailing grand summary but the figures for shortage & deduction do not show, thus the total doesn't calculate.

             

             

            • 3. Re: Additional Calculations
              philmodjunk

              Even further clarification would help...

               

              1 ) Do you only have the one table?

               

              2) Does:

               

              2% x   500.00  =       10.00

              3% x 1000.00  =       30.00

              5% x   200.00  =       10.00

               

              Represent 1 record or 3?

               

              3) Is this for a single sale or a total of all sales for a given time period (the week, month, quarter or...?)

               

              4) If it is three records, (as it should), how do you identify which records belong to a given salesperson and which records owned by that salesperson represent a single date or sale? (depending on what you answer for question 3.)

              • 4. Re: Additional Calculations
                shellas

                Mr. Caulkins,

                 

                I have 1 table named Commissions_Calc, self-joined using an auto-increment serial.  These are 3 records, shown in a portal, displaying fields from Commissions_Calc2, via the self-joined relationship. This is for one amount for only one salesman for a specific date.

                 

                I tried creating a new record just now for a different salesman, and the portal on this separate record starts at 4.  Besides, every record shows the same portal with the same information.  The whole design seems to be flawed.

                 

                 

                 

                 

                 

                • 5. Re: Additional Calculations
                  philmodjunk

                  I suggest a second table, SalesPersonnel, with at least the following fields:

                  PersID (auto-entered serial Number)

                  Name (text)

                  gDate (date field with global storage enabled)

                   

                  In your current table, (I'll call it Sales),  you'll need to add the following fields if you don't already have them:

                  SaleDate (Date)

                  PersID (Number)

                   

                  Relate your tables this way:

                  SalesPersonnel:: PersID = Sales:: PersID AND

                  SalesPersonnel::gDate = Sales::SaleDate

                   

                  Enable creation of related records for Sales Records.

                   

                  Now you can place your portal to Sales on a SalesPersonnel layout to show all sales transactions for a specified date. Put gDate on this layout formatted with a calendar popup so you can select different dates to see the appropriate set of transactions.

                   

                  Now we can talk about your original issue...

                  I'd add one or two additional number fields for this and expand my portal width so these become additional columns. Let's call those two extra fields "Shortage" and "Deduction".

                   

                  Now your calculation would look like this:

                   

                  Sum ( Sales::Commission calc ) - Sum ( Sales::Shortage ) - Sum ( Sales:: Deduction )

                  • 6. Re: Additional Calculations
                    shellas

                    Thank you, Mr. Caulkins. Your suggestions work quite well. 

                     

                    Sorry for the late response.  I was working on another Filemaker project and just got back to this one.