Qrew LegendI have done some work in that sector that I think you are in.
With another client of mine, they were working with sort of troubled teenagers who get caught up in the justice system and they were trying to get them on a better path,
So using that an an example situation
They had Clients (the teenagers). So that is where they record their name and DOB and other "demographics" like address, contact info and who they live with parents / guardians etc......
Then there were Cases. One Client has many Cases. A case would be the Event that caused them to be "in trouble".
Then they would have Interactions, so one Case had many Interactions, which would be counselling sessions on anger management or getting them to show up for a court date or some other attendance with "the system".
So that might be the same three table setup that you need.
Why don't you contact me directly at mark.shnier@gmail.com and I can give you some free time to look at your app and suggest next steps.
As for the immediate question of how to get the number of unique clients served in a quarter, you can run a summary report of your Interactions, grouped by Client Name and filtering on the dates for the quarter. The bottom of the report will give you the number of "groups" which is the number of unique clients served that quarter.
The next question about the number of new clients each Quarter with a reset for the next calendar year is trickier and might need the use of a "Formula Query". The syntax and logic for can be a bit daunting, so if it needs that and you are in somewhat of a non profit type organization, I can help with that with some free consulting hours.
.
Thank you, Mark!
The reporting demands sound incredibly similar and I wonder if the funding source might be similar or even the same.
I'll definitely reach out and appreciate you! I administrate a (non-profit) program that supports sexual assault survivors through clinical advocacy services for forensic examinations.
Today has gotten away from me, and I'm about to log off to take my kiddo trick-or-treating, but I will absolutely reach out to you and would love a fresh set of eyes and brain!
Also - I was thinking this through a touch more just thinking out loud on this same forum.
If I have:
I am thinking the whole "New this year" formula logic might go something like, (From the Cases table which doesn't exist just yet, and I need to build), "Produce the earliest "Date of Service" related to this Case, that is on or after the First Date of the Fiscal Year". And then, my report is a summary report of all Clients, filtered for the Fiscal Year.
Nope, that still doesn't make sense in my head. Ugh. I want to be able to look back historically, and if that just keeps moving the target to the most current Fiscal Year, then that's no good. This keeps breaking my brain right in half. 😂
Qrew LegendActually, you may be on the right track. Wherever possible I always prefer to have summary fields and relationships and avoid Formula Queries. The syntax is more difficult and they can fail in the future in case where there are too many records. (But when there is not a regular solution, then FQs are always a fall back). But for sure we can get this working one way or the other.
I'm also on duty tonight handing out treats at the door at my daughter's house (and watching what I hope will be the final game of the World Series from here in Toronto!)
WAIT I THINK I'VE GOT IT!!!!
What if I created a parent table on top of all of this. So I have (parents to children):
Now, when I build a master Report record, the only native field it needs is "Fiscal Quarter to be reported"
Then, it rolls up:
It all anchors to the parent table's "Reporting Period", thus I can do a historic look-back/audit. And it also enables it to be dynamic and updated based upon things that change over time.
I'm still not entirely sure how to build it, but I think this might be the starting philosophical approach. Thoughts?
Qrew LegendYes, that is the right structure. I think that the identification of a Case Interaction being the first one for a Quarter can no doubt be done with some crazy Formula Query, but let's first try to muddle our way through with regular relationships.
The Interactions table could use some helper fields so they know the Quarter Identifications for the Current Year. We will also want to know the Quarters for the Previous year, because no doubt in January you need to report on how many New clients you saw last year.
So let's make some fields which will result in a format like 2025-Q1
[Current Year-Q1]
formula is:
var text YYYY = ToText(Year (Today());
var text Q = "Q1"
List("-", $YYYY, $Q)
Then make the other 3 Quarters.
Then repeat for last year
[Last Year Q1]
formula is:
var text YYYY = ToText(Year (Today()-1);
var text Q = "Q1"
List("-", $YYYY, $Q)
Then make a field called [Interaction Year-Q]
var text YYYY = ToText(Year ([Interaction Date]));
var text Q = IF(
Month([Interaction Date]) < =3, "Q1",
Month([Interaction Date]) < =6, "Q2",
Month([Interaction Date]) < =9, "Q3",
Month([Interaction Date]) < =12, "Q4")
List("-", $YYYY, $Q)
Here are your main relationships:
Clients < Cases < Interactions
One Client has many Cases which have Many Interactions
on this relationship
Cases < Interactions
Make a Summary field for [Interaction was in Current Q1?]
Any record where
[Interaction Year-Q] is equal to the value in the field [Current Year-Q1]
Set the field properties to show totals even though it is a check box field.
Do some kind of sanity check on that field and once you believe it works, then duplicate that field for any other quarters you want to track, like maybe 7 more copies fields for each Quarter this current Year and for last year.
Then on the this relationship
Clients < Cases
Summarize those 8 fields again up to Clients with the "Any" but no filter and that will identity Clients which have had Interactions in, respectively, those 8 Quarters. So 8 more Summary fields set to show totals.
You could then, for example, make a simple table report of all Clients who had Interactions in Any of those 8 time periods showing the 8 Any Interaction fields across the columns and then totals at the bottom. You would make normal extra fields on the Client record to calculate if they had any service in the Current Year and Last Year, respectively.
like
[Any Client Interactions Current Year Q1?]
or [Any Client Interactions Current Year Q2?]
or [Any Client Interactions Current Year Q3?]
or [Any Client Interactions Current Year Q4?]
So that setup will give you a report showing the answer to one of your questions:
How many total Clients were served (each Quarter)
and also the formula field will give you # of unique clients served in the year.
I will post back separately with the question about now many New clients were served each Quarter, but I think it's trivial once you have those 8 summary fields built on the Client record.
