Counting and SubSummary Parts

[Angela10]

Having a problem here. Working on a database for a law firm. Main table is Cases and the related table is Clients. A case can have many clients. Clients can be active or closed. Attorneys are assigned on the client level. An existing report shows attorney client assignments for active cases using the sub-summary report feature and a summary field. We search for active clients and sort by assigned attorney and the display is like:

Atty Assignments

Brenda 17

Bob 20

Sarah 15

This worked great until they realized they need to see not only a count of the client assignments but also a count of the number of unique cases comprising the attorney assignment count. Something like this.

Atty Assignments Cases

Brenda 17 5

Bob 20 8

Sarah 15 10

For the life of me, I can’t figure out how to get at calculating the case count. Any suggestions?

PS-this is a legacy database in version 6, with plans for the upgrade to 9 in near future. However, this report is needed ASAP (of course)

[LaRetta]

Hi Angela,

I'm concerned with your structure or I don't have all the pieces of your structure. If you have one case with many clients and you are creating your report from the clients table then each client will only have one case and the numbers will be the same. You appear to be asking how many unique cases each ATTORNEY represents but that would mean that you must have an attorney field in each case. Do you? Do you have an attorney file? I believe what you are missing is the many-to-many possibilities and I would suggest a join table. Why?

A client may have more than one attorney – what if they contact your law firm to represent them in the future in a different case? Or what if they want one divorce attorney and one tax attorney? The client may have one inactive case but another active case. And an attorney may be joint on one case representing class action (or group of clients). And multiple attorneys may be assigned to one case. And the combinations go on …

If you use a join table then all the IDs (and only the IDs) will track the many-to-many relationships. Each time you add any piece to this puzzle, you create a join record representing the complex relationship, answering these questions (and pulling the IDs from each of your files: Who is the client? Who is the attorney? What is the CaseID? And how do you track retainers? I mean, what if a client has an attorney but no case?

So overall, I believe your confusion stems from insufficiant structure. If you do not wish to modify your current structure, you will need to add the attorney into the case file. This could be handled using a multiline (if more than one attorney). You would then join from Clients::Attorney to Cases::Attorney and create a value list based upon this new relationship. A many-to-many join table will resolve all reporting issues. In fact, I began by creating a vs.6 structure including Attorney file and join file but decided it may not be the direction you wish to go. So I await your input first. Please feel free to ask further questions or provide more information. We are here to help.

LaRetta

[Angela10]

I didn’t describe the structure accurately.

The database was developed to handle a particular practice group and jurisdiction – no need to develop for other types of cases, etc. We have the following:

Clients (defendants – one record for each defendant that is our client)

Cases (plaintiffs – one record for each plaintiff’s case we are involved in)

Clients in Cases (defendants – one record for each Client that is named in the Case)

No attorney table as of yet. But can add one.

The report I’m running now is in the Clients in Cases level. Assigned attorney is a field in this table. We run a search for clients in cases with active status and then group by assigned attorney and offer a count of those assignments.

Now, they want to see in the same reporta count of the Cases the attorney is working on. So for example, attorney Brenda has 17 active assignments in the Clients in Cases level. We represent multiple clients in a case and the same attorney can be assigned to more than one client it a case, it turns out Brenda actually works on 10 Cases but for 17 clients in those cases. Make sense?

I tried playing around with creating a value list on the Case level based on the relationship to the Clients in Case and getting a list of attorneys that work that case. Then I wanted to build a relationship using the Get (value list ID on the Case Level and the assigned attorney from the Clients in Case. Got an error message - the value list id field can’t be used as a match field. I think I’m on to something there, since I can get the count to work if I manually enter the Clients in Case attorneys on the Case Level.

Anyways – that’s where I’m at. As my dad would say – “there’s more than one way to skin a cat” – any advice and assistance is much appreciated!

Thank you

[LaRetta]

Thanks for clarifying. Here is one cat-skinning technique ...

Create a second relationship, joining ClientsInCases to itself and join them on the ATTORNEY field. Then create a value list called Unique Attorney Cases. Use values from field and only related values and (in the popup), specify the Unique Attorney Cases relationship. Use values from field (below left) of CaseID. Then create a calculation in ClientsInCases (number, unstored) with:

PatternCount ( ValueListItems ( Status ( CurrentFileName ) , "Unique Attorney Cases" ) , "¶" ) + not IsEmpty ( Unique Attorney Cases::CaseID )

Place this field up in the leading part next to the client summary and attorney's name.

LaRetta

[Angela10]

Thank you! Thank you! Thank you!

That worked. FYI - for anyone using this solution in the future, the calc text had some extra stuff in there - the Status part isn't needed.

Here's the final calculation in my test database -

PatternCount( ValueListItems( "MyDatabase.fp5" , "Unique Attorney Cases" ) , "¶" ) + not IsEmpty(Unique Attorney Cases::zkp_Case ID)

[comment]

Poor cat. I believe this can be done by a report from the join table, sorted by Attorney, Case.

A sub-summary by Attorney is the only part we really need for this. The number of assignments is a simple summary field:

sCount = Count of SerialID (or any field that cannot be empty)

Next we need a calculation field:

cInvCountByCase = 1 / GetSummary ( sCount, Case )

And the number of distinct cases for each attorney will be given by a summary field:

sTotalInv = Total of cInvCountByCase

Me and the cat like this better, because it respects the found set.

[LaRetta]

Yours certainly is better, Michael! And you are right that we must respect the found set here and mine doesn't. I had actually tried using GetSummary() and a summary here but couldn't get it work properly. I now see where I went wrong with that attempt. Thank you for stepping in. Angela? I suggest you use Michael's method!

Poor cat indeed!! I not only skinned him but I tar-and-feathered him as well!

LaRetta

Edited October 20, 2008 by Guest
Added joke :^)

[Angela10]

Oh no….now I'm confused cat. I tried the suggestion from Michael to respect the found set and I’m missing something.

Here’s my understanding of the solution….

1.) I create a summary field called sCount in my Clients in Case that counts the zkp_record ID. zkp_record ID is an autoenter serial no used as a a primary key when needed. Always has somethign in it.

2.) Then, I create a calculation field that does the following: cInvCountByCase = 1 / GetSummary ( sCount, Case )

So, I can name that calc field what I want, and then modify the calc provided as such:

1/Get Summary (sCount, Case (what’s this case field?))

3.) And den...I create another summary field to total what I created in the calc field: sTotalInv = Total of cInvCountByCases (aka whatever I named my field)

4.) Finally, I run my report and sort by assigned attorney (anything else?)

So, I try it and I get nothing . How sad for me :

Pointers?

[comment]

Well, you have cases and Filemaker has a Case() function, so what we have is a case of confusion…

The 'Case' in:

1 / GetSummary ( sCount, Case )

is the field that identifies a case (the matchfield to the Cases table would be good for this). I should have called it CaseID.

The sort order must be by attorney AND by the CaseID field, in that order.