User friendly date effective association collection

Recently I got a request to provide a function to classify a team (customer specific date effective classification). The goal was to make the process simple and user friendly. My suggested solution was accepted. I want to share it, so probably it can be re-used.

Problem description
Several classifications are defined. Only one classification can be assigned to a team at any given time. The current classification is presented in Teams form. If current classification is not defined then Classification field is empty. The user can simply set new classification by selecting a new value in lookup. If new classification is set, previous classification is made ineffective. Future classification is taken into consideration for new classification validity period. The user can manually update classifications effective dates in Party form.

Steps to reproduce
1. Define classifications
To simplify it - let's assume that "Class1", "Class2", "Class3" are defined

2. Add "Class1" in Party form for new "Team 1"
Effective (ValidFrom) set equal to (today - 7 days), Expiration (ValidTo) left without changes.

3. Add "Class3" as future period in Party form for the "Team 1"
Effective (ValidFrom) is equal to (today + 7 days), Expiration (ValidTo) left without changes. Click Yes in dialog and note that Expiration (ValidTo) is updated on "Class1"

4. Open "Team 1" and make sure that current classification is "Class1"

5. Set current classification to "Class2" for "Team 1"

4. Three classifications are defined in Party form
"Class1" valid from (today - 7 days) to (today - 1 day)
"Class2" valid from (today) to (today + 6 days)
"Class3" valid from (today + 7 days) to (max date)

Hints
Write down all possible periods to design the algorithm.


Solution
As for data model 3 tables are required for date effective association collection:
OMTeam - standard table.
ClassificationTable - new table with 2 fields Classification and Description.
TeamClassification - new table with foreign keys Team, Classification and date effectiveness enabled.

As for logic there are 2 main methods:
- when new value is set
- when value is set to empty

When the user sets a new classification value the following should happen:

Full method code is below.

public static void createOrUpdate (RefRecId _team, RefRecId _classification) { TeamClassification teamClassificationCurrent, teamClassificationFuture; date currentDate, maximumDate; currentDate = systemDateGet(); maximumDate = maxDate(); if (_team && _classification) { ttsBegin; // select current record select forUpdate teamClassificationCurrent where teamClassificationCurrent.Team == _team; // case 1. current record exists if (teamClassificationCurrent) { // new association value was defined if (teamClassificationCurrent.Classification != _classification) { // case 1.1 current record ValidFrom is today -> correct value if (teamClassificationCurrent.ValidFrom == currentDate) { teamClassificationCurrent.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction); } // case 1.2 current record ValidFrom is before today -> create new period else { teamClassificationCurrent.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod); } teamClassificationCurrent.Classification = _classification; teamClassificationCurrent.update(); } } // case 2. no current record else { // create new current record teamClassificationCurrent.initValue(); teamClassificationCurrent.Team = _team; teamClassificationCurrent.Classification = _classification; teamClassificationCurrent.ValidFrom = currentDate; select validTimeState(currentDate, maximumDate) ValidFrom, RecId from teamClassificationFuture order by ValidFrom where teamClassificationFuture.Team == _team && teamClassificationFuture.ValidFrom > currentDate; // case 2.1 future period exists if (teamClassificationFuture) { teamClassificationCurrent.ValidTo = teamClassificationFuture.ValidFrom - 1; } // case 2.2 no future record else { teamClassificationCurrent.ValidTo = maximumDate; } teamClassificationCurrent.insert(); } ttsCommit; } }

When the user sets classification to empty value the following logic is used:

public static void deleteOrRemove(RefRecId _team) { TeamClassification teamClassificationCurrent; date currentDate; currentDate = systemDateGet(); ttsBegin; select forUpdate teamClassificationCurrent where teamClassificationCurrent.Team == _team; if (teamClassificationCurrent.RecId) { // case 1. current record ValidFrom is today -> delete record if (teamClassificationCurrent.ValidFrom == currentDate) { teamClassificationCurrent.delete(); } // case 2. current record ValidFrom is before today -> set new validity else { teamClassificationCurrent.ValidTo = currentDate - 1; teamClassificationCurrent.update(); } } ttsCommit; }

As for form modifications there are several solutions, however I selected unbound ReferenceGroup control in OMTeam form to display and modify current classification. active and selectionChanged methods on OMTeam datasource must be overwritten as well as modified method on control. Find more details in xpo attached.