Forum Discussion

shashidhar joliholi's avatar
shashidhar joliholi
Copper Contributor
Feb 27, 2025

Entra ID expressions for attribute mapping

Hi All,

we have the following requirement. 

  1. if [StatusEndEmploymentDate] is null or if its grater than today's date and city value is present the user should move to repective OU
  2. if [StatusEndEmploymentDate] is less than today's date than user should move to the staging OU. 

we have tried following query but there is no luck. need your help to achieve the requirement. 

Switch([StatusEndEmploymentDate],Switch([City],"OU=Users,DC=abc,DC=com", "Amsterdam", "OU=Users,OU=Amsterdam,DC=abc,DC=com", "Antwerp", "OU=Users,OU=Antwerp,DC=abc,DC=com", "Bengaluru", "OU=Users,OU=Bengaluru,DC=abc,DC=com", "Copenhagen", "OU=Users,OU=Copenhagen,DC=abc,DC=com"),IIF(DateDiff("d", Now(), [StatusEndEmploymentDate])>"-1",Switch([City],"OU=Users,OU=IAM,DC=abc,DC=com","Amsterdam","OU=Users,OU=Amsterdam,DC=abc,DC=com","Antwerp","OU=Users,OU=Antwerp,DC=abc,DC=com","Bengaluru","OU=Users,OU=Bengaluru,DC=abc,DC=com","Copenhagen"))

Active Directory
Apps
Azure Active Directory (AAD)
Identity Management
microsoft entra
  • LainRobertson's avatar
    LainRobertson
    Silver Contributor
    Feb 28, 2025

    Hi shashidhar joliholi,

     

    My first reply disappeared into the ether, so I'll try once more.

     

    You haven't provided enough information, so I'm going to have to make some assumptions.

     

    First, the context of your post appears to be provisioning from Workday into Active Directory.

    Next, I'm going to assume your staging organisational unit is "OU=Users,OU=IAM,DC=abc,DC=com" - from your second IIF() statement (as you have a different value in the first IIF() statement).

    Lastly, you have stated behaviours for when Now() is less than or greater than the ending date, but not when they're equal. So, I'm assuming that when they're equal, the employee is still active as it's their final day or working rather than their first day of not working.

     

    An additional observation is that you missed providing a value for "Copenhagen" in your second IIF() statement, which is an error.

     

    Original expression

    Switch([StatusEndEmploymentDate],
   Switch([City],
      "OU=Users,DC=abc,DC=com",
      "Amsterdam", "OU=Users,OU=Amsterdam,DC=abc,DC=com",
      "Antwerp", "OU=Users,OU=Antwerp,DC=abc,DC=com",
      "Bengaluru", "OU=Users,OU=Bengaluru,DC=abc,DC=com",
      "Copenhagen", "OU=Users,OU=Copenhagen,DC=abc,DC=com"
   ),
   IIF(DateDiff("d", Now(), [StatusEndEmploymentDate])>"-1",
      Switch([City],
         "OU=Users,OU=IAM,DC=abc,DC=com",
         "Amsterdam", "OU=Users,OU=Amsterdam,DC=abc,DC=com",
         "Antwerp", "OU=Users,OU=Antwerp,DC=abc,DC=com",
         "Bengaluru", "OU=Users,OU=Bengaluru,DC=abc,DC=com",
         "Copenhagen"
      )
   )
)

     

    Revised expression

    IIF(IsNullOrEmpty([city])
   , "OU=Users,OU=IAM,DC=abc,DC=com"
   , IIF(IsNullOrEmpty([StatusEndEmploymentDate])
      , Switch([city]
         , "OU=Users,OU=IAM,DC=abc,DC=com"
         , "Amsterdam", "OU=Users,OU=Amsterdam,DC=abc,DC=com"
         , "Antwerp", "OU=Users,OU=Antwerp,DC=abc,DC=com"
         , "Bengaluru", "OU=Users,OU=Bengaluru,DC=abc,DC=com"
         , "Copenhagen", "OU=Users,OU=Copenhagen,DC=abc,DC=com"
      )
      , IIF(DateDiff("d", CDate([StatusEndEmploymentDate]), Now()) > 0
         , "OU=Users,OU=IAM,DC=abc,DC=com"
         , Switch([city]
            , "OU=Users,OU=IAM,DC=abc,DC=com"
            , "Amsterdam", "OU=Users,OU=Amsterdam,DC=abc,DC=com"
            , "Antwerp", "OU=Users,OU=Antwerp,DC=abc,DC=com"
            , "Bengaluru", "OU=Users,OU=Bengaluru,DC=abc,DC=com"
            , "Copenhagen", "OU=Users,OU=Copenhagen,DC=abc,DC=com"
         )
      )
   )
)

     

    Additional note

    Make sure the datetime values coming from Workday include the time zone offset or you can run into issues as many values are interpreted as UTC if no offset is provided. For example:

     

    2025-02-28T15:45:15+08:00

     

    Cheers,

    Lain

Resources