Live Updating Summary?l

[Codeus]

Hi there,

I have a table of pupils exclusions, there may be multiple exclusion records for each pupil on different dates. I want to be able to see the total number of days the pupil has been excluded for while adding a new record.

To do this, I have created a summary field of days excluded from each record. I then add this summary via a self-join table reference by pupil-name.

This works fine, but at present I have to commit the record and refresh the screen to see the changed value (or hit enter while I am in that field).

Is there a smarter way to do this that would allow the total field to update automatically?

A simplified version of the file is attached below, any help would be appreciated!

Regards,

Codeus

summaryupdate.zip

[Robert Kidd]

Change from a Summary field to a Calculation field with result

Sum(test 2 artistlink::NumberOfDays)

[Codeus]

Excellent, that works - thanks!

Oddly though, if you remove the self-join summary field from the layout, the calculation-sum field stops updating automatically. You seem to need both on the layout for it to work.

I have attached another example file as before with 2 layouts - one working with the summary field and the other broken when it is removed to illustrate the point.

This isn't a problem as I can just hide the summary field behind other objects but seems odd to me.

Anyway thanks again for the help.

Codeus

summaryupdate_2.zip

[Søren Dyhr]

Oddly though, if you remove the self-join summary field from the layout, the calculation-sum field stops updating automatically

It's not odd at all, thats the way unstored calc'fields work ...it's the rendering of a layout that pulls it - welcome to Filemaker ...but you do need to take this particular into consideration:

http://www.fmforums.com/forum/showpost.php?post/206543/

This means although your vanity dictates live figures, is it against the way a database as such works, so unless it's a very small school you're developing for, is this kind of summarizing bound to be at least one of the bottlenecks in your solution.

Since you rank your skills as Advanced, might you enlighten me of other relational database tools allowing you to have unscripted live values? Usually is it done with event triggers which in itself is contradictional ...since the triggers pulls a script.

--sd

[Codeus]

No... my point was that removing the summary field from a layout, stops the calculated sum field (which is still on the layout) from updating.

I was saying it seems odd that a calc-sum field changes it behaviour based on another field (on which it is NOT dependant) being on the same layout or not.

I also don't think it is 'Vanity' to want a running total on my users screen that updates when they tab out of the field.

I dont see how my knowledge of other databases is even slightly relevant here, I have been working with FileMaker for 10 years, not Access so forgive me if I don't enlighten you.....

Seriously, I don't think my comment deserved a flame when I was just looking for advice.

Codeus

[comment]

The reason for this behavior, I think, is that Filemaker does not refresh the screen unless it thinks there is a good reason for it.

Changing the value in the current record is not a sufficient reason to refresh the sum of related records (even though the related set MIGHT include the current record, as it does in your case - but that's just an unlucky coincidence as far as FM is concerned).

But when a summary field is present on the layout, the screen MUST be refreshed when a value in the found set is modified. And when the screen is refreshed, the related sum is refreshed too (in this case - not in EVERY case).

Note that the two solutions discussed here are NOT equivalent: the summary field summarizes found records only. A sum of related records ignores the found set.

I believe the correct approach would be to enter data into a portal to Exclusions, placed on a layout of Pupils. A calculation of Sum ( Exclusions::NumberOfDays ) would provide the 'live' total - and you could add some filtering to limit the related set to a date range, for example.

[Søren Dyhr]

I have been working with FileMaker for 10 years

So have I ...12 years rather, but the discovery of the nature of unstored fields comming in as a revalation after 10 years, suggests what???

--sd

[Codeus]

Thanks comment, what you say makes perfect sense. I will look at moving the layout to be based on the parent record as you suggest, its all in the same database anyway.

Soren, I think your 'suggests what?' comment is just trying to say I must be stupid to ask this question.

I don't write FMP 24/7 as it is just a part of my job looking after a Special Needs School but none the less I have managed to create some really useful databases over the years using it. People on this board have always been really helpful with advice and tips and I am saddened by the tone of your responses.

But I thank those who responded in a helpful manner and the day I think I know everything and stop asking questions will be the day I die

Codeus

[Søren Dyhr]

Alright we should perhaps both wear the lastname Byzantinius - I do indeed behave snotty from time to time, but if you take a look at:

http://fmforums.com/forum/showtopic.php?tid/128903/post/128903/hl//

...is the solution you think up, hardly giving your skills away either - the problem with your approach is that you usher redundant data to numerous locations, where there is no way you can clear it again - since valuelist can't build on global fields. I'm aware dynamic valuelists weren't around in fm3 because the relations weren't up to it yet, but several workarounds existed though, which a developer who thinks himself skilled "advanced" ...must have paid attention to in his/her decade long acquaintance with filemaker.

Something along the lines of this attachment, could easily have found it's way into your solutions, it's obvious that the Go To Object is way to new to be relevant when you posted. But you could under fm3 actually script loop control the number of Go To Next Field, by first go to the default in the taborder and then "fire a round" landing you in the correct popup field incarnation(it might even have been possible with fm2.1's recursive looping?)

--sd

test.zip

[Codeus]

Sorry, I dont understand what you mean.

My original question was how to put a field on a layout showing the sum-value from a set of records that match the current one (in this case by matching pupilname).

So if I had records like this: -

name: fred

days excluded: 2

name: bert

days excluded: 5

name: fred

days excluded: 4

then, if the current record being browsed was a record for fred, it would show 'Total days excluded = 6'.

I was using a summary field from a self-join relationship by pupilname. My problem was that the summary field on the layout would only update with a forced refresh, whereas I needed it to update when the 'days excluded' value was changed and the user tabbed to the next field.

The solution provided by RobertKidd using an unstored sum() works provided that a summary field from the self-joined table is still on the current layout.

The part I didnt understand was why a seemingly irrelevant field from the self-joined table instance had to be on screen for the sum() to update when the field is exited and I think that was answered by comment, that you have to have the summary on screen to 'push' the update.

My question wasn't related to valuelists in anyway - or FM3 - are you confusing this thread with another?

Codeus

[Søren Dyhr]

[Removed by Administrator]

For a refresher please read http://fmforums.com/forum/showrules.php?

Edited May 22, 2007 by Guest