Skip to Content
Technical Articles
Author's profile photo Xavier Le Garrec

MDF promotion design in Compensation Worksheets

Please find the presentation here as well as the demo and explanations below :


For more information on how to build complex mdf objects, please see this post :


All the best,


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Ka Shun Wong
      Ka Shun Wong

      This is a great post to show how to use:

      1. edit the pop-up link to MDF Portlet from People Profile for promotion (so as to mass-upload initially before sending worksheet to planning manager)
      2. retrieve data from that MDF portlet and have data reloaded after saving the worksheet
      3. have user-level promotion effective date (as standard EC-COMP promotion can only have 1 job info effective date per template; while standard lets you define user-level effective date on pay component (including Non-Recurring or Recurring Pay Component) record)


      Just several questions:

      1. In the MDF portlet, it shows the current Job Code and Grade, is it referring the same date as worksheet effective date? or it always takes current (aka as of Today value) date? (I think this is not an issue for annual salary review process, but wanna know the design behind so as to see if the same design can apply to other scenarios)
      2. Is your salary range/benchmark deriving from the fields in MDF (ie. Job Code and Grade)? Will be impact the final salary range/benchmark display?


      Thanks for your contribution again and look forward to seeing your reply.




      Author's profile photo Xavier Le Garrec
      Xavier Le Garrec
      Blog Post Author

      Hi Carson,

      1. The MDF takes the current value from Employee Central (not the template effective date).
      2. Yes the fields finalJobCode and other standard promotion fields in Design worksheets are mapped to the MDF fields, therefore the Final Benchmarks (Compa Ratio and Range Penetration) update accordingly. You can see it in the recording (standard final benchmarks use the coloring feature and they get updated correctly in the recording).

      All the best,


      Author's profile photo Ka Shun Wong
      Ka Shun Wong

      Thanks Xavier for the confirmation.

      I have another quick question which is not relevant to this blog topic that I cannot figure out how to check a date column is blank in the column formula.

      In my situation. I need to check a date column for suppressing statement generation.

      Wonder if you have any hint for this.

      Thanks and Happy New Year 2021.


      Author's profile photo Xavier Le Garrec
      Xavier Le Garrec
      Blog Post Author

      Hi Carson,

      I would try this :

      if(date_column != "",false,true) 

      or if your date isn't in a date column I would also try

      if(toDate('date_column','MM/dd/yyyy') != "",false,true)


      Happy new year



      Author's profile photo Ka Shun Wong
      Ka Shun Wong

      Thanks Xavier.

      I further verified and confirmed below statement should be used:

      if(date_column != “”,"false","true") 


      Since if the date_column is blank, toDate(date_column,'MM/dd/yyyy') will result N/A, which means system failed to evaluate. However, we cannot set:

      if(toDate(‘date_column’,’MM/dd/yyyy’) != “N/A”,"false","true")

      which will always result N/A in my testing. Probably once the system failed to evaluate in toDate() function, it stopped further checking process.

      Author's profile photo Ka Shun Wong
      Ka Shun Wong

      And it is interesting (stupid):

      Both custom* columns are in string type, and customFirstSalaryReviewDate can be blank while customCutoffDate contains a date value derived from lookup table.

      My requirement is:

      - if customFirstSalaryReviewDate is blank, return 'TRUE'

      - elseif customFirstSalaryReviewDate <= customCutoff, return 'TRUE'

      - else return 'FALSE'


      I have built below formula in a string column:



      Strange result is obtained:

      1. if customFirstSalaryReviewDate is blank, it returns 'N/A' (means system failed to evaluate)

      2. if customFirstSalaryReviewDate is not blank, it can returns 'TRUE' or 'FALSE'


      I tried to split my formula into 2:

      1. if(customFirstSalaryReviewDate='','TRUE','FALSE')

      > this results properly, 'TRUE' if blank

      2. if(dateDiff(toDate(customFirstSalaryReviewDate,"MM/dd/yyyy"),toDate(customCutoffDate,"MM/dd/yyyy"))<=0,'FALSE','TRUE')

      > this results properly for customFirstSalaryReviewDate is not blank case


      Based on above behavior, seems the if() function will try to compute all condition checks first before giving result.

      Now I need to have 4 custom columns to compute:

      1. string column for customFirstSalaryReviewDate

      2. string column to check if customFirstSalaryReviewDate is blank (result TRUE or FALSE)

      3. string column to check the dateDiff() between customFirstSalaryReviewDate and another fixed date (it shows numbers if customFirstSalaryReviewDate is not blank, or else shows 'N/A'

      4. string column to check if column 2 is TRUE, then result TRUE, else check column 3 to show TRUE if the number is positive, else show FALSE if the number is negative