Subsummary/Grand Total returns value of last record only

[dpkenna]

This is fairly complex (to me), and I probably won't explain it right, but here goes.

I have two tables, Table1 and Table2, a one to many relationship. Table1 contains JobNumber (the relationship field) and JobRep. Table2 contains Portal1, multiple instances of ItemPrice, and TotalPrice, a summary of those prices. TotalPrice is multiplied by field Commission to return TotalCommission. Table2 also contains Portal2, OutsidePayments, and TotalPayments, a summary of those payments. Calculation field Profit = TotalCommission - TotalPayments. I want to run a columnar report sorted by JobRep with Subsummaries and Grand Totals of TotalCommission and Profit. But when I run the report, only the value of the last record is given for the subsummaries and grand totals. For example:

JobNumber 1, JobRep Bob

ItemPrice 500

ItemPrice 500

TotalPrice 1000

Commission 5%

TotalCommission 50

OutsidePayment 20

OutsidePayment 5

TotalPayments 25

Profit 25

JobNumber 2, JobRep Bob

ItemPrice 1000

ItemPrice 1000

TotalPrice 2000

Commission 5%

TotalCommission 100

OutsidePayment 10

OutsidePayment 10

TotalPayments 20

Profit 80

JobNumber 3, JobRep Jim

ItemPrice 100

ItemPrice 100

TotalPrice 200

Commission 5%

TotalCommission 10

Outside Payment 5

TotalPayments 5

Profit 5

JobNumber 4, JobRep Jim

ItemPrice 100

ItemPrice 50

TotalPrice 150

Commission 10%

TotalCommission 15

OutsidePayment 5

OutsidePayment 5

TotalPayments 10

Profit 5

Returns report:

Bob

JobNumber 1 TotalCommission 50 Profit 25

JobNumber 2 TotalCommission 100 Profit 80

Bob 100 80

Jim

JobNumber 3 TotalCommission 10 Profit 5

JobNumber 4 TotalCommission 15 Profit 5

Jim 15 5

---------------

15 5

I've tried every combination of leaving running total unchecked and checked, and leaving restart summary for each sorted group checked and unchecked, and nothing changes the result. I've searched Google and this forum, but I can't find anything about this. Can anyone help me?

Edited June 20, 2006 by Guest
Left out a field

[dpkenna]

Can anyone give me any guidance on this at all? Is there something fundamentally wrong with the way I've set up the database, or did I not explain it well? Any input would be greatly appreciated.

[Razumovsky]

Hi dpkenna

Your explanation is a bit difficult to follow, and it sounds like possibly a layout problem. Perhaps you could post a sample file?

[dpkenna]

Not without a confidentiality agreement. I simplified things a little bit for my explanation (apparently not enough). I guess I have to go it alone. Thanks anyway.

[Lee Smith]

I'm not sure if your file will actually help or not, but it sometimes can speeds things up by letting us see exactly how the file is made up, and we can then spot real cause of the problem.

I'm not sure if you are concerned about your data or the UI. You can Address the data by:

1). Posting a clone of your file without records.

2). Posting a copy of your file with just a few records, and remove any confidential data.

For UI,

3). Prepare a sample of your file without the proprietary interface.

Be sure to state what isn't working, what you have tried, and what it didn't do as expected.

HTH

Lee

Edited June 26, 2006 by Guest

[Søren Dyhr]

What stirkes me is the Commision % ...it's different from job to job - how does it come in, via a lookup ...an indexing issue occures otherwise?

Next issue is the calc's - Why isn't:

http://www.filemaker.com/help/FunctionsRef-341.html

...used? The issue is that although you use the persons name as related breaker value for you report are you bound to separate each order via the foreign key as breaker value as well, and your sort needs to include both fields 1) the related breaker 2) the foreignkey ...and to be honest should the comission % be an the parent record as instead.

However is it as such pretty tough to stay out of making summaries on summaryfields with this task - which is impossible without a workaround, so I have made you a template showing how I would deal with it...

--sd

pling.zip

Edited June 27, 2006 by Guest