This functionality enables users to upload files (like Excel spreadsheets) through a catalog item and automates importing and transforming the data. The process upon submission, imports the file, transforms it and updates the target table, deletes the attachment, and sends an email to the requester with the import results. This gives users the power to handle simple, repeatable data loads without developer or admin intervention.
Technical Approach
Catalog Item:
Create a new catalog item. You don't need to add any variables—just enable the "Mandatory Attachment" option under the portal settings. Use the “Available For” and “Not Available For” to allow or restrict access to the catalog item.
Data Source + Transform Map:
Create a new Data Source that matches the format of the files that will be uploaded. These are the values/choices that worked for my use case but they could be different depending on the type of attachment being uploaded. These fields are critical:
- Import Set Table Name: Make sure this is correct.
To find it, go to All > System Import Sets > Load Data, select Create New, input a name, upload your file, then click Submit.
Once redirected, click Loaded Data, then go to Column Context Menu > Configure > Table to copy the table name. - Type: File
- Format: Excel
- File Retrieval Method: Attachment
We won't go into configuring the Transform Map here since its setup will vary but you will need its sys_id. Navigate to the Transform Map record and copy the sys_id for use in the Flow Designer step.
Script Include:
Create a Script Include (global scope) named something in camelCase to load the attachment, perform the transform, and return the sys_id of the resulting import set. The API you should look into:
var importOnDemand = Class.create();
importOnDemand.prototype = {
importAndTransform: function(dataSourceSysId, transformMapSysId) {
var grDataSource = new GlideRecord('sys_data_source');
if (!grDataSource.get(dataSourceSysId)) {
return;
}
var importSetLoader = new GlideImportSetLoader();
var grImportSet = importSetLoader.getImportSetGr(grDataSource);
importSetLoader.loadImportSetTable(grImportSet, grDataSource);
grImportSet.setValue('state', 'loaded');
grImportSet.update();
var importSetTransformer = new GlideImportSetTransformer();
importSetTransformer.setImportSetID(grImportSet.getUniqueValue());
importSetTransformer.setMapID(transformMapSysId);
importSetTransformer.setSyncImport(true);
importSetTransformer.transformAllMaps(grImportSet);
return {
import_set_sys_id: grImportSet.getUniqueValue()
};
},
type: 'importOnDemand'
};
Flow Action:
Create an Action using Flow Designer with two inputs:
- dataSourceSysId (String)
- transformMapSysId (String)
Add a script step to execute the Script Include and return the import set sys_id:
(function execute(inputs, outputs) {
var importHelper = new global.importOnDemand();
var result = importHelper.importAndTransform(inputs.dataSourceSysId, inputs.transformMapSysId);
outputs.import_set_sys_id = result.import_set_sys_id;
})(inputs, outputs);
Under Outputs, create a variable with the same name as the final output line: import_set_sys_id. Map the return value using the data pill picker.
Flow:
Create a Flow that:
- Triggers on Service Catalog.
- Uses Lookup Record to retrieve the Data Source.
- Looks up the attachment record.
- Runs the Action from above, passing the sys_ids of the Data Source and Transform Map.
- Deletes the uploaded attachment from the Requested Item (select Delete All).
- Uses Lookup Record on sys_import_set_run, filtering by the returned import_set_sys_id and state IN complete, complete with errors.
- Sends an email to the requester using data pills from the Requested Item, Transform History, and the Action output.
Hook It All Together
In the Catalog Item, go to the Process Engine tab and link the Flow you created.
Notable Lessons
- Make sure the Flow runs as the system user.
- Update the system property sn_flow_designer.allowed_system_tables to include sys_import_set_run.
- If you run into this error “invalid pharmacy compound uuid” there is an answer here. Within the flow the send email action errored out so I deleted it and added it back.
The Wrap Up
If you made it this far, thank you for reading my first technical article. I'm just a junior developer sharing things that have worked for me. As I build more functionalities and learn new things, I'll share them with you all in hopes that you and/or your company can also benefit. This functionality can be used over and over and customized for different use cases.
If you try this or customize it for your org, I’d love to hear how it went in the comments below! Happy developing until next time!
