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.
Thank you for the advice - I an using FM Pro 11 and the Invoice Sample does not have the Invoice Data table.
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.
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.
( Qty * Unit Price ) * Discount Rate
total = Qty * Unit Price ;
discount = total * Discount Rate
total - discount
If ( IsEmpty ( Invoice Data::INVOICE ID MATCH FIELD ) ; 0 ; Sum ( Invoice Data::Amount ) )
Thank you both for your help.
Here is what I have:
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))
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.
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 ?
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.
But which table links to which?
Is it this or something different?
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.
Thank you - as always!!!!!!!