Jump to content
Sign in to follow this  
Curtis_B

get found count with conditions

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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