asked on

how can I sum line items based on certain fields in the line items table

i have an invoice with invoice lines that include the following . some of the lines are for labor & some lines are for products. can I setup a field in the invoice table to give me the total for the lines with the item being labor & another field to sum the total for products.
thanks

Member_2_908359

yes, but you need a way to distinguish a labour line from a product line, ie another field in the invoices lines table which indicates the type of line it is.
If those lines are created from a price list, I suggest you put this criteria in the price list, and copy it to the line field upon record creation using tha auto-enter feature for this "type" field. Then you'll be able to summarize this lines through a link using 2 fields, the invoice ID field + the line type field.
Let me know if you habe this, and need further help

Member_2_908359

A small example of this
http://www.lesouef.net/files/invoice_lines.zip
(done with fm6)

billmercer

Eh, I believe you meant to say done with FM7...

Member_2_908359

Indeed! 8 actually... That's the problem to answer 2 questions at the same time, unlike my PC I am pretty bad at multi-tasking (and getting worst!) and I had just done another example in fm6 for the previous question, so that sentence was supposed to be in the other post...

RALPHQC

ASKER

hi
I was able to follow your instructions up to
(Then you'll be able to summarize this lines through a link using 2 fields, the invoice ID field + the line type field.
Let me know if you habe this, and need further help)

I looked at your example file,
I want to have a field in the invoice table which sums the price for manpower & a second field to sum the price for the parts.

thanks

ASKER CERTIFIED SOLUTION

Member_2_908359

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RALPHQC

ASKER

Hi
Thanks for the help

I created another field in the inv lines to calculate the extended total if item is= manpower.

I sum the field to get the man power invoice sum
I subtract the sum of the man power from the main sum to get the parts sum.

I didn't want to add more table; my solutions has so many allready.

do you think it's ok to do so many calculations or add more tables is better. (faster)
thanks

Member_2_908359

The drawback of yr way is that it is a calculation based on another caculated field, therefore slower.
The best is to convert these fields as numbers and calculate them with a script whenever needed, or avoid to display them in commonly used layouts because they are re-calculated everytime you display them