I'm working on education solution that requires some changes.
Our students (vocational school) during theirs education are hired for practical learning. Student during education time can have one or more employers, but only one at the time. Every employer can have many students hired. Every contract have information about when contrat has started and when it should expire (in case of present employers) or when it had ended (in case of past employers).
I've got tables Students, Contracts (which is join table) and Employers.
Students –< Contracts >– Employers.
Students::id = Contracts::id_Student
Employers::id = Contracts::id_Employers
But most of the work is done considering the latest employer (printing mails to them, creating letters or other info).
In Form Student Layout need to have adress, name of latest employer displayed. So far i did this by putting filtered portal based on join table on layout.
Get ( CurrentDate ) > Contracts::endDateOfContract;
I also have putted same filtered portal on letters for print.
And that worked fine during the whole academic year, as solution was build to work on current year. Now the academic year has ended and I need to rebuild it.
Now in database I have graduates and still need info about the latest employer nad obiviously current way doesn't working as contracts has ended.
I was thinking about two solutions.
1) Add another relationship
Students —<The Latest Contract >— Employers.
Students::id = The Latest Contract::id_Students and
Students::LatestContract (GLOBAL that contains value of 1) = The Latest Contract::ifLatest (Boolean, 1 if employer is the latest).
I could go and manually mark which employer is latest, but it seems to be pointless as I'm sure that it can be done with FM automatically.
What I need is to either calc or script that can evaluate if Employer is the latest.
Is there any way to create calculation in join table that can compere all endDateOfContract for specific Student and take value 1 for only the latest one (which endDateOfContract is the biggest)? Or is it job for script?
2) Add different relationship
Students >– Employers
Students::latestEmployerId = Employers::id
And in this point I need calculation or script to check all records from join table and then put id of the latest employer.
Which way is better or simpler to create? Or maybe there is even simpler way to achieve what I need.
Any help would be appriciated, and if my explanation is unclear ar blurry in some way, please tell me and I will add additional info.