Jump to content

Count function returning incorrect result


This topic is 7233 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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. smile.gif

patrick

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 7233 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.