get found count with conditions

[Curtis_B]

Maybe it's getting too late for my brain, but I'm stuck on a concept here.

I have two related tables, office locations and claims. Each office location will have many claims. Each claim will be linked to only one office (by inputting an office id). For the sake of clarity I will simplify my objective in this post.

Lets say I want to have a list in the office locations table, where all offices will be listed row by row. For each row there will be a calculation telling me how many claims that office has in total. I'm not sure how to accomplish this.

I know that in SQL I would be selecting the row count where officeid = officeid, for each office.

Your help is appreciated!

Curt

[The Shadow]

I assume you already have created the relationship on officeId in your relationships tab in Define Database.

Once you have that, you create a summary field in the claim table that is count of claim::officeId. When you place this related summary field on a layout based on office location, it will count claims, but only those related to the office record you're on.

Hope this helps.

[Curtis_B]

Awesome, that did satisfy my simple example. Thank you.

Now, here's where things get complicated. Each claim is considered either 'on time' or 'late'. I can represent this status in a number of ways. My Ultimate goal on the list of office locations is to calculate the number of 'on time' claims vs. 'late' claims. To add another level of complexity, this number will be based on a user inputted date range (globals, I currently have them in the office locations table).

So basically, I want to add conditions on top of this summary. Any suggestions? I apologize if this explaination is too vague.

Thanks again,

Curt

[The Shadow]

Here's a simple example to look at. By setting up the late/on time condition as relationships to new table occurences you can keep reusing the total summary fields to obtain the count of claims from different points of view.

Note that the summary fields don't always update immediately, but the counts will be correct for reports.

Also, if new conditions are something too elaborate to test with a relationship, you can create a calculated field in claim that returns 1/0 for passing the test, and then count that instead.

office.fp7.zip

[Vaughan]

A normal calculation field with the expression Count( claim::officeId ) would perform the same function as a Summary field, without the problems.

[Søren Dyhr]

I can perhaps see some point in not littering the solution with fields having redundant functions, and a scripted flush cache to disk is what it takes to freshen the figures.

But simple unstored aggregation calc's on your relational configuration like this:

Count ( lateClaims::claimId )

and

Count ( onTimeClaims::claimId )

Both with the "Do not evaluate..." tag lifted away from default, will give the missing "liveliness"....

So what I'm after is a whitepaperish argument for when to use each of the methods, something points in direction of the scaling of the solution as well as the layouts rendering over a networked solution ...beyond that is related summaries not documented, and may evaporate with next upgrade of filemaker.

I know it's a very open question, but I think a lot of the developers really could benefit from a guidance to balance the two approaches against each other ...and hope you can find time in between the work with Aperture.

--sd

[Curtis_B]

Yes, that attachment was a tremendous help. I have never set up a condition as a relationship to a new table occurence before. I didn't even realize I could do that.

Thanks, problem solved

Curt

Edited May 23, 2007 by Guest

[The Shadow]

Oh right, now I feel silly. I was thinking about summaries and I completely forgot about a straight Count().