Apex Code Development You need to sign in to do that
Don't have an account?
BlairB Reporting on a picklist field from the most recent record
Hello,
I am hoping I can get a more experienced programmer/coder's help on an issue I'm having.
I am relatively new to the Salesforce platform but have been tasked with getting a functional database up and running for the social services agency where I work. We offer residential services for our clients and we have a great deal of data we want to track about each client and about each program and that program's residences. We have many many custom objects and many many relationships between objects.
One of the most basic things we do every week is a roster of each program and its residences and some information about each client currently in each program/residence. After a lot of time/work I figured out a way to get a current roster for each residence and each program (this is tricky as clients can move across programs or to a different residence within the same program). One of the items we report on weekly in that roster is the client's current level of skills and privileges. This level is set weekly at case management meetings. The way the database is set up currently, we have our clients as Contacts and the Contact is the master in the master-detail relationship with many other objects. Another object that we have is "Case Management" which is the detail in the master-detail relationship with Contact. Within Case Management, there is an object for the weekly Team Meetings. This object is the detail in a master-detail relationship with Case Management. So it's related to Contact, but not directly.
We do our reporting on the roster every week from the Enrollment object which is a junction object between Contact and Program. I want to be able to include the most recent skills and privileges level on the roster (from the Team Meetings object) but I cannot figure out how to get that onto the report.
What would be nice is to always have a field that updates with the most recent skills/privileges level on either the Case Management object or the Contact's main detail page. I can't sort out how to do this. Every week a new record is created (in Team Meetings) with information from that week's meeting. I don't know how to get a field to update with the most recent skills/privileges level. This field is a picklist (as there are a standard set of levels), so roll-up summary doesn't work. So, my first issue is how to somehow parse out from the many records a client will have what the most recent level is and then how to reflect that into a report.
This seems like it may require some programming/coding knowledge that I do not have, so I am really hoping someone in this forum can help me out! I know this is a long explanation. Please let me know if there's anything I can clarify.
Hello BlairB,
I understand there is following structure:
Contacts--------------------Enrollments(Junction)---------------Program
|master-detail
Case Management
|Master-detail
Weekly team meetings
Now you wish to report on Enrollments with contacts anf weekly team meetings. For this please create a custom report type with the flow of objects like Enrollments->Contacts->Case management->Weekly team meetings. Create report on this report type and filter it with the date to get the most recent skills and privileges level.
For custom report types please refer following links:
https://help.salesforce.com/HTViewHelpDoc?id=reports_defining_report_types.htm&language=en_US
https://help.salesforce.com/HTViewHelpDoc?id=reports_report_type_guidelines.htm&language=en_US
Let me know if you have any questions.
Hi,
Thank you for this information. The flow of that looks right. Enrollments is the junction object between Programs and Individuals.
Case Management is the detail in a master-detail with Individuals and Team Meetings is the detail in a master-detail relationship with Case Management.
I'm running into an issue, however. When I try to create a report where Enrollments is the primary object, the only other option for creating another relationship is "Activities." I'm not sure why that is. If I start with Individuals, I can either do Enrollments or I can do Case Management and then Team Meetings. I tried starting with Programs, but with only 4 object relationships allowed, I can't get there and I always dead end with Enrollments since it only has Activities as its object relationship.
Any idea what's going on? It seems like a relatively simple thing I want to do but something in these relationships is preventing me from getting there.
Thank you for your help!
Hello,
As explained over the message please establish a lookup between Individual (Contacts) and enrollment so that you would be able to create custom report type with Enrollment as primary objects.