Totals of Calculated fields - not updating.

[Mike-LI-NY]

I have a staff file ste up as follows:

Each staff member has a gross salary field, as well as a portal to another table that creates advances for that staff member as needed. So EmployeeA could have 3 entries in the advance table that total $60 (this result is computed from the original table, using a calc called Advances_total (def: Sum ( Advances::Advance_Amount ) + Cash_Advance_100), where Cash_Advance_100 is simply a number.

I then have a report that lists all staff with their total Salary and total Advances, and at the bottom of the report, I have a summary field that calculates the Total Salaries of everyone in the found set, as well as another summary field that Totals all Advances for everyone.

The problem arises when you add, delete or change an entry in the advances table - the summary field that totals advances doesn't update itself. Nor does the Total Advances field for that staff member (when on the report that lists everyone). If you click into the field on the report, it will update, but the summary field will remain unchanged.

Where did I go wrong? Can anyone provide me with some help? Thanks in advance!

06tlctaff_Clone.fp7.zip

[Søren Dyhr]

Alright first off, how did you get away with syntax like this:

${Bonus $1} + ${Bonus $2} + ${Bonus $3}

...and what does it??

--sd

[comment]

In version 7, you can use the $ character in a field name.

In version 8, a reference to such field will be automatically escaped as ${field$name}.

Help > Creating a database > Working with formulas and functions > Using a reserved word or symbol for a field or table name:

"If one of your field names or table names is a reserved word or contains reserved symbol, you must put the characters ${ } around the name when it appears in a function."

[Mike-LI-NY]

Old field names from a file that kept getting migrated from an older version of FM - most of them are gone now...

Any suggestions on the summary fields?

[Søren Dyhr]

Thanks for sorting this out Comment!

Well it was somewhat of a challenge to trace down what it really was that caused it until I noticed your severe tagging off in the defs dialog. The portal has a sortorder!

What you need to do is to attach a script to the X in the portal, looking like this:

Delete Portal Row 

Set Field [ 06tlcstaff::pk_staffID; 06tlcstaff::pk_staffID ] 

Commit Records/Requests [ Skip data entry validation; No dialog ] 

--sd

[comment]

I don't see any refresh problems when the amount is changed. Deleting/adding requires committing the record - and nothing else. What am I missing?

[Mike-LI-NY]

Still no luck. Do this.

Open two windows (impt) - one to the American Payroll layout, the other to the Form view. Find All.

In the American Payroll, see that pat Meadors has ADP Advs of $0.00, and notice the summary field isn't the correct total for all 6 records (yet the other summary fields are right). Rather than giving the total, it's only showing the value of the current record.

Now, on the Form view, go to Pat Meadors record and add a $200 advance. Go back to the American Payroll Report in the other window and notice that nothing has changed. Click into Pat's ADP Adv field and the 0 changes to a 200. However, if you were to change her Earned Salary on the Form Page on the Staff Info tab, it would instantly update in the other layout.

So I guess there are 2 issues -

1) Total Advance information isn't being updated on the American Payroll layout when it is modified on the Form.

2) The Summary field isn't calculating properly.

[Søren Dyhr]

In the American Payroll, see that pat Meadors has ADP Advs of $0.00, and notice the summary field isn't the correct total for all 6 records (yet the other summary fields are right). Rather than giving the total, it's only showing the value of the current record.

You've made Tot ADP Advs a Sum( calc'field and not a summary field - why? The Sum( function requires either a listing of fields to sum or the same field in several records over a relation. So the Sum( of just one single field in the record is just a copy of the field it self. A worthless calc' to be honest!

--sd

[Mike-LI-NY]

Ok now the ADP Adv Summary field is correct (changed to a summary field) - thanks for catching that. However, it's only correct if no changes are made while on the American payroll layout - if you change the salary, it is automatically updated, alas taht's not the same for Advances.... any idea why that isn't updating?

[Søren Dyhr]

However, it's only correct if no changes are made while on the American payroll layout

What is this, changing stuff in a report'ish looking layout... almost a sacrice. Only "Misc Check" can be altered at all what is supposed to be reflecting this change??

--sd

[Mike-LI-NY]

Now, on the Form view, go to Pat Meadors record and add a $200 advance. Go back to the American Payroll Report in the other window and notice that nothing has changed. Click into Pat's ADP Adv field and the 0 changes to a 200. However, if you were to change her Earned Salary on the Form Page on the Staff Info tab, it would instantly update in the other layout.

You don't make the change on the American Payroll layout - it's if someone else is working in teh database and makes a change or something - Salary automatically updates but the Advances & Advance Summary field do not.

[Søren Dyhr]

it's if someone else is working in teh database and makes a change or something - Salary automatically updates but the Advances & Advance Summary field do not.

Take a look at this post of when things freshen:

http://www.nabble.com/GetNthRecord-question-t1520416.html#a4188900

...here pick 4th post!

In addition to this, the refresh may sometimes not happen immediately

when the change of a referenced field happens externally (i.e.

another user modifying a field referenced by the calc), but in most

cases this is because the change was not commited yet.

This is your situation isn't it, so it's expected behaviour!

--sd

[Mike-LI-NY]

Shuold I expect that even if I'm the person making the change in another open window?? Because if I make a change or add an advance in one window, the total advances for that staff member will update on the FORM layout (therefore I assume the change has been committed) and then switch to another open window with the American Payroll, it isn't updated.

However, if I were to change the salary on the FORM layout, it will update both individual and summary fields on the Payroll layout. Are you saying that because the Staff Member's Advance total is a sum from another table it isn't refreshing?

Interestingly, after you make a change and go to the Payroll layout, and click into the staff member's Total Advances, it will update itself, but the summary field doesn't update.

Is there a way to force it to automatically recalculate?

[Søren Dyhr]

Is there a way to force it to automatically recalculate?

Yes - read what Honza writes! And especially to the post RPN issue mentioned very late in his post. Evaluate( with an extra paramter is another suggestion:

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

--sd

[Mike-LI-NY]

SD - this is getting all a bit too technical for me - I'm a pretty basic user without extensive programming/scripting/calculation experience. Can you make it clearer for me? I'm not sure which calculation to alter and how to do it.

Thanks for all your help so far!

[Søren Dyhr]

Well you might get away with stuffing this script-command in a button labled "Freshen"

But I felt a little baffled by the plentora of flatfile kind of fields in your fields def. bearing more spreadsheet reasoning than genuine database reporting.

What complicated the matter for me was that I thought you were on fm7 and not fm8, where the Go To Related Record didn't provide the "...match" feature yet. I would have hated to use a Copy All Records in a separate layout to gather all the keys for the found set, and the make a GTRR from it.

The whole idea for previous versions is descriped here

Then did I see that you used a distinctive fm8'ish feature tabbed layouts, this means that if I were in your situation ...probably would make a scripted snapshot without using a single Sum( ...by pulling the entire business to the related file where I make a "XOR-Torso" : which means a bodyless subsummary report. Take a look at the attached template.

Since I use a linetotal relying on both summary fields and ordinary fields isn't there circumventions availiable, other than to utilize the GetSummary( function for the purpose (I think??).

There is another snag to it, I would gladly have been without the use of globals, but Getsummary isn't behaving over a relation:

http://network.datatude.net/viewtopic.php?t=128&sid=0fce1899ccea4fc6411a4f32b74f7695

There is one outstanding issue though, the GTRR matching a found set won't include records where nothing is entered into a portalrow, but the script's part dealing with the globals considers these figures as well ...so I need an idea how to tighten it up ...anyone??

...anyway give the approach a thought or two!

--sd

summaries2sided.zip