I have a requirement to report on the status of cases based on a chosen date. i.e. what status each case was at on say October 1st 2019 - how many were at each status.
Seeing as we cannot access the history of values to report on, we have split out the statuses into another data type, then have a new object to hold a history of status changes. One new record is created in this object every time the status of the case is changed. (One to Many relationship from Case to Case Statuses).
For the report I need to produce, let’s say the user wants to see what the status of cases were on 1st October 2019, and a particular case hasn’t had its status changed on that day, but does have a record for the previous day. How can I show that the case status on the 1st Oct is whatever the previous status change was (i.e. the previous record in the Case Statuses object)?
We’ve done some pretty convoluted logic with composites and subsets etc to do what should be simple in the past, but not had to do this before so wondering if anyone out there has a solution?.
Thanks,
Chris
Hi Chris, Just reading your case scenario through and will reply when I have an answer for you.
MP
Hi Chris, please confirm that I have interpreted your requirement correctly:
Say for example Case1234 was created on October 1st and it’s status changed from New (Upon creation) to Pending on October 4th, then changed to Under Review on October 7th then no further changes until it’s changed to Closed on October 12th.
If the report is run in say December to see what the status of cases was on say October 1st, the Status for Case1234 should be New.
If the report was then run to show statuses as of October 3rd, the status of Case1234 should still be New?
If it was run for October 4th it should be Pending and remain on that status for all days up until October 7th. If you ran it on October 7th it then changes to Under Review if you run the report.
Is my understanding of what you are trying to achieve correct?
Regards - Mark
Hi Again Chris,
If my understanding of what you are trying to achieve is correct you could use a solution like the one I have screenshotted here. If my understanding is correct let me know and I can elaborate fully.
MP
Hi Mark,
Not sure that’s quite what I’m after. I’ll try to elaborate…
Cases change status frequently, but I need to know how many cases were New, Active, Pended, Awaiting Member info, closed etc. on a particular date.
The “show history” on a field is fine for viewing individual cases, but if a manager wants to go in and see how many cases were at each status on a particular date in a summary report, it is more challenging - hence the separate object to store that info. The challenge is that not every case will have a status change every day, so I need it to include the last status that every case has - sort of "select most recent Status where record created date is less than or equal to ".
Many thanks,
Chris
Hi Chris,
I would be tempted to start with something like this (will probably involve using templates for your report).
For each case, do a search for audit records with date created <= the date you want. Sort on date created descending and then on your list display just one row and no paging.
I have used a similar scheme before - successfully, but of course it depends on your environment.
Richard
Hi Guys, my method is a little more rudimentary but would give you what I think you are looking for.
What I have shown above is the view of an object that records case status and the date that status changed. A new record is created each time a case is advanced or regressed, capturing the date that event took place.
So for each case there will be a series of child records saved detailing the case progress, it’s revised status, and the date it took place. Once set up it just runs on it’s own, requires no maintenance and will leave you with Case data you could report out on in a number of ways…
I guess it boils down to which approach you prefer. If you want to know more about my approach please ask.
MP
And for records that have no status change on any given day you could have a rule to capture the Status record anyway, so if a case remains on a status for say, 3 days, there will be a date record for each of those days showing the unchanged status. There are almost certainly more clever ways of achieving your end result, but it sounds like you have already explored most of those.
MP