Forum Discussion

ChristineK's avatar
ChristineK
Qrew Assistant Captain
16 hours ago

Modifying 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!

reports

2 Replies

  • hueyal's avatar
    hueyal
    Qrew Cadet
    2 hours ago

    Below is a formula that may help with determining 'new' customers in a fiscal year.  Based on your setup, you'll probably need to tweak some. Hopefully, you can leverage based on your environment.

    This is based on two tables in my app:  Customers and Visits (where customers have many visits).  

    - Create application variable to set the first month of the fiscal year ([Fiscal Year Start Month]
    - Add Formula - text field to Visits table (code is below and I've broken it up to help explain the different components (Field 56 is Related Customer in Visits table)

    If visitsBeforeThisVisit is 0, that visit is the first visit of the fiscal year.  "New" is returned if visitsBeforeThisVisit is 0.

    //Calculates Fiscal Year Start Based on Vist Date
var date FYStart = If(Month( ToDate([Visit Start Date & Time]) ) < ToNumber([Fiscal Year Start Month]),
Date(Year(ToDate([Visit Start Date & Time]))-1,ToNumber([Fiscal Year Start Month]),1),
Date(Year(ToDate([Visit Start Date & Time])),ToNumber([Fiscal Year Start Month]),1));

//Components of Query
var text qryCustomer = "{56.EX.'" & [Pet - Related Customer] & "'}";
var text qryFYStart = "{6.OAF.'" & $FYStart & "'}";
var text qryCurrentVisit = "{6.BF.'" & [Visit Start Date & Time] & "'}";

//Retrieves Number of Visits in FY before the record's visit start date
var number visitsBeforeThisVisit = Size(GetRecords($qryCustomer & "AND" & $qryCurrentVisit & "AND" & $qryFYStart));

//Returns New if visit is the first visit of the fiscal year for the customer
If($visitsBeforeThisVisit = 0, "New","")

     

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    10 hours ago

    I 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.  

    .