how do we apply transform based attribute in JDBC connection application
Can you explain your use case further? What are you trying to do? Is this for a create profile? Or something else?
Hi @vkashat ,
We have two different fields showing end dates for Employees/Contractors. We have created a transform to identify the correct end date from Employees and Contractors & we are using this transform to update the cccstopdate in AD connector whereas I need assistance on how to update the stop date based on transform without referring to Identity attribute for a JDBC application
What’s the reason you can’t use an Identity Attribute?
Hi @vkashat ,
We are updating end date of contractor/Employee in two different identity attributes for other uses
I see, but you could still use two new identity attributes and as long as you label them properly, you’ll avoid confusion. For example, call one JDBC Contractor End Date (jdbcContractorEndDate) and another JDBC Employee End Date (jdbcEmployeeEndDate) and use them specifically for this use case.
To add, you could possibly add transform logic to your create policy, but you won’t be able to attribute sync the data that way. You can only achieve that by creating an identity attribute and putting the mapping in the create policy.
in JDBC app, we have only one attribute
Then it would just be a JDBC End Date attribute and you could use a first valid transform or use a static transform with Velocity template to select the date you need.
How can I use the transform in the create and modify section of the JDBC app ?
if(getAttributeRequestValue(account, “JNJSTOPDATE”) != null)
{
statement = *connection*.prepareStatement("UPDATE JJEDS_ADMIN.PERSN_ALL SET JNJSTOPDATE = ? WHERE WWID = ?");
String *inputTermDate* = getAttributeRequestValue(account, "JNJSTOPDATE");
String *formattedTermDate* = changeDateformat(inputTermDate);
*statement*.setString(1, formattedTermDate);
*statement*.setString(2, identity);
*statement*.executeUpdate();
}
Hello,
From your discussions, I think you are using the JDBC provisioning rules to update the attributes in JDBC target is totally fine and valid option that you have.
But, before passing the details into the JDBC Provisioning Rule, I think you want to pass the correct “inputTermDate“ depending upon whether the user is an Employee or Contractor.
In SailPoint ISC, there is no such thing as “Modify Account“ profile the way we have profiles for Create Account, Enable Account & Disable Account operations respectively.
Hence, to suffice your objective, you have to use a “SINGLE IDENTITY ATTRIBUTE“ inside the identity profile name it as “JDBC End Date“. Now, develop a static transform for identity attribute named as “JDBC End Date“ and use Velocity Syntax in order to build complex IF ELSE logic in order to populate the correct endDate based on Employee_Type of the user. In this way, this single attribute of “JDBC End Date“ will store correct values for employee/contractors. Then, map this new identity attribute of ““JDBC End Date““ to endDate attribute of JDBC Application in the “Create Profile“ section and then, go to Attribute SYNC, ENABLE THE ATTRIBUTE SYNC for this attribute.
Example of STATIC TRANSFORM, i am talking about is as follows.
{
"name": "Static Transform for JobEndDate",
"type": "static",
"attributes": {
"EndDateForEmployees": {
"attributes": {
"values": [
{
"attributes": {
"attributeName": "EmployeeEndDate",
"sourceName": "AuthzSource"
},
"type": "accountAttribute"
},
{
"attributes": {
"value": "NOT GIVEN"
},
"type": "static"
}
]
},
"type": "firstValid"
},
"EndDateForConTractor": {
"attributes": {
"values": [
{
"attributes": {
"attributeName": "ContractorEndDate",
"sourceName": "AuthzSource"
},
"type": "accountAttribute"
},
{
"attributes": {
"value": "NOT GIVEN"
},
"type": "static"
}
]
},
"type": "firstValid"
},
"EmployeeType": {
"attributes": {
"values": [
{
"attributes": {
"attributeName": "EmployeeType",
"sourceName": "AuthzSource"
},
"type": "accountAttribute"
},
{
"attributes": {
"value": "NOT GIVEN"
},
"type": "static"
}
]
},
"type": "firstValid"
},
"value": "#if($EmployeeType != 'NOT GIVEN' && $EmployeeType != 'Employee')$EndDateForEmployees#{elseif}($EmployeeType != 'NOT GIVEN' && $EmployeeType != 'Contractor')$EndDateForConTractor#{else}#end"
},
"internal": false
}
And, then, you JDBC Provisioning Rule will work as expected.
Hi @rohit_wekhande ,
The below transform is giving the error below:
Error:
There was an exception while calculating the value for this attribute. Error during transformation for attribute: oedStopDate (Transform ID: Delimited Update OEDStopDate) Cause: Error rendering template: #{if}($getEmployeeType == ‘Employee’)$endDate#{elseif}($getEmployeeType == ‘Contingent Worker’)$contractExpirationDate#{else}#{end}
Transform:
“type”: “static”,
"attributes": {
"requiresPeriodicRefresh": "true",
"getEmployeeType": {
"attributes": {
"values": \[
{
"attributes": {
"attributeName": "OU__c",
"sourceName": "Workday Delimited"
},
"type": "accountAttribute"
}
\]
},
"type": "firstValid"
},
"contractExpirationDate": {
"attributes": {
"input": {
"type": "accountAttribute",
"attributes": {
"attributeName": "CONTRACT_END_DATE",
"sourceName": "Workday Delimited"
}
},
"inputFormat": "MM/dd/yyyy",
"outputFormat": "ISO8601"
},
"type": "dateFormat"
},
"endDate": {
"attributes": {
"input": {
"type": "accountAttribute",
"attributes": {
"attributeName": "LAST_DAY_OF_WORK",
"sourceName": "Workday Delimited"
}
},
"inputFormat": "MM/dd/yyyy",
"outputFormat": "ISO8601"
},
"type": "dateFormat"
},
"defaultDate": {
"attributes": {
"input": "12/31/9999",
"inputFormat": "MM/dd/yyyy",
"outputFormat": "ISO8601"
},
"type": "dateFormat"
},
"getRehireCheck": {
"attributes": {
"values": \[
{
"attributes": {
"attributeName": "REHIRE",
"sourceName": "Workday Delimited"
},
"type": "accountAttribute"
},
{
"attributes": {
"value": "null"
},
"type": "static"
}
\]
},
"type": "firstValid"
},
"value": "#{if}($getEmployeeType == 'Employee')$endDate#{elseif}($getEmployeeType == 'Contingent Worker')$contractExpirationDate#{else}#{end}"
},
"internal": **false**
Kindly add the default values to the method getEmployeeType as below.
“getEmployeeType”: {
"attributes": {
"values": \\\[
{
"attributes": {
"attributeName": "OU__c",
"sourceName": "Workday Delimited"
},
"type": "accountAttribute"
},
{
"attributes": {
"value": "NOT GIVEN"
},
"type": "static"
}
\\\]
},
"type": "firstValid"
}
Change the If Else condition in the static value as below.
#{if}($getEmployeeType != ‘NOT GIVEN‘ && $getEmployeeType == ‘Employee’)$endDate#{elseif}($getEmployeeType != ‘NOT GIVEN‘ && $getEmployeeType == ‘Contingent Worker’)$contractExpirationDate#{else}#{end}
Try the above changes. It seems that “$getEmployeeType“ method is also returning EMPTY value due to which exception is getting thrown.
Hi @rohit_wekhande ,
After the below transform code, the Identity refresh is getting failed for the user:
“type”: “static”,
"attributes": {
"requiresPeriodicRefresh": "true",
"getEmployeeType": {
"attributes": {
"values": \[
{
"attributes": {
"attributeName": "OU",
"sourceName": "Workday"
},
"type": "accountAttribute"
},
{
"attributes": {
"value": "NOT GIVEN"
},
"type": "static"
}
\]
},
"type": "firstValid"
},
"contractExpirationDate": {
"attributes": {
"input": {
"type": "accountAttribute",
"attributes": {
"attributeName": "CONTRACT_END_DATE",
"sourceName": "Workday"
}
},
"inputFormat": "MM/dd/yyyy",
"outputFormat": "ISO8601"
},
"type": "dateFormat"
},
"endDate": {
"attributes": {
"input": {
"type": "accountAttribute",
"attributes": {
"attributeName": "LAST_DAY_OF_WORK",
"sourceName": "Workday"
}
},
"inputFormat": "MM/dd/yyyy",
"outputFormat": "ISO8601"
},
"type": "dateFormat"
},
"value": "#{if}($getEmployeeType != 'NOT GIVEN' && $getEmployeeType == 'Employee')$endDate#{elseif}($getEmployeeType != 'NOT GIVEN' && $getEmployeeType == 'Contingent Worker')$contractExpirationDate#{end}"
},
"internal": **false**
Hi @vkashat / @rohit_wekhande ,
I am getting below error for the below transform code:
Error Message:
There was an exception while calculating the value for this attribute. Error during transformation for attribute: oedStopDate (Transform ID: Update OEDStopDate) Cause: Error rendering template: #if($getEmployeeType == ‘Employee’ && $endDate != ‘null’)$endDate#elseif($getEmployeeType == ‘Contingent Worker’ && $contractExpirationDate != ‘null’)$contractExpirationDate#end
Transform Code:
“name”: “Update OEDStopDate”,
"type": "static",
"attributes": {
"requiresPeriodicRefresh": "true",
"getEmployeeType": {
"type": "firstValid",
"attributes": {
"values": \[
{
"attributes": {
"attributeName": "OU",
"sourceName": "Workday"
},
"type": "accountAttribute"
}
\]
}
},
"contractExpirationDate": {
"type": "firstValid",
"attributes": {
"values": \[
{
"type": "dateFormat",
"attributes": {
"input": {
"type": "accountAttribute",
"attributes": {
"attributeName": "CONTRACT_END_DATE",
"sourceName": "Workday"
}
},
"inputFormat": "MM/dd/yyyy",
"outputFormat": "ISO8601"
}
},
{
"type": "static",
"attributes": {
"value": "null"
}
}
\]
}
},
"endDate": {
"type": "firstValid",
"attributes": {
"values": \[
{
"type": "dateFormat",
"attributes": {
"input": {
"type": "accountAttribute",
"attributes": {
"attributeName": "LAST_DAY_OF_WORK",
"sourceName": "Workday"
}
},
"inputFormat": "MM/dd/yyyy",
"outputFormat": "ISO8601"
}
},
{
"type": "static",
"attributes": {
"value": "null"
}
}
\]
}
},
"value": "#if($getEmployeeType == 'Employee' && $endDate != 'null')$endDate#elseif($getEmployeeType == 'Contingent Worker' && $contractExpirationDate != 'null')$contractExpirationDate#end"
},
"internal": **false**
Hello @vsekar7 ,
Kindly provide the exception details once. Also, test the transform in preview and see what is happening there.
Try below code.
{
“name”: “Test Transform”,
“type”: “static”,
“attributes”: {
“requiresPeriodicRefresh”: “true”,
“getEmployeeType”: {
“type”: “firstValid”,
“attributes”: {
“values”: [
{
“attributes”: {
“attributeName”: “OU”,
“sourceName”: “Workday”
},
“type”: “accountAttribute”
}
]
}
},
“contractExpirationDate”: {
“type”: “firstValid”,
“attributes”: {
“values”: [
{
“type”: “dateFormat”,
“attributes”: {
“input”: {
“type”: “accountAttribute”,
“attributes”: {
“attributeName”: “CONTRACT_END_DATE”,
“sourceName”: “Workday”
}
},
“inputFormat”: “MM/dd/yyyy”,
“outputFormat”: “ISO8601”
}
},
{
“type”: “static”,
“attributes”: {
“value”: “NO_VALUE”
}
}
]
}
},
“endDate”: {
“type”: “firstValid”,
“attributes”: {
“values”: [
{
“type”: “dateFormat”,
“attributes”: {
“input”: {
“type”: “accountAttribute”,
“attributes”: {
“attributeName”: “LAST_DAY_OF_WORK”,
“sourceName”: “Workday”
}
},
“inputFormat”: “MM/dd/yyyy”,
“outputFormat”: “ISO8601”
}
},
{
“type”: “static”,
“attributes”: {
“value”: “NO_VALUE”
}
}
]
}
},
“value”: “#if($getEmployeeType == ‘Employee’ && $endDate != ‘NO_VALUE’)$endDate#{elseif}($getEmployeeType == ‘Contingent Worker’ && $contractExpirationDate != ‘NO_VALUE’)$contractExpirationDate#{else}#end”
},
“internal”: false
}
Hi @rohit_wekhande ,
I am still getting the below error in VSCode extension:
Could not evaluate Kenvue Update OEDStopDate: There was an exception while calculating the value for this attribute. Error during transformation for attribute: uid (Transform ID: Kenvue Update OEDStopDate) Cause: Error rendering template: #if($getEmployeeType == ‘Employee’ && $endDate != ‘NO_VALUE’)$endDate#elseif($getEmployeeType == ‘Contingent Worker’ && $contractExpirationDate != ‘NO_VALUE’)$contractExpirationDate#end
Regrds,
Venkat
can the value of “getEmployeeType“ be null/empty? The value for below attribute in WORKDAY source?
{
“attributes”: {
“attributeName”: “OU”,
“sourceName”: “Workday”
},
“type”: “accountAttribute”
}
?
Hi @rohit_wekhande ,
The result of that will be Employee/Contingent Worker. We are using the same condition in another transform where it is working in Production:
“name”: “Update ccStopDate”,
"type": "static",
"attributes": {
"requiresPeriodicRefresh": "true",
"getEmployeeType": {
"attributes": {
"values": \[
{
"attributes": {
"attributeName": "OU",
"sourceName": "Workday"
},
"type": "accountAttribute"
}
\]
},
"type": "firstValid"
},
"contractExpirationDate": {
"attributes": {
"input": {
"type": "accountAttribute",
"attributes": {
"attributeName": "CONTRACT_END_DATE",
"sourceName": "Workday"
}
},
"inputFormat": "MM/dd/yyyy",
"outputFormat": "ISO8601"
},
"type": "dateFormat"
},
"endDate": {
"attributes": {
"input": {
"type": "accountAttribute",
"attributes": {
"attributeName": "LAST_DAY_OF_WORK",
"sourceName": "Workday"
}
},
"inputFormat": "MM/dd/yyyy",
"outputFormat": "ISO8601"
},
"type": "dateFormat"
},
"defaultDate": {
"attributes": {
"input": "12/31/9999",
"inputFormat": "MM/dd/yyyy",
"outputFormat": "ISO8601"
},
"type": "dateFormat"
},
"getRehireCheck": {
"attributes": {
"values": \[
{
"attributes": {
"attributeName": "REHIRE",
"sourceName": "Workday"
},
"type": "accountAttribute"
},
{
"attributes": {
"value": "null"
},
"type": "static"
}
\]
},
"type": "firstValid"
},
"value": "#{if}($getEmployeeType == 'Employee' && $getRehireCheck == '0')$endDate#{elseif}($getEmployeeType == 'Contingent Worker' && $contractExpirationDate != '')$contractExpirationDate#{else}$defaultDate#{end}"
},
"internal": **false**