How to sum values displayed in portal?

[BobP]

OK, you'll have to think way back to FMPro v.6.

I have set up a database of three related files to form a basic many-to-many relationship. One file is "projects" (manufactured pieces of furniture); another is "materials" (various lots of wood and so forth that is stored in inventory and then used in those projects); the third is a "lineitems" file that ties the two together, so that: In the projects file, I can view through a portal (to lineitems) all the various materials used in that project (and the dollar value of each). From the materials file I can view through a portal (again to line items) to see in which projects various types materials were used (and how much of that material). Each project and each lot of inventory has an ID number, which is used to set up all the relations.

Here's the problem: In the projects file, I want to have a field that displays the total cost of the materials used IN THAT PROJECT. In other words, to total the value of each line shown in the portal. Likewise, in the materials file, I want to get the total quantity that has been used so far, so that I can display how much of this lot of material remains.

I've poked around a bit on this and other forums, and it looks like I could:

a. Use subsummary reports. This just seems really clunky (at least how I've been trying to do it). Also, I would prefer the field update itself dynamically if I add a new "lineitem" via the portal, though if I have to click a button to run a script to recalculate the total, I could live with that.

b. Buy the Trio Numbers extention. I have a trial version and I have to say it confuses me a bit, but seems to do what I want. I'm happy to take the time to figure it out if I have to, but I do NOT however, need a "running total" as if this were a checkbook. I just need one total at the end of the portal. Still, maybe this is the best solution?

c. Upgrade to v.7 (or 8) which apparently has a way of doing this? Is that true?

d. Some other solution (please explain)

So my question: Which would you pick?

Thanks, much. This forum has been very helpful as I've been trying to teach myself this program.

[Søren Dyhr]

Nothing much has changed in that dept. since version 3.0, it's certianly neither summaries nor an upgrade that your solution requires. The function you need is Sum( which takes either a list of fields or the same field in a set of records a relation away.

What you can do is to investigate this template, that as a bonus learns you how do deal with kits or sets of items, entry into an invoice.

http://www.dwdataconcepts.com/dl/tm/Compile_INV2.sit

...the template is in .fp3 format that is an earlier version than yours, but the way you deal with it, is to drag all files in the folder ontop of the Filemaker Icon, to make the conversion take place.

--sd

[BobP]

Perfect. So easy, I feel kind of silly. I tried something like this at first, but it was giving me a total of ALL my lineitems, not just those in the portal. I think I must have been doing the calculation in the "lineitems" file and pulling it into the related file. You just do the sum in the file WITH THE PORTAL and it works like a charm.

I don't understand everything else going on in these templates, but they are very interesting. Think I'll study them some more.

Thanks so much for your help. -- Bob