
1. Re: Modify Sum Functions
schamblee Oct 4, 2012 6:12 AM (in response to brentjohn)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 Oct 4, 2012 6:31 AM (in response to 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 Oct 4, 2012 7:11 AM (in response to brentjohn)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 Oct 4, 2012 8:28 AM (in response to brentjohn)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 Oct 4, 2012 10:03 AM (in response to brentjohn)Thank you both for your help.
Here is what I have:
Table: Project_Set_Up
Field: ps_Name (Text)
Field: ps_Number (Autoenter 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 (lookup 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 Oct 4, 2012 10:12 AM (in response to brentjohn)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 Oct 4, 2012 3:12 PM (in response to brentjohn)Phil,
The relationships that link the tables is ps_Number (Autoenter 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 Oct 4, 2012 3:37 PM (in response to brentjohn)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_NumberIf 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 Oct 4, 2012 4:33 PM (in response to brentjohn)Phil,
That WORKED!!!
Thank you  as always!!!!!!!