Transform Map Best Practices

[Alikutty A]

Transform Map Best Practices

While working with transform maps, we generally have an habit of creating temporary tables for the load and leave it later once the job is done. If you have plans to reuse the import set or if they are being configured for a real time integration, it is always better to follow certain best practices which can improve its efficiency and reduce the future maintenance overhead. Here I am listing out some of the best practices that has helped me out.

 

1. Naming – As time passes, there could be many import sets and corresponding transform maps setup in your instance. It becomes a maintenance nightmare to manage these later and additionally to locate the right import table while manually loading any data. Adopting standard naming convention for import sets and transform maps is a time saver for the future support and improves its usability. 

2. Coalesce – If the transform map is created for a long term use, plan your coalesces ahead of designing it. Be careful while selecting attributes like a CI name, User name or any fields which are not unique as these can introduce duplicate records.

3. Execution order for Transform map scripts – Understanding the execution order of transform map scripts can greatly help in setting up the logic at the right place and avoid any unnecessary overrides in the data or validations. The following is the order in which transform map scripts execute

• On Start script - At the beginning of execution (Only once)
• Coalesce source script - Determines action as insert or update
• On Before script - After action is determined and before target is mapped
• Field map source scripts - Field level validations
• Run script - Just before mapping source to target
• On After script - After source is mapped
• On Complete script - At the end of execution (Only once)

4. Choice actions – You should always set the choice action in field maps to ignore for reference and choice columns unless it is necessary. By default, the choice action is set to create on all the fields maps. This allows foreign records or choices to be created from file or external systems that can impact your existing processes.

5. Run Business Rules – Executing business rules on the imported record is necessary to ensure that the record is aligned with the business logic of the related table. There are cases where the developer uncheck the Run business rules which create issues in the imported record. For eg: while importing CI’s, if business rules are not executed then the corresponding asset records are not created which can cause further problems while working with Asset management.

6. The Reference field value name is generally left blank or less used in the transform maps. When coming to reference fields, by default the field maps search on the display value column to find a matching target record. Providing a reference field value name on the field map enables you to make it search on a different column of the target table. For eg: While importing data related to any user reference field. You could input the User ID (user_name) for quickly mapping the target user record by allowing it to search on the User ID instead of the Name column.  

           

7. Column Size - Make sure you make the length of import columns matching with the length of your columns in the target table. You may not notice this while configuring the transform map but this would come to picture at a later time once you productionize the import table.

8. 'ignore = true' does not work on onAfter transform scripts which means that it will always execute on your target record irrespective of its usage in the onBefore or Run script. Your row transformation would be skipped but the onAfter scripts will still execute the logic. This is the OOB behavior as the ignore object is not accessible in the onAfter scripts. If you are using ignore with onAfter scripts, one workaround is to always place your onAfter scripts after a sanity check as shown below. The ignore does not apply to field map source scripts as well.

if(source.getValue("sys_import_state") === 'ignored')
  return;

9. Logging in Transform Scripts - If you are involved in building transform map that requires good amount of logging, there are quite a lot of useful attributes available in the source object for your usage. Here are few of them which could be used for logging.

        

//For eg
source.setValue("sys_import_state_comment", "Your custom error here");

        Attributes in the source object:

• sys_import_row - Row number of import row
• sys_import_state_comment - Comment in Import Row
• sys_row_error - Track Error message/code separately in sys_import_set_row_error table
• sys_import_state - State of Import Row - Inserted, Updated etc
• sys_id - Import set row sys_id
• sys_import_set - Import set sys_id
• import_set_run - Import set run sys_id
• sys_transform_map - Transform map sys_id
• sys_target_sys_id - Target record sys_id
• sys_target_table - Target table of transform
• sys_class_name - Source table or import table name

10. Unused columns - Often, we can see a lot of unused fields on the import tables. These fields are auto created whenever you load a file and are those columns which are never used, added through incorrect data or while playing around with the transform map. For integrations or imports that are frequently used, too many such fields can create confusion on your maintenance. You can set the glide.import_set_row.dynamically_add_fields property to false to prevent dynamic addition of import set columns without your knowledge. Once your transform map is designed, if you need to add a new column, it can be added manually in the dictionary.

        Note: This does not apply to new import table creation but on future loads

11. Clean up Import Sets – Use the /cleanup_import_sets.do page to remove the unused import set tables or transform maps. This should keep the original list of import tables updated and avoids any confusion over selecting the right table while importing data.