problem: cant get many to one summary to work

[juggledad]

Help! I've hit a wall with what I'm begining to beleive is a bug, but I'm not sure. Can someone tell me if I can do what I'm trying. The attached database is a minamal version demonstrating the problem.

What I'm attempting to do:

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

I have several rows of a table (slips) that have a name and cost. Well all but one have the cost filled in, I want to figure out the cost of the empty one;

Example

hg 10

lg 5

lg 23

kids x

where x = 50 - (total of others) or x = 50 - (5 + 23 + 10) = 12

That part actually works. Now I want a second table (fees) that will have one entry with the total for each name (the two tables are joined on name). For example

lg 28

hr 10

kids 12

what I keep getting is

hr 10

kids 50

lg 28

It won't compute the correct cost for the kids, but does for each of the other names.

The database is in the attached isip file

is this a bug or is it something you just can't do?

thanks in advance

Paul

PPbug.fp7.zip

Edited October 11, 2006 by Guest
attached zip file

[John Mark Osborne]

One reason nobody has answered your post might be that your download doesn't work.

[juggledad]

thanks John, I couldn't figure out how to attach a file, but in the end I figured it out

[John Mark Osborne]

Have you tried using an Aggregate function like Sum? This is generally the method you use when try to total related records. Your formula might look like:

Sum(SLIPS::Cost)

[juggledad]

Yeah, I have tried the sum. This is a weird problem which seems to be because I'm trying to sum a calculation field. What's worse is that in the many table (Firing Slips) the sum works fine - gives the correct value - in the 'one' table (Firing Fee) Here is the data from the app

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

Firing Slips

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

Potter Cost Member cost Kidclass Cost Total members Cost Total Kids Cost

HR $10.00 $10.00 $0.00 $38.00 $12.00

KIDS $12.00 $0.00 $12.00 $38.00 $12.00

LG $23.00 $23.00 $0.00 $38.00 $12.00

LG $5.00 $5.00 $0.00 $38.00 $12.00

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

Firing Fees

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

Potter ID Cost tot kid cost tot member cost

HR $10.00 $0.00 $10.00

KIDS $50.00 $50.00 $0.00

LG $28.00 $0.00 $28.00

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

and here are the table definitions

---------

Firing Slips

---------

Field Name Field Type Formula / Entry Option

Potter Text Indexed

Member cost Number

Cost Calculation Unstored, = If ( Potter = "KIDS" ; Kidclass Cost; Member cost)

Total Kids Cost Summary = Total of Kidclass Cost

Total members Cost Summary = Total of Member cost

Kidclass Cost Calculation Unstored, = If ( Potter = "KIDS" ; (50 - Total members Cost); 0)

----------

Firing Fees

----------

Field Name Field Type Formula / Entry Option

Potter ID Text Indexed

Cost Calculation Unstored, = Sum(Firing Slips::Cost)

tot kid cost Calculation Unstored, = Firing Slips::Total Kids Cost

tot member cost Calculation Unstored, = Firing Slips::Total members Cost

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

In the Firing Slips table, you can see how the entry in the 'Cost" colum is correct, but in the Firing Fee table, while it is correct in two of the cases, in the third case it is the total of all the costs from firing slip, not just the one Potter Kids

I just don't understand - if I change the item Kidclass Cost to a number from a caculation, and put a value in it, then the carry over caculation in Firing fee works.

I'm Stumped - Anybody got any ideas? (data nd definitions taken from attachment)

[T-Square]

This looks like a combined problem of:

- structural issues. It looks relationally problematic that you have multiple fields for the amount to be charged, which you have to account for in your calculation fields), and

- calculation issues. It looks like you have calculation fields that are calculating against one another in the different tables. Also, summary fields calculate for the complete found set; when you create a calculation in Firing Fees that use the summary fields in Firing Slips, you will end up with the overall summary (at least I think that's what's happening).

FWIW, I will take a look at your file a little later and see what I can come up with...

David

[T-Square]

Okay. So my brain is exploding here. Can you explain exactly what you're trying to accomplish? I *think* you've got potters who get charged one fee, and if they have kids, somehow the kid's portion is separated out from the base fee. But you're using a total fee in here for some reason--but the Total fields in Firing Slips are built on their own table, so they end up summarizing the current record.

I *think* that if you separate your potters and kids out into a People table, and then associate slips and fees with individuals (instead of piggybacking the kids onto the potters), things will begin to shake out in a more logical manner. For example, you assign a kid a firing slip and a firing fee. Dad still pays--it's just a separate transaction in your system, and hence simpler to manage.

Get back to me and I'll see what I can do further.

David

[juggledad]

Background: This is a billing application for a pottery co-op. Each peice to be fired is measured and a firing slip written up. Many peices are put in a kiln (a firing) so any one person could have many peices in a firing. Many firings can happen during the month, and we bill by the month.

we have one rate for bisque firing and one for glaze firing(bisque firing M=.005/cubic inch, glaze firing =.009/cubic inch)

the application has been running for a year and a new variable was added.

We teach kids classes. If the kiln is filled with kids peices, the teacher pays $50 for the load. The teachers want to turn around the kids peices as quick as possible, so they load member peices in the kiln with the kid peices. This means the teacher cost = $50 - (total member cost) (member cost = cubic incher * rate)

I have one table called firing slips, this contains one entry for each peice that is fired.

I have another table called firing fee that contains one entry for each potter/firing which is a total of all their firing slips. (the application then produces a report of their total fees for the month)

In the cut down example I attached, the firing slip table works fine. I can get the member total and I can figure out the teacher cost (I Identify the KIDS by the potter id). The problem comes when I move to the firing fee table.

While the firing fee total for each member is correct, the total for the kids ends up being $50 and I don't understand why.

If TABLE2 F2 = sum(table1:f1) and table1:f1 a number, the result works, but if table1:f1 is a caculation, the result doesn't end up as what I'd expect.

After all my testing and experimenting, I'm begining to thing 'bug?'

p.s. thanks T-Square for any time and light you might shine on this.

Edited October 20, 2006 by Guest

[T-Square]

Having done a little pottery in my past, I sort of surmised some of what you are trying to accomplish. With the additional detail, you given me some ideas I want to try out. I will get you a sample file in the next couple of days...

David

[T-Square]

So, I don't know if this is the right direction, but have a look. I was trying to change your system so that you have a FiringEvent and a join table for specific pieces in a FiringEvent.

I am too tired to explain, so take a look and ask questions...

David

PPbug.fp7.zip