Find totals from a value list

[Kicker3]

Is there a way to find all totals using a value list? I have a value list in my invoice layout that has several values in it ( salt,plow,walks and so on)

I want to be able to find a total $ amount spent on salt for example. I know I can do a find & find all invoices with salt item on them. but how do I find a grand-total for ALL invoices with salt on them & have it total the amount of $ spent on salt. Can this be done? I'm stuck...

[Reed]

You need to create a summary field that totals the amount in the line items. The summary field depends on the found set, so if you find only the records for salt, the total will only be for those records.

Dana

[Kicker3]

Yes, but the total for those records also contain other items on the invoice. I need a total of salt only.

[Reed]

Yes, each invoice has multiple different items. But those items should be in a separate line items table, where each record is a single item.

If your data isn't set up that way, then you'll have to explain exactly how it's structured to find a solution.

[Kicker3]

Yes, each invoice has multiple different items.And those items are in separate line items table, so how do I formulate it to add those line items?

[Reed]

Here's something I just threw together... I created one calc field in the invoice table that sums all the line items for that invoice. Then I created a summary field that creates the grand total for all found invoices. For the totals for specific items, I created a separate table occurence for line items that is related to invoices by the invoice id and a global "filter field" in the invoice table. Another calc field in the invoice table sums the specific line items for each invoice and another summary field totals the found set for that field. There is a screen refresh issue here, but if you use it how I set it up (choose the specifc item from the popup in the lower right of the layout, and click find) Only invoices with that specific item will be in the found set, and the two summary fields will give you the grand total and the total for specific items.

There are surely other ways to accomplish this, but this works for me. Let me know if this fits the bill for you.

Dana

invoice.fp7.zip

[Kicker3]

Thanks

Works great... I just need to figure out how to use my layout, because the cost of any one item is never the same price. One time salt cost will be $ 24.00 and the next it might be $ 107.00 But I'm alot closer than I was...

[Reed]

Then you just enter the cost in the line items table directly instead of storing it in the products table. Just change the calculation fields to sum whatever field you want.