How do I sum portal based on criteria and display?

[J__]

Hi,

I have a table of data, which I display in a portal on a layout.

Actually there's a parent Owner -> Children (in portal) relationship going on.

I want to display a sum of all the children - no problem I use Sum(childTable::Amount) and display in a field.

I also have a field in the child table, say called code, which contains who the record was added by. I would like to display in another field (above the portal) the sum of a specific code. so, let's say I had a code number of 42 and wanted to display the sum of those records - I still want the portal to show all child records no filtering or anything.

If i could use SQL, it seems like I could just say

Select * from Children where Code = 42

and then sum those.

what's the trick?

thanks for your help in advance,

sincerely,

J__

[transpower]

Use a summary field for each possible code in the child table. Then place these fields on the parent layout. If the relationship between the parent and child tables is properly set up, then the fields on the parent layout will show the correct totals.

[J__]

Is this summary field for in a report?

I looked at the help on this and I see you can do Total Count of.

Ok, I was trying to use Sum(childTable_AmountFld) and that gives me the total sum of all records for my child tables' records. - i definately need that.

Here's a better example, which i have concocted - say you have a table of SalesReps.

you have 1-M relation to another table called Sales. Each time they make a sale, a new child record is added. Maybe you have a state field in the sales table, and you would like to display a sum of all the sales amounts in the Sales table, which are in North Carolina. so you have to somehow 'observe' the state field and only sum the ones that match - that's what I am struggling with. I can sum all of them - and it seems to work using Sum(salesAmount).

So, for a specific Sales person, I want to see what he or she did for teh whole country and then I want to see an amount for just the state of north carolina.

the other thing is that I want to see those values both on the form at the same time, not if i pick one or the other. My experience is using SQL databases, where I could use a select statement and a join with a sum, but this doesn't appear to be the filemaker way and that's what I'm trying to figure out.

Does that make sense? I hope I'm explaining it in a reasonable way.

thanks for your help in advance,

Sincerely,

J__

[J__]

ah- ha ! I got it.

is this right? you create another relation, which has the criteria that matches what you want.

Then you can use the Sum(sales_amount) to get totals for it, is that right? Looks right.

so, you might create a relationship which says State=State and SalesPeople_PK = SalesPeople_FK

is that right? seems to be.

Ok, what's the downside now?

thanks,

sincerely,

J__