Sum bug (see attached dB)

[kam]

Attached is a very simple database with a huge problem. There are 3 tables: Category, Item, & Received Items. 'Items' may be apples, oranges, & pears, whereas 'Category' may be either fruit or vegetable.

In 'Items' there's an unstored calc field that sums all related received items. In 'Category' there's an unstored calc field that sums all related items, or at least is suppose to sum them!

Just open the database & start adding some numbers in the portal. The qty in the 'Category' section doesn't update.

This is too big of a bug to not find more people screaming about this problem. So I'm hoping that it's just me or that others know how to solve this problem. Is it just me?

bug.zip

[sbg2]

Add a new Qty to the Received Items portal then click outside the portal, the Qty for Category should update.

[comment]

Try it this way.

nobug.fp7.zip

[kam]

Thanks for quick responses! Unfortunately, both suggestions don't work.

Yes, clicking out of the portal row *should* work but it doesn't.

And thank you very much for taking the time to alter the dB to make it work. Unfortunately, this doesn't do what I want.

I'm not trying to calculate the sum of all received items in both tables. Rather, I want to see the total number of apples I received. Then all oranges, then all corn, etc. But in the Category table I want to see the total of all fruit I received (apples + oranges + pears). Then I want to see all the vegetables (corn + broccoli + spinach).

In other words, in the Category section, there's a total for all fruit items, & as you browse the records you'll see there's a 'vegetable' category that should show the total for vegetables. But it doesn't work!

I'm thinking massive bug, what do you think?

[comment]

I think either I don't understand what you're after, or we are not getting the same results from the same file.

When I am on a record of Apples, I see the total of Apples AND the total of Fruits.

If I go to a record of Spinach, I see the total of Spinach AND the total of Vegetables.

All the totals seem correct to me, and update instantly if I add/change the portal data.

[kam]

Thanks.

What you describe is exactly what I want & what you would expect to happen. However, whenever I add or change a portal row the Category total does not update. I have tried this in FMP8, FMP8 Advanced & as a standalone app on both WinXP & OSX, but have the same problem every time. (BTW, there's an entry in Filemaker's knowledgebase that says summary fields not updating has been a known problem since v7. Others have described this problem in general but offer no workarounds or further details, so I'm desperately trying to get to the bottom of this.)

Seriously, it's working for you?!? For example,

Category: Fruit

Total: 100

Item: Apples

Portal Rows:

20

20

10

Total: 50

Item: Oranges

Portal Rows:

10

15

Total: 25

Item: Pears

Portal Rows:

21

4

Total: 25

And let's assume something similar for Vegetables.

Let's say I add a portal row with the value 20 for Apples. The Apples total will update to 70 (50+20). The Fruit total should update to 120 (70+25+25), but it doesn't, it stays at 100.

I would appreciate if others would verify this database works for them or if they experience the problem I describe. Of course, any help that you can offer is always appreciated. THANKS!

Edited November 17, 2005 by Guest

[comment]

It's working for me exactly as you describe that it should - both in 7D AND in 8.

[kam]

Thanks!

Hmmm. Then how is it that my all 5 of my systems (3 XP Pros & 2 OSX) display the exact same problem every time? What's different about our systems? How in the heck can this be happening?

Here's some screenshots to show my results. Notice in pic 1, there's 10 (5+5) Apples. Pic 2 has 10 (3+2+5) Oranges. Yet in each pic you can see that the 'Fruit' total is 40, which is clearly wrong. Add/modify/delete as many portal rows as you like & the 'Fruit' total remains 40.

The only way to force an update is to open & save the field definition.

This is insane...what am I missing!?!? :)

[comment]

The only thing I can think of is that you are looking at your original file, not the one I have modified.

[kam]

Thanks, Comment, for hanging in there with me!

The problem is the database you created does not do what I need it to do. Your dB does not calc the total by Category, it totals all items. I need a total for each item, and a total for all items that fall within a certain category, which your dB does not do.

So are you saying that your dB updates properly (which it does, however it doesn't yield the results I need) or are you saying my dB is updating properly?

TIA!!!

[kam]

Just to be clear (if not redundant), here's an example in spreadsheet form of what I'm trying to do. I'm quite certain my database is correct, but it's not updating the Category total. If I add/modify/change the received items the Category total will stay the same, it will not update.

I think this is a serious bug & it's difficult to think a lot of others haven't run into this. I'd love to be proven wrong! Thanks!

[comment]

1. I am talking about MY file. Your file does have a refresh problem (more on that later).

2. My file does NOT total all items. The calc is summing over a relationship, and only related items (i.e. the same category) are summed. Go to the Categories layout, and flip between the two records, and observe how the number changes.

It is quite normal for calculations that depend on cascading relationships to have refresh issues. As a rule, Filemaker does not police changes in related tables. It stores a static snapshot in a cache, and the cache is updated when *FileMaker* thinks it should be updated. Usually, it works all right, but when you put everything on one layout, it might not. Then you need to help by doing a Refresh Window [Flush cache...] script step.

The modification I have made makes each total independent of each other, so there is no cascading of relationships and it updates instantly. Even so, if you DELETE a portal row, you will need to go to another record before it will update. Once again, that is quite normal and expected.

Hopefully this makes it clear.

[kam]

:) Comment is correct! I stand corrected, & thank you very, very much for your help.

For some reason when I tried your dB the first time I could have sworn I got sums for all items. That had to be my cloud of confusion, because I re-downloaded your file & it worked as you said.

I tried flushing the cache in a manual "refresh" script but that had no impact. Very strange, but at least I now know to address this.

Thanks again!

(For those of you interested in the difference between our two databases, my 'Items' table totaled each related 'Received Item', and my 'Category' table totalled each item in each category in the 'Items' table. What's important here is my 'Category' calculation totalled the values in the 'Items' table.

Comment's version makes one important change. His version calculates the totals in the 'Received Items' table, not the 'Items' table as I had done.

While I would argue that either way should work, clearly, Comment's method just plain works. And we can fortunately put this one to rest!)

[comment]

Just in case you're interested, here's a couple of links to threads disscussing the refresh issue:

http://fmforums.com/forum/showtopic.php?tid/171568

http://fmforums.com/forum/showtopic.php?tid/160650

http://fmforums.com/forum/showtopic.php?tid/165477

http://fmforums.com/forum/showtopic.php?tid/168252/

[Søren Dyhr]

I start to wonder.... is apple frequently going to change cathegory from fruits to vegetables??? This means that the creation of the portal rows is a multi-criteria relation that allows creation of related records, while the sums is sitting on one of each individual key.

I suggest you do it this way as the tamplate does it, because you simply can't freshen the way you do it....

--sd

buzz.zip

[kam]

Comment, thanks so much for all of your help & the thread links!

I'd like to point out a possibly big disadvantage between our methods. Take a relationship like this: Table C -> Table B -> Table A.

Your approach (which works so clearly it's the only appropriate method : ) relates from Table C to Table A. My approach (which doesn't update properly ) relates Table C -> Table B, & Table B -> Table A.

But what if Table B contained a complex calculation that it performed on Table A's data. And Table C took the value from Table B to perform an additional calculation.

My approach works well (er, should work well) with this model because each calculation is performed only once. But it appears your approach requires these calculations to be performed twice, once in Table B & then once again in Table C. If there's a lot of records, this could be very slow.

This is just an observation not a criticism because again, clearly your approach works while mine will not until the FM developers make some changes. Man, is FM a love-hate relationship or what!?! :)

[comment]

I haven't changed your relationships.

A relationship like:

Table A <-> Table B <-> Table C

automatically makes some records in Table C related to some records in Table A, and vice versa. I simply took advantage of that, so that the "grand total" could be computed directly, without middlemen.

I am not suggesting this would be the solution for every case. And there IS a refresh issue with complex calculations, relying on cascading relationships. I have posted several demo files with a big 'Refresh' button (you saw some of them, if you followed the links above).

But in a real solution this problem would be hidden from the user. I don't think a user engaged in the receipt of a shipment of apples is in a hurry to know what it does to the overall quantity of fruit. Let him finish the data entry, and when he comes back, the correct data will be there.