Import photos using Workday Integration

[DenMagnuson1]

***The instructions below are for the global plugin version of HR Service Management and Workday integration. As I migrate and test with the Scoped App versions, I will provide any necessary updates.***

After running across the thread Import user photo from LDAP into S-N (reloaded) and having just finished getting our Workday integration working, I started wondering if I could pull in employee photos from Workday and associate to their user record and live profile. Borrowing heavily from the LDAP import script, I was finally successful and wanted to share what I did and what's different from the LDAP import.

To accomplish this I needed to modify the following items:

• Workday Web Services - SOAP Message Functions: Get_All_Workers, Get_Effective_Workers, Get_Updated_Workers
• Import table: workday_data_import
• Script Include: workday_worker_xpath_map
• Transform map: Workday_Data_Transform

The first difference to note is that the Workday integration transforms the data to the HR Profile table. This table doesn't have a photo field. I could've added on and then sync it over to the User table, but the HR Profile table doesn't have a need for a new field like that when it can dot walk to the user.photo field. The other difference is that we wanted to make the Live Profile pictures the same too. This meant adding and additional attachment record that was related to the user's profile record, which if is a new user, doesn't exist. The last difference and part that someone smart than me might know the right answer on, is the proper length for the import field. After testing, I didn't set the property com.glide.loader.verify_target_field_size to true like the other article said. I kept running into the row size to large error, but was able to get it to work by making the import field a string with a max length of 256,000. When I looked at the xml data that was being pulled, the size varied based on the different sizes and age of the jpeg images in Workday.

Here's what I did to get it working.

1. Add the line <bsvc:Include_Photo>true</bsvc:Include_Photo> to the three SOAP Message Functions to pull the image xml data.
2. Add a new field to the Import table to receive the xml data. Column label - Image, Column name - u_wd_image, Type - String, Max length - 256,000
3. Add the line colToXPathMap.u_wd_image = "wd:Worker_Data/wd:Photo_Data/wd:Image"; to the Script Include to map where the photo data is kept.
4. Add an onAfter transform map script to the Transform map for the employee data.
5. In Workday Properties, clear the Date since last successful run value so you pull all employee records.
6. Execute the scheduled import. NOTE: Since you are pulling all employee records, the import will take longer then just updates and effective changes.

Here's the onAfter script I'm using:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
   var usr = new GlideRecord('sys_user');
   usr.get(target.user);
   var existingPhoto = new GlideRecord('sys_attachment');
   existingPhoto.addQuery('table_name','ZZ_YYsys_user');
   existingPhoto.addQuery('table_sys_id', usr.sys_id);
   existingPhoto.addQuery('file_name','photo');
   existingPhoto.query();
   //**check if there is a picture on Workday
   if (source.u_wd_image != '') {
   //**if there is no picture for the record in SN
   if (!existingPhoto.next()) {
   //**launch the function to attach the picture
   attachPhoto();
   }
   //**if there is a picture for the record in SN
   else {
   var sysEncodedAttachment = new GlideSysAttachment();
   var binData =   sysEncodedAttachment.getBytes(existingPhoto);
   var EncodedBytes = GlideStringUtil.base64Encode(binData);
   //**verify if the current existing SN-picture for the record does not match the current LDAP picture
   //if it does not match, delete the current SN-picture and launch the funtion to attach the new picture
   if (EncodedBytes != source.u_wd_image) {
   existingPhoto.deleteRecord();
   attachPhoto();
   }
   }
   }
   //**if there is no picture on Workday
   else {
   //**check if there is one on the SN-record and delete it
   if (existingPhoto.next()) {
   existingPhoto.deleteRecord();
   }
   }
   //function to attach a new photo from the Workday to the SN-record
   function attachPhoto(){
   var sysDecodedAttachment = new GlideSysAttachment();
   var DecodedBytes = GlideStringUtil.base64DecodeAsBytes(source.u_wd_image);
   var attID = sysDecodedAttachment.write(usr, 'photo', 'image/jpeg', DecodedBytes);
   var newAttachment = new GlideRecord("sys_attachment");
   newAttachment.addQuery("sys_id", attID);
   newAttachment.query();
   if (newAttachment.next()) {
   newAttachment.table_name = "ZZ_YYsys_user";
   newAttachment.table_sys_id = usr.sys_id;
   newAttachment.content_type = 'image/jpeg';
   newAttachment.update();
   }
   }
   //check if Live Profile exists for user, if not create record
   var liveProfile = new GlideRecord('live_profile');
   liveProfile.addQuery('table', 'sys_user');
   liveProfile.addQuery('type', 'user');
   liveProfile.addQuery('document', usr.sys_id);
   liveProfile.query();
   if(!liveProfile.next()){
   liveProfile.initialize();
   liveProfile.type = 'user';
   liveProfile.table = 'sys_user';
   liveProfile.document = usr.sys_id;
   liveProfile.name = usr.name;
   liveProfile.insert();
   }
   //check for existing Live Profile image
   var existingProfile = new GlideRecord('sys_attachment');
   existingProfile.addQuery('table_name','ZZ_YYlive_profile');
   existingProfile.addQuery('table_sys_id', liveProfile.sys_id);
   existingProfile.addQuery('file_name','photo');
   existingProfile.query();
   //**check if there is a picture on Workday
   if (source.u_wd_image != '') {
   //**if there is no picture for the profile record in SN
   if (!existingProfile.next()) {
   //**launch the function to attach the picture
   attachProfile();
   }
   //**if there is a profile picture for the record in SN
   else {
   var sysEncodedAttachment2 = new GlideSysAttachment();
   var binData2 =   sysEncodedAttachment2.getBytes(existingProfile);
   var EncodedBytes2 = GlideStringUtil.base64Encode(binData2);
   //**verify if the current existing SN-picture for the record does not match the current LDAP picture
   //if it does not match, delete the current SN-picture and launch the funtion to attach the new picture
   if (EncodedBytes2 != source.u_wd_image) {
   existingProfile.deleteRecord();
   attachProfile();
   }
   }
   }
   //**if there is no picture on Workday
   else {
   //**check if there is one on the SN-record and delete it
   if (existingProfile.next()) {
   existingProfile.deleteRecord();
   }
   }
   function attachProfile(){
   var sysDecodedAttachment2 = new GlideSysAttachment();
   var DecodedBytes2 = GlideStringUtil.base64DecodeAsBytes(source.u_wd_image);
   var attID2 = sysDecodedAttachment2.write(liveProfile, 'photo', 'image/jpeg', DecodedBytes2);
   var newAttachment2 = new GlideRecord("sys_attachment");
   newAttachment2.addQuery("sys_id", attID2);
   newAttachment2.query();
   if (newAttachment2.next()) {
   newAttachment2.table_name = "ZZ_YYlive_profile";
   newAttachment2.table_sys_id = liveProfile.sys_id;
   newAttachment2.content_type = 'image/jpeg';
   newAttachment2.update();
   }
   }
})(source, map, log, target);

Hope this can be helpful for someone else. If anyone knows how to do the import field better, please let me know.

Dennis

[Kiel Sanders]

Hi Dennis

Great job!   Thank you for sharing your work with the community so that everyone can experiment with it.   Content sharing like this is what makes the community special.

Kiel

[lisamclaughlin]

Hey Dennis -

Thank you so much for this, this is incredibly helpful!

I have a couple of questions if you (or someone) have the time to evaluate them.

We have a similar goal - we want to pull in user photos from the Workday WSDL & populate them into the User Profile of ServiceNow. I've followed the steps above, but I'm still having some issues getting the Photos to pull from the WSDL we have set up.

I've added the appropriate messages to the SOAP Message functions Get_All_Workers, Get_Effective_Workers, and Get_Updated_Workers.

I've edited the Import table - though I didn't see anything that was strictly "Workday_Data_Import". I edited sn_hr_wday_data_import. It's the closest variation of that table name I could find.

I've edited the workday_worker_xpath_map.

I've added an onAfter script to the Workday_Data_Transform map using the code posted above.

Are there any steps that I might be missing to hook this up appropriately? I can provide any additional information if needed to try and troubleshoot this.

Thanks!

[DenMagnuson1]

Lisa,

Based on your reply, I'm guessing you're using the new scoped app for HR and Workday. I need to update my post to clarify that the solution was for the original Global HR Case Management application that came starting with Fuji and it's associated Workday Integration. What version is your instance running? I have the ability to test with both Helsinki and Instanbul so I can try to replicate and update the steps under there for you.

I did take the HR class as the Pre-Course training at Knowledge17 and the Enterprise Onboarding course that covered the new Lifecycle Events app which will be in Jakarta and breaks down the integration better. I don't know how much difference the scoped app you're running will be from the Jakarta version, but there is a migration tool that will be included to help people like me on the global app transfer over to the new table structures.

Again, let me know what version you're on and I'll setup something I can try to replicate for any instructions that might be different. Are you seeing any errors in your logs?

Dennis

[lisamclaughlin]

Hey Dennis -

Thanks for responding! Yes, we are using the Scoped HR Core & Workday Integration applications. We are currently using Istanbul.

I didn't see any errors when I was initially testing everything out, but let me take another look at that today.