January 24, 2014

Calculating Earliest Start Date and Latest End Date using MVL & MVF

Recently I had a Requirement where in I have a Parent BC and a Child BC. Child BC has records with "Start Date" and "End Date". Each record has different start and end dates. Parent BC has Earliest Start Date and Latest End Date fields.

What I had to do was, find the least of the Start Dates and max of the End Date from the child and populate them at Parent BCs Earliest Start Date and Latest End Date fields.

For determining Earliest Start Date and Latest End Date, as always first thought that came into my mind was achieving this using Scripting :) But I found a better configuration.

Here is what you have to do.

In the Parent BC:

Create a MVL "Parent Child MVL" between Parent and Child BC. (Link between these BCs must have been there)

Create 2 MVFs =======>

Name: Start Date MVF
MVL: Parent Child MVL
Destination Field: Start Date
Type: DTYPE_DATE

Name: End Date MVF
MVL: Parent Child MVL
Destination Field: End Date
Type: DTYPE_DATE

Create 2 calculated fields =======>

Name: Earliest Start Date Calc
Calculated Value: Min ([Start Date MVF])
Type: DTYPE_DATE

Name: Latest End Date Calc
Calculated Value: Min ([End Date MVF])
Type: DTYPE_DATE

You are ready! You can add a small code to read these Calc fields and populate the Parent BC fields Earliest Start Date and Latest End Date! (there might be a better way though)

Siebel is awesome :) 

No comments:

Post a Comment