Cannot add table to import set
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-17-2023 07:54 AM
This BR runs until this log gs.log("File name inserted into sys_data_source for " + latestFilename + " by user " + currentUserId);
, and then it gives me false in if(ranout). I am trying to get file from user attachment in Change Request and then parse it into a table,
(function executeRule(current, previous /* , g */) {
// Get the current user's sys_id
var currentUserId = gs.getUserID();
gs.log("Business Rule Running" + currentUserId);
var gr = new GlideRecord('sys_attachment');
var firstName = gs.getUser().getFirstName().toLowerCase();
gs.log("FirstName" + firstName);
gr.addQuery('sys_updated_by', firstName);
gr.orderByDesc('sys_created_on');
gr.query();
if (gr.next()) {
var latestFilename = gr.file_name;
// Set the retrieved filename to a field or perform other actions here
current.latest_inserted_filename = latestFilename;
// Log the message
gs.log("Latest inserted filename by user " + currentUserId + ": " + latestFilename);
var dataSourceGR = new GlideRecord('sys_data_source');
dataSourceGR.initialize();
dataSourceGR.setValue('name', latestFilename); // Set the file name in the 'name' field
dataSourceGR.insert();
gs.log("File name inserted into sys_data_source for " + latestFilename + " by user " + currentUserId);
var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(dataSourceGR);
var ranload = loader.loadImportSetTable(importSetRec, dataSourceGR);
importSetRec.state = "loaded";
importSetRec.update();
// Ensure the Import Set has been successfully loaded before creating a Transform Map
if (ranload) {
// Create a new Transform Map
var transformMapGR = new GlideRecord('sys_transform_map');
transformMapGR.initialize();
transformMapGR.setValue('name', 'Excel_transform_map'); // Set the name of your Transform Map
transformMapGR.setValue('source_table', importSetRec.getUniqueValue()); // Set the source table to the Import Set sys_id
transformMapGR.setValue('target_table', 'u_excel_data'); // Set the target table
transformMapGR.insert();
gs.log("Transform Map 'Excel_transform_map' created.");
// Trigger the transformation (uncomment and modify as needed)
/*
var transformMapSysID = transformMapGR.getUniqueValue();
var importSetRecSysID = importSetRec.getUniqueValue();
var transformWorker = new GlideImportSetTransformerWorker(importSetRecSysID, transformMapSysID);
transformWorker.setBackground(true);
transformWorker.start();
gs.log("Transformation started for " + latestFilename + " by user " + currentUserId);
*/
} else {
gs.log("Import Set could not be loaded. Check your data source.");
gs.log("ranload: " + ranload);
gs.log("Error message, if any: " + importSetRec.getLoadErrorMessage());
}
} /* else {
gs.log("Attachment not found for user " + currentUserId);
} */
})(current, previous);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-18-2023 04:02 AM
You can create import set and transform map manually. Refer them created one in the code.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-18-2023 04:39 AM
Thanks, but If I create it manually, I wont be able to get a file from user at run time. I will have to select an excel file here.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-18-2023 06:30 AM
Create a Data Source and attach a sample Excel file. Create Import Set and Transform map from the Data Source. Once created, this import set and transform map can be used in this script.
Thank you,
Palani
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-19-2023 02:47 AM
So when there is different excel file everytime to be uploaded?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-19-2023 02:52 AM
You need to maintain the same template (Headers inside the sheet). Excel file can be different and data can be different