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

# Modify Sum Functions

### 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

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

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

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

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

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

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

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

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

Phil,

That WORKED!!!

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