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

[Mike Patel]

Thank you for sharing, I got it working. Not sure why but some users image is not getting decoded. I see the WD image field has value and I can convert it using https://codebeautify.org/base64-to-image-converter. But not sure why it's not creating attachment record. Did you had any issue like it?

[Mike Patel]

It works great only issue is once we do one time load and someone changes their picture in WD.  It doesn't show up on WD import set since we are not clearing Properties (Workday Properties, clear the Date since last successful run value so you pull all employee records.)