Summary Field Not Updating

[Jammer Six]

Hello.

My first question here. I’ve been working with FMP since it was Filemaker, but I’m not a professional, and I think FMP has managed to get away from me this time.

I have no idea why my summary fields aren’t working.

Here are some of the facts:

I’m designing a database to keep track of project costs in a game. Everything is fictional.
The problem is in the second line of tables. I noticed the problem in this layout:

This shows Items, that are used to build Goods, and the Items are made up of Materials. This screen shows the table Items, and the portal shows records from Items Material Join Table. Items are added to the portal by the pop up buttons on the left, that show an Item’s name, but enter the serial number attached to each Item at creation of that Item’s record. The Jita Price column is also from the Item table.

The Number of Units column is from the Item Material Join Table, and entering a number there allows the calculation field Cost, which is in the Item Material Join Table to calculate the cost of that line. It is unstored, and this all works.

What doesn’t work is the summary field Total Item Cost, which is outside the portal, and in a Leading Grand Summary part. It should calculate a running total on Cost, but it only does so when it ******* well feels like it.

Enter a new list, and it happily shows the result. However, go to one of the records and change something, like Jita Price, to reflect reality, and it doesn’t update. Except sometimes it does.

Can anyone help me before I decide to buy a shotgun and blow this screen off my desk?

 

 

 

 

 

EVE Manufacturing.fmp12

[Jammer Six]

Now for a specific demonstration of the problem.

Starting anywhere in the attached DB, click the Goods List tab. It should go to a blue list.

In the column “ID Number”, on the left, click “13”, which should say “Flycatcher” next to it. It should go to the blue form view of a record named “Flycatcher”.

Click the “Item” tab. It should go to a record named “Cormorant”.

This is where our problem lives. The portal shows records of Material assigned to Cormorant, with the number of units needed and the Jita Price of each unit. It totals into a calculation field named “Cost”, the column on the right.

Cost is summarized in the summary field Total Item Cost, in the box under the name of the Item at the top.

The summary field is in a Leading Grand Summary part.

And now the problem.

Note the Total Item Cost for the Cormorant, whatever it is.

Move the cursor over the name of the first Material in the list, (probably Tritanium) until it changes into a cartoon hand. Left click. It should go to the related record, Tritanium.

In the Jita Price, change the price of Tritanium.  ( testing, I usually just add or remove a bunch of zeros to the price.)

Click outside the field.

Click the Item Form tab to return to the Cormorant record.

Note the following: the price of Tritanium in the Jita Price column of the portal will have changed, because the record Tritanium changed.

However, the summary field Total Item Cost will not have updated, in spite of the change to the price of Tritanium.

If one changes the price of Tritanium by using the the field in the “Jita Price” column of the portal, the Total Item Cost field updates properly, but it misses changes that were made that weren’t made on this layout.

Since this information and the change any price change creates is one of the main reasons I built this database, this makes me sad.

[comment]

12 hours ago, Jammer Six said:

it misses changes that were made that weren’t made on this layout.

Filemaker caches the values of records in the portal. Filemaker monitors the portal table for changes - but not other tables that may influence the result, as they do in your example.

Therefore, when you return to the Items layout, you need to do:

Refresh Window [ Flush cached join results ]

to refresh the summary field value.

If instead of the summary field you use a calculation field in the Items table. then you only need to call Refresh Object [] on that field or click in it (but may have to do so even if the change takes place inside the portal).

--
P.S. Do you really want the item's cost to change when a material's price changes? In most cases that use this data model, the price is looked up into a field of the join table so that future price changes do not affect historical data. Your arrangement would make sense only if all items are created at the same time, and the prices of the materials are being determined during the same period.

 

 

Edited March 7, 2021 by comment

[Jammer Six]

Thank you!

This database isn't meant to be a record of any kind-- it isn't meant to track sales, work up bids or even generate estimates.

It's meant to be a decision tool. In the game there comes points in time when a decision has to be made-- do I build this product or that product? How about these three products?

At that point, I update all the prices to prices I know I can buy the raw materials for, update the selling prices, and then see which products have the highest ROI. Then I make the decision, I'm going for that product, or these two products, and I shut the database and figure out how many of my chosen product(s) I can build with the money I have in my hand.

And I put the database away until the next time I have to make a decision about which product to build. (At which time all the information it contains will be so far out of date it will be useless, and it will need updating all over again.)