Count function returning incorrect result

[pwhalen]

I have a Layout based on a table "Projects".

The layout has a portal which shows scheduled tasks via a relationship to a "ScheduleTasks" table.

Each Schedule Task can have several Comments associated with it via a relationship to a "ScheduleTaskComments" table. (Each comment can be marked as Resolved in a "Resolved" field.)

Each Schedule Task row in the portal has a "Complete" field which can be checked when the Task is complete. Instead of simply checking the "Complete" checkbox in the "Complete" field to mark the Task as complete, I run a script to mark it as complete. I do this because I first want the script to check to see if the Task has any Comments which are unresolved.

To accomplish this, the script does something similar to the following...

Count( TaskComments::f_TaskID ) - Count( TaskComments::Resolved)

...in order to find out how many Comments remain unresolved.

The problem is that the Count function returns results for the Comments for ALL Schedule Tasks, instead of just the one in the portal row where the button was clicked.

The Count function works properly in a Calculation field in the "ScheduleTasks" table which tells me how many comments are associated with each task.

Any ideas?

Thanks,

patrick

[pwhalen]

The Count function works properly if I use the GoToRelatedRecord script step and temporarily switch to a layout based on the "ScheduleTasks" table instead of the "Projects" table. (A minor inconvenience.)

Does this seem right? Shouldn't the Count function consider the fact that it is being called from a portal row, or not?

patrick

[ESpringer]

No, the way the relational paths work, all comments related to all related tasks count as related to the main record you're working on.

One way to solve is to put the calculation of how many tasks are unresolved right there as a standing indicator in the ScheduleTasks table:

Count( TaskComments::f_TaskID ) - Count( TaskComments::Resolved)

If it's in the ScheduleTasks table, it will get only the TaskComments related to that task, which is what you want.

Yes?

[pwhalen]

Thanks, ESpringer, for the info.

It seems like that would work, but I just hate creating extra fields for such purposes, so I'll probably just stick to the 'temporarily going to the related record in a different layout' solution.

I still think it seems odd that the calculation wouldn't consider itself to have been run from the record in the portal, rather than the main record displayed in the layout. But I guess I'll live.

patrick

[ESpringer]

Another solution (though it also involves another field, but a global, which is very light on storage) is:

Have a global field in your main interface db, and have the first set of the script be taking the ID from the related (portal) record and putting it in this global holding field.

Then set up a relation between the global (which now indicates a specific Task) and all the comments which match on TaskID. Now this new relation will draw only on the sub-set of tasks relevant to the portal row with which you started.

Cheers.

[pwhalen]

Another good idea. How does Filemaker deal with a situation where 2 or more people run the same script at the same time? Is it pretty good about ensuring that the right person gets the right value from the global?

Thanks again for the info.

patrick

[Fitch]

Global fields are local to each user, so there is never a conflict.