I would like to be able to create a "Persistence Report" (a report that shows whether or not a student was enrolled from one fall semester to the next fall semester), but I'm having trouble with building a field that I think may be important to create the report.
The Field
I would like to create a formula field on a custom Object (Academic Semesters) that returns whether or not that Academic Semester record is a student's first Fall Semester, second Fall Semester, etc. (the Spring semester, for all intents and purposes, is relatively irrelevant).
Currently, here are a couple of a possible custom fields that may help to create the above formula field:
School__c | lookup - College account records
Period__c | picklist - Fall, Winter, Spring, Summer
Academic_Year_Start_Date__c | Date - Used in the next formula
Year_Period__c | Formula (Number) - Returns a value that's YYYY.PP, where YYYY is the 4 digit year code and PP is the semester (PP = .01 if Period = Fall, .02 if Period = Winter, .03 if Period = Spring, .04 if Period = Summer); in practice, this is used as an "ordering" number to help us sort a student's semesters (imagine: Fall 2013 = 2013.01, Spring 2014 = 2013.03, Fall 2014 = 2014.01, Spring 2015 = 2014.03, etc.); see below
As you can see, it sorts everything already in a numerical fashion, but I just want to be able to label that Academic Semester record as "Fall 1", "Fall 2", etc.
I would like to create a field that recognizes that the Academic Semester record is a Fall record AND that it's the student's 1st fall semester (possibly based on the Year/Period sorting? this is where I'm stuck).
The End Goal
Ideally, I would like to have these as Lookup fields on the Contact page, where the field "Fall_1__c" is populated by the Academic Semester Record that's tagged as "Fall 1", etc.
Does this make any sense? This is mission critical to our non-profit, as it lets us pave the way for more direct reports (we export everything to Excel as of now and do a lot of things by hand) and any help here would be VERY VERY appreciated!
2 个回答
I think you need some Apex Code here. I believe that you have Academic Semester Records related to the Contact object and you have to return a single record that meet certain criteria. Then, you need Apex to actually update the Contact record with the record that's returned from from that query.
