ChristineK
Qrew Captain
Joined 3 years ago
User Profile
User Widgets
Contributions
Form requirement nuanced trade-offs with proper relational structures
I have two related questions! Someone suggested using grid edit reports on my forms for a more all-in-one parent-child editable comprehensive form. That's not working at least on my end -- has anyone had grid reports be available within a form? I'm reorganizing another section of my app for a division that hasn't moved over to my current setup yet. In their legacy setup, there is currently a one-form-fits-all, which requires many things based upon the service level they rendered. We're moving to a "Clients", "Cases", and "Service Calls" -- which means I no longer can require things at the Client or Case level based upon the extent of the service call..... is this just a trade off I have to make? I'm concerned people might not go back into the parent record and update with additional information as it's received in this new setup (and hence why the grid edit would honestly be a fabulous option -- bring the Service Call record right onto the master record and edit it all from there).46Views0likes1CommentRe: Absolutely maddening reporting requirements, I think I'm almost there though - help!
Thanks, Jeff! I had been thinking it'd be lovely if that's possible on the summary table level somehow, on the summarized row itself because each will be different, but I suspect it's not. I ultimately will need to bring in the following, and "null" across any of these (all different areas) need to just be filtered out so that null isn't a distinct count: These 4 fields right from the Interactions table 11 fields from rom grandparent Client table: Client age group Race Gender If "other" on gender, the brief detail needs to come down where i can get that detail Special classifications (Several fields: Deaf/HOH, Homeless, Immigrants, LGBTQ, Veterans, Cognitive Disabilities, Limited English Proficiency) 26 Victimization Type fields from the parent "Cases" table, including two fields with a detail39Views0likes0CommentsAbsolutely maddening reporting requirements, I think I'm almost there though - help!
Hi all! A daunting task I've put off forever is here. I need to build some dimensional reporting. Here's what I need to do: Extrapolate the following: Total individual clients served within a quarter, by the specific funding stream (upline from Interactions, Clients may be having multiple cases across multiple funding streams, and the stream is set at the Interaction level) Total individuals new that fiscal quarter for the fiscal year Individual client demographics (feeding from Client table) Individual client case victimization types (feeding from Case table) Individual clients who received any services across 4 service buckets And then, the easy part, just a quick-and-dirty count of services by sub-category (about 30 of them or so) My table structure: Clients --> Cases --> Interactions > [Interactions Parent]: Funding Stream > [Interactions Parent]: Service Line What I have built so far: Using the "Interactions" table ideally as the "Single Source of Truth" and not pivoting to the parent tables if I don't have to Interaction table helper fields: 4 "Flag" fields when a record has a qualifying service which falls into each of the 4 service categories 4 conversion helper fields that converts the above flags to a "Related Case - Related Client ID" Summary Report that counts distinct count across my four "Related Case - Related Client ID" by each of the bucket (pictured below) Issue I'm running into: "Distinct Count" wants to count "null" or "" entries as "1" What's Left to Do: I need to run that same exercise to pull down Client demographics and Case victimization types, but if I can't get the Interactions summary report to stop counting "null", then I don't know this is the right pathway. I also have a helper field that rolls up the "Minimum date of service" last quarter to the Client table, and rolls back down as a lookup on the Interactions table, but that doesn't feel right - I need that per funding stream, and that is just going to be unsustainable and sprawling, so I need to think of a better way I'm a little leery of pipeline building or moving out of my Interactions table to the parent tables, for fear of either 1) developing a sprawling maintenance trap with pipelines, or 2) moving away from the data and baking in a wrong formula. Conversely, I would absolutely love to connect with anyone willing to give me a few free spare minutes! Attachments: Report settings Current sample report with "null" counting out in the last column"182Views0likes5CommentsRe: Modifying an existing app for unusual reporting needs
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): REPORTS, which have basically everything below, and roll up information on the below Clients who have Cases and Interactions about the Cases Now, when I build a master Report record, the only native field it needs is "Fiscal Quarter to be reported" Then, it rolls up: Whether they're "new" that fiscal year, based upon a calculation that looks up the full Fiscal Year's "Interactions" and figures out if the first one for that fiscal year T/F falls within the reported quarter The age group, based upon the Date of Birth to the Date of First Interaction that Fiscal Quarter All the other demographics which typically don't change All the services that fall within that range 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?44Views0likes2CommentsRe: Modifying an existing app for unusual reporting needs
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: Clients Cases Interactions (or, "dates of service") 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. 😂42Views0likes4CommentsModifying an existing app for unusual reporting needs
Hello all! I started toask this in a subsequent question yesterday (we need the Qrew posts to be editable so we can add to an original post rather than reply/have it potentially get lost!). Thanks to the legend Mark Schnier for some help on a specific formula. Now I'm trying to wrap my head around a bigger challenge. The app I'm fixing has some (what I consider) unusual reporting requirements. We have Clients, who have Cases, and our team of Users have Interactions with the Clients about the Cases. Presently, the app is structured with only two tables for those three things -- "Client/Case Files", and "Interactions". So question #1 is -- as I add in a 3rd table, I'd love any words of wisdom about correcting any legacy data into the 3-table format. Or do I just let it go and course-correct forward? Question 2 is around building logic so as we can get the nuanced reporting we need. Each Quarter, we have to report out on 1. How many total Clients were served, and 2. How many Clients were NEW that quarter. Then, at the beginning of a new fiscal year, all legacy clients are counted NEW again. So each time a legacy Client presents for the first time in a new Fiscal Year, I need to be able to report out when they're "new". That could happen any time within the Fiscal Year, and I'm really struggling to wrap my brain around how to build that specifically. Lastly - and the form field that Mark helped me build, which I'll need to wrap my head around within the context above -- is that we need to report out the demographics on those. As those demographics are generally client-reported to us, they can change -- so I need a mechanism to ensure we're 1) capturing that info, 2) updating that info when the person needs to be reported out again new, and 3) in particular with the demographic of what "age range" someone is in, those things of course change. So to that, ideally I'd have a way to also capture it as it is, in one particular point in time -- so that if we ever were to need to audit a past report, we can see how the reporting was then; and be able to also pull the present reporting out as it is today. Any help is welcome! I feel like the entire conundrum sort of bends my brain in half, and I don't know if I'm just being a dunce, or if this is truly just unusual reporting parameters! I hope this makes sense - and thanks in advance for any guidance!284Views0likes8CommentsRe: Help me write a formula that is dynamic
Side note: Qrew forum request -- the ability to edit posts haha. I'm going to add on some additional things and make this already-dense request a little more so! :-D Here is one other related thing I'm trying to do -- which is not directly related to the above, but does affect it, so it's worth mentioning. For grant reporting, I also need to be able to capture "New This Year", and "New This Quarter". Just to make things EXTRA fun, everyone is considered "New" at the beginning of the FY, even when they're legacy clients...... I see in a competitor database that was developed specifically for these kinds of grants, there are tick boxes for "New this quarter" and "New this year" right in a master Client table -- but without being able to see how those are behaving, I'm curious how I might build something like that within my QB app. Maybe, it's a report on a dashboard that somehow counts "Clients with any Service records this quarter, where the earliest service record since the last fiscal year falls within this quarter"? These are the reporting requirements that turn my brain inside out. 🙃🤯32Views0likes1CommentHelp me write a formula that is dynamic
Me again. This is the perfectionist in me. I'd love suggestions on if it's possible (and how) to write a formula that calculates a person's age based upon DOB field, converts it into a category ("0-12", "13-17", etc.), AND ALSO where that field can apply the reference date based upon a report filter. So, if in the future I need to see a report and the age needs to convert to the date where it would be in the beginning of that date range (or even more ideally, if it can reference the first date of service within the filtered time range on a connected table and apply the rule as it would have been in that time frame), that way I can always see what we would have received as a report-out at that point in time if I needed to go back and spot-check something. I know that sounds terribly convoluted. It's for grants reporting, and honestly these are some of the items that get at some of the most obnoxious calculations we're asked to report out on...... If I can't make it perfect, no big deal, but I thought I'd see what you all can conjure up. Example: Person is 17 at the time they come to receive services for the first time. The dynamic field should be able to reference my "Services" table, see when the first Service record within the filtered quarter is, and apply the age range. The following quarter, we'd actually report them out again, but applied to the new quarter -- and let's say they turned 18, so for THAT filter, I want it to now produce the updated age group. Ultimately, accuracy within my present reporting period is of chief importance, but ideally, the ability to pull up historic records and check would really be fantastic.128Views0likes4Comments