Cannot add table to import set

[Hm10]

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);

[palanikumar]

I see some issue with your Data source record. I see important fields and attachments are not set. Update the data source part with the below script and let me know whether it is working

    var dataSourceGR = new GlideRecord('sys_data_source');
    dataSourceGR.initialize();
    dataSourceGR.setValue('name', latestFilename); // Set the file name in the 'name' field
    dataSourceGR.format = 'Excel'; // Change if this is not excel
    dataSourceGR.import_set_table_name = 'import set table name'; // Update this text with actual import set table name
    dataSourceGR.header_row = '1';
    dataSourceGR.sheet_number = '1';
    dataSourceGR.file_retrieval_method = 'Attachment';
    dataSourceGR.type = "File";
    dataSourceGR.insert();
    GlideSysAttachment.copy('sys_attachment', gr.sys_id, 'sys_data_source', dataSourceGR.sys_id); // Copy attachment to data source

[Hm10]

hi @palanikumar , thanks for your reply, can you please let me know How can I give name of import set before creating it? 

(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;

       var dataSourceGR = new GlideRecord('sys_data_source');
    dataSourceGR.initialize();
    dataSourceGR.setValue('name', latestFilename); // Set the file name in the 'name' field
    dataSourceGR.format = 'Excel'; // Change if this is not excel
    dataSourceGR.import_set_table_name = 'import set table name'; // Update this text with actual import set table name
    dataSourceGR.header_row = '1';
    dataSourceGR.sheet_number = '1';
    dataSourceGR.file_retrieval_method = 'Attachment';
    dataSourceGR.type = "File";
    dataSourceGR.insert();
    GlideSysAttachment.copy('sys_attachment', gr.sys_id, 'sys_data_source', dataSourceGR.sys_id); // Copy attachment to data source
    var loader = new GlideImportSetLoader();
    var importSetRec = loader.getImportSetGr(dataSourceGR);
    var ranload = loader.loadImportSetTable(importSetRec, dataSourceGR);
    importSetRec.state = "loaded";
    importSetRec.update();
  //   gs.log("Import Set"+importSetRec);
    // 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); // Set the source table to the Import Set sys_id
      transformMapGR.setValue('target_table', 'u_excel_data'); // Set the target table
	        transformMapGR.setValue('enforce_mandatory_fields', true); // Enforce mandatory fields
      transformMapGR.setValue('copy_empty_fields', true); // Copy empty fields
      transformMapGR.setValue('create_new_record_on_empty_coalesce_fields', true); // Create new record on empty coalesce fields
      transformMapGR.insert();
      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(importSetRec.getUniqueValue(), transformMapSysID);
      transformWorker.setBackground(true);
      transformWorker.start();
      gs.log("Transformation started for " + latestFilename + " by user " + currentUserId);
      
    }
	else {
    gs.log("Attachment not found for user " + currentUserId);
	}
})(current, previous);

[palanikumar]

Please create Import set table and Transform map. Data Source can be created dynamically

[Hm10]

You are saying that I should create Import Set and Transform map from code?