9 Replies Latest reply on Oct 4, 2012 4:33 PM by brentjohn

    Modify Sum Functions

    brentjohn

      Title

      Modify Sum Functions

      Post

           I’m using Sum calculation functions (Item Total Qty, Item Total Amount, etc.) to total items from a portal.  I need to manipulate these functions (adding or subtracting from other tables) but cannot modify a sum function.

           How can this be accomplished?

        • 1. Re: Modify Sum Functions
          schamblee

               That a look at the Invoice starter template.  Two tables with invoice calculations Invoices and Invoice data.   Invoice Data Contains a Discount Calculation, then there are Subtotal, and Total Calculations you may also want to take a look at. 

          • 2. Re: Modify Sum Functions
            brentjohn

                 S Chamblee,

                 Thank you for the advice - I an using FM Pro 11 and the Invoice Sample does not have the Invoice Data table.

            • 3. Re: Modify Sum Functions
              philmodjunk

                   In the template released with version 11, that table is called LineItems. Don't know if it has the same example calculations or not. You may need to describe one of your calculations in more detail in order for us to help you with it.

              • 4. Re: Modify Sum Functions
                schamblee

                     12 Invoice Starter:  Discounts are applied to each line item.   Payments could be entered as a negative number and would work with the sum() function.

                      

                     Here is sample calculation from the invoice starter in 12.

                     Discount Calculation:

                     ( Qty * Unit Price )  * Discount Rate

                      

                     Amount Calculation

                     Let  (
                      [
                      total   = Qty * Unit Price ;
                      discount  = total * Discount Rate
                      ] ;

                      total - discount

                      )

                      

                     SubTotal Calculation

                     If ( IsEmpty ( Invoice Data::INVOICE ID MATCH FIELD ) ; 0 ; Sum ( Invoice Data::Amount ) )

                      

                      

                • 5. Re: Modify Sum Functions
                  brentjohn

                       Thank you both for your help.

                       Here is what I have:

                       Table: Project_Set_Up

                       Field: ps_Name (Text)

                       Field: ps_Number (Auto-enter Serial)

                       Field: ps_Sub_Total (calculation SUM (li_ttl_labor)

                       Portal: Line_Item_PS (relationship: ps_Number = li_Number (allow creation))

                        

                       Table: Line_Item_PS

                       Field: li_Number (number)

                       Field: li_labor_qty (number)

                       Field: li_labor_price (number)

                       Field: li_ttl_labor (number: li_labor_qty * li_labor_price)

                        

                       Table: Job Cost

                       Field: jc_ps_Name (Text drop down list (I created a Value List referencing “ps_Name”))

                       Field: jc_lk_ps_Number (look-up ps_Number)

                       Field: jc_labor_hours (number)

                       Field: jc_labor_cost (number)

                       Field: jc_ttl_labor (calculation: jc_labor_hours * jc_labor_cost)

                        

                       Here is what I want to accomplish:

                       I want to be able to subtract (through a script) from li_ttl_labor, jc_ttl_labor.  However, due to the SUM calculation, I am not able to subtract.

                  • 6. Re: Modify Sum Functions
                    philmodjunk

                         What are the relationships that link these tables?

                         However, due to the SUM calculation, I am not able to subtract.

                         Don't see any sum function here. What field uses the Sum function and what exact expression does it use?

                         I want to be able to subtract (through a script) from li_ttl_labor, jc_ttl_labor.

                         Do You want jc_ttle_labor - li_ttle_labor ?

                    • 7. Re: Modify Sum Functions
                      brentjohn

                           Phil,

                           The relationships that link the tables is ps_Number (Auto-enter Serial).

                           ps_Sub_Total is a SUM Function – through a Calculation Type:  “SUM (li_ttl_labor)”.

                           I want li_ttl_labor - jc_ttl_labor.

                            

                           I hope this helps.

                      • 8. Re: Modify Sum Functions
                        philmodjunk

                             But which table links to which?

                             Is it this or something different?

                             Job Cost>----Project_Set_Up-----<Line_Item_PS

                             Project_Set_Up::ps_Number = Line_Item_PS::li_Number
                             Project_Set_Up::ps_Number = Job Cost::jc_lk_ps_Number

                             If I have the relationship right, it would appear that you want: Sum ( Job Cost::jc_ttl_labor ) - SUM (Line_Item_PS::li_ttl_labor)

                             This assumes a one to many relationship between Project_Set_Up and Job Cost. If the relationship is one to one (one record in job cost for any one record in Project_Set_Up), then you could use: Job Cost::jc_ttl_labor - SUM (Line_Item_PS::li_ttl_labor)

                             But note the many guesses I am making to get to these two possible calculations.

                              

                        • 9. Re: Modify Sum Functions
                          brentjohn

                               Phil,

                               That WORKED!!!

                               Thank you  - as always!!!!!!!