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?
