Defining Category fields for dashboard

[Matt Malyschko]

The basic idea is that there are Clients who have Consultations for Psychological issues.

Important fields to note for Clients:

• Agency (there are 4 different agencies that a client can be assigned to - due to NDA let's call these Agency 1, Agency 2, etc.
• Employment type (Employee/Volunteer)
• Referral Cause (Work/Personal/Incident) - this is to determine the initial reason this person became a client.
• Underlying Issues - there are about 30 different reasons for this - one or more may be selected. Some examples - Anxiety, Depression, PTSD, etc.

 

I can create the summary of consultations and costs, etc. but where I struggle is how to get a summary and graphs of everything broken down into their different categories.

I've attached an excel sheet that shows the type of things I am looking for. The first graph shows total # of consults for a given Agency and Employment type combination. Eg: Agency 1 - Employees. The second shows further breakdown for this Agency 1 - Employees, into the Referral Cause. Obviously a total of Agency 1 (both Employees and Volunteers) would be good too.

I have a Months table (12 records) for the calculations. My question I guess lies in the definition and relationships between this table and the Consultations table. If I create a new field in the Clients table for each Underlying Issue (eg: underlying_issue_anxiety, underlying_issue_depression) and have a checkbox with value 1 I can link the clients table to consultations, and in months I can sum these fields, giving me the correct value.

However, as there are SO many different combinations, there would have to be 30 fields for each referral cause, and then each for employee/volunteer, and then again for each agency... Surely there is a way I can do this with having the Underlying Issues field as a list of all the different values, and checkboxes to select the appropriate ones, then use this field itself in the graphs and breakdowns?

Any help would be greatly appreciated!!

 

**EDIT** - upon further investigation I feel using sub-summary fields for this would work? But still unsure how to go about that...

[comment]

I am not sure I understand the basic requirement here: it seems that one patient has many issues, and one patient has many consultations. If this is so, then one consultation has many issues - and I don't see how you can classify it into a single issue category (unless each consultation is tagged as dealing with one specific issue).

 

 

Regarding the issue of Issues: there can be little doubt that you need a separate record for each issue a client can have. If you didn't need detailed reporting, you could perhaps get by with a single checkbox field in the Clients table - but what you describe clearly calls for a

 

Client -< ClientIssues >- Issues

 

structure (where Issues is a table of issue types - basically a value list).

[Matt Malyschko]

I am not sure I understand the basic requirement here: it seems that one patient has many issues, and one patient has many consultations. If this is so, then one consultation has many issues - and I don't see how you can classify it into a single issue category (unless each consultation is tagged as dealing with one specific issue).

 

Yes, one consultation may have multiple underlying issues. For example, one client may have only Anxiety issues, and another may have Anxiety and Depression. Therefore, under Anxiety, you would expect to see 2 and Depression 1.

 

 

Regarding the issue of Issues: there can be little doubt that you need a separate record for each issue a client can have. If you didn't need detailed reporting, you could perhaps get by with a single checkbox field in the Clients table - but what you describe clearly calls for a

 

Client -< ClientIssues >- Issues

 

structure (where Issues is a table of issue types - basically a value list).

I understand how to create this, but still not sure how to report on it.

I also require to see the values on a dashboard layout.

[comment]

For example, one client may have only Anxiety issues, and another may have Anxiety and Depression. Therefore, under Anxiety, you would expect to see 2 and Depression 1.

 

Do you mean clients? Two clients under Anxiety,  one client under Depression? Are you sure this is what you want? Because the total number of clients is not 3, contrary to what this type of report would suggest.

 

 

I understand how to create this, but still not sure how to report on it.

 

Well, to take the simplest example, given these records in the ClientIssues table:

 

ID   Client  Issue

1   Adam    Anxiety

2   Betty   Anxiety

3   Betty   Depression

 

you could sort them by Issue, and using a summary field defined as as Count of  [iD], placed in a sub-summary-by-Issue part, you would get:

 

Anxiety:    2

1   Adam    Anxiety

2   Betty   Anxiety

Depression: 1

3   Betty   Depression

 

and deleting the body part from the layout would leave you with:

 

Anxiety:    2

Depression: 1

 

And if each record in Clients counts its consultations (or its consultations in a given period), then you can include those totals in this report too (again, grossly overstated in grand total).

 

 

Alternatively, you could get the same numbers in the Issues table, using Count ( ClientIssues::Issue ). That would probably be more fitting for a "live" dashboard count.

 

 

---

Of course,in a real implementation, the ClientIssues table would hold ClientID and IssueID fields - not names.

[Matt Malyschko]

Thanks comment it's starting to make a bit of sense.

I think with this I could get the basic working. But then how would I break this down further to show separation between agency, employment type and referral cause. Can this just be done by using GetSummary on the clients table for the given break fields? Not exactly sure how it would work?

Thanks again

[Matt Malyschko]

comment,

I am struggling to understand how your solution would work with multiple consultations.

Say for the month of March (1/3/14 - 31/3/14) Adam has 2 consultations and Betty 1.

I would want to see
Anxiety: 3
Depression 1

I think I am missing this part:

And if each record in Clients counts its consultations (or its consultations in a given period), then you can include those totals in this report too (again, grossly overstated in grand total).

 

 

Alternatively, you could get the same numbers in the Issues table, using Count ( ClientIssues::Issue ). That would probably be more fitting for a "live" dashboard count.

 

Are you able to explain this further?

[comment]

 But then how would I break this down further to show separation between agency, employment type and referral cause.

 

IIUC, all of these are attributes of a client - and therefore, by extension, an attribute of a ClientIssue as well. So it's only a matter of sorting and adding more sub-summary parts, all using the same summary field.

 

 

Say for the month of March (1/3/14 - 31/3/14) Adam has 2 consultations and Betty 1.

I would want to see

Anxiety: 3

Depression 1

I think I am missing this part:

 

Are you able to explain this further?

 

If you define two global date fields in the Clients table, gStartDate and gEndDate, and add another relationship as:

 

Clients::ClientID = Consultations 2::ClientID

AND

Clients::gStartDate ≤ Consultations 2::Date

AND

Clients::gEndDate ≥ Consultations 2::Date

 

you can then add a calculation field to the ClientIssues table =

Count ( Consultations 2::ConsultationID )

and a summary field to total this field.

 

 

Note that this will work for one month (or any period) at a time only. Comparing different time periods would be much more complex.

[Matt Malyschko]

Note that this will work for one month (or any period) at a time only. Comparing different time periods would be much more complex.

This would hopefully be the end result - comparing month-to-month stats in graphs ... Sigh.

[comment]

That's something you will have to script. There simply aren't enough consultation records to produce such report directly from existing data (since you want to count each consultation as many times as the client has issues).

[Matt Malyschko]

I knew I would probably have to do some work to the records, but didn't know what...  Long night ahead I guess.. lol

[comment]

I knew I would probably have to do some work to the records

 

I don't know that you need to do anything to the records as such. You just need to count them, several times, and store the results somewhere you can use them.

 

Two things could be handy here: one is a technique called "Fast Summaries" and the other - provided your record count is reasonable - is the ExecuteSQL() function.

[Matt Malyschko]

I feel like this would probably be easier to have a script perform a search etc, gather all the required values and enter the values. Ie; performing what was previously done manually. I was hoping I'd be able to get it completely automated and updated live but I am struggling to get it working as intended...

[Matt Malyschko]

I've attached my latest workings.

You will find the report I'm trying to make on Underlying Issues Breakdown - this is based on the ClientsIssues TO

When you navigate to this layout, I would use Find to search for ">0" in Client Consultations in the Body, then sort by Agency, Employment Type, Referral Cause and then Issue.

This gives an accurate breakdown per the Issues, but the summary groupings I cannot figure out... I have created 3 consultations, 2 for 1 client who has only anxiety, and then 1 for another client who has anxiety and depression, but the Referral Cause sub-summary is counting these distinct records, but it needs to count unique client's under that section.

I've seen the use of ExecuteSQL function to gather unique values in the past, but have never implemented it and am struggling with how it would help with this and where to put it, etc.

*sigh*

Reports.zip

[comment]

I am afraid I cannot open .fmp12 files ATM.