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
 
RALPHQCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lesouefCommented:
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
lesouefCommented:
A small example of this
http://www.lesouef.net/files/invoice_lines.zip
(done with fm6)
billmercerCommented:
Eh, I believe you meant to say done with FM7...
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

lesouefCommented:
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...
RALPHQCAuthor Commented:
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
lesouefCommented:
ok, I modified my exmaple, reload from there. my explanations were a bit short I must say, but the idea was there.
http://www.lesouef.net/files/invoice_lines.fp7
another way to do it:
http://www.lesouef.net/files/invoice_lines2.fp7

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RALPHQCAuthor Commented:
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

lesouefCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.