I'm converting an FMP5 solution to FMP8.5. A count in a merge field
which was working correctly under FMP5 is no longer updating
correctly. The field's indexing is correctly marked "do not store--
recalculate when needed".

There's a bit more to it. I'm also combining multiple files into a
single file with multiple tables. If I leave the files separate, the
count still updates correctly, even after conversion to FMP8.5... but
not when everything is in a single, multi-table file.

Here's what I've got:

Table 1: Clients
Table 2: Activities

The tables are related by a primary key in Clients to a foreign key in
Activities.
(PKEYClients to fkeyClients)

Activities also has a self-relationship (fkeyClients to fkeyClients)
which allows Activities layouts to show a portal of other related
activities for the currently shown client. The relationship is named
R_SelfActivities.

Activities also has a field, cActivityCount = Count
( R_SelfActivities::Activity Comments ). This calc field is included
in a merge field on the Clients layout. Adding new Activity Comments
via the portal on the Clients layout no longer updates the count.

The count will update if I move to the next record then back, or
change to preview then back, etc.

For compatibility with very old data, it has to be this Comments field
which is counted.

I've got a couple of workarounds, but would dramatically prefer to
know my mistake here. Suggestions?

Thanks,
-Rob Steward

On May 6, 11:02 pm, robdstew... (AT) gmail (DOT) com wrote:

Basically the issue traces back to the new record locking changes in
FM7, record commit timing, and how this all gets tied back to screen
refreshes, and layout recalcuation. (FM7/8/8.5 'caches' unstored calc
fields, and doesn't automatically recalculate them everytime -
anything- changes, like FM5 did)

Part of the reason adding records via the portal doesn't affect the
calculation is that the portal records aren't immediately commited the
moment the cursor leaves the field the way they were in FM5.

Another part is that the unstored calculation may be cached and its
value not be invalidated and recomputed, even if you add and commit a
new related record.

Advancing/retreating a record works very well because it does two
things: first forces the commits, and 2nd it forces a complete
recalcuation of any unstored fields. Its a lousy workaround though,
because it fails in databases with one record, and in cases where the
found set is one.

The best solution really depends on the exact circumstances. But I
hope that by shedding some light on where the issue is coming from you
can sort it out. Typically adding 'commit records' and/or "Refresh
Window" script steps in the right places in your script can go a long
ways towards fixing those hiccups.

In cases where you are working directly on related records through a
portal, and you want their values to be updated, you may need to add a
'recalculate' button that 'commits' and 'refreshes the window'.

It may seem annoying, but actually the benefits of the new record
locking system have a lot of advantages that are particularly useful
for in multi-user systems, and I'd never want to go back.

There are occasionally some frustrating situations where commit and
refresh don't work (usually because you need to perform the commit in
a different layout, or even a different window)...

Another trap is the timing and placement of commit records. It used to
be that you could 'set field' a key value, and then reference a record
related to it on the relationship you just 'setup' by setting the key
field, but now you need a commit records between the set-field and
reference for the relationship to populate...

There are lots of little pitfalls like this to get used to in FM7+
coming from an older FM background.

Filemaker has made some good documents available on the website
regarding converting legacy FM solutions to FM7/8/8.5 and it touches
on a lot of this stuff. I highly recommend giving it a read. Hands on
Experience is crucial to really understanding it, but if you read the
docs at least you'll have an idea what's going on.

-regards,
Dave

You are trying to get the Activities self-relationship to (a) update the
calc and (b) update the Clients screen as you change data on the clients
layout. You're not going to get that to work in FM8.5.

You should either:
1) create a count field in the Clients table and set it to
count(Activities::Comments); use that as a merge field on your Clients
layout; or

2) add a summary type field in Activities that is a count of Comments.
Use that summary field as a merge field on your Clients layout.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Member, FileMaker Business Alliance

robdsteward (AT) gmail (DOT) com wrote: