IntegrationHub ETL Table Lookup/Field Mapping Transform script with Regex

Tone1
Tera Contributor

‎07-22-2023 10:16 AM

Hello

 

i'm playing a bit with the IntegrationHub ETL and i came across a problem i can not solve.

 

My Input is a Excel:

Name,Costcenter

srv21,001

srv43,004

 

I want to import  Servers to the Windows Server Class and automatically populate the Cost Center field based on the Account Number but the Account-Numbers i have in the cmn_cost_center are ACN0001, ACN0002 etc... (demo instance). The Table Lookup feature as a Transform option needs 1to1 matches so i think i have to use a transform script with regex to lookup the Account number in cmn_cost_center, look for a regex match (001 against ACN0001, the 004 againts ACN0004) and use the correct account number from cmn_cost_center as the entry, any idea how i can do this easily?

 

Thanks in advance.

0 Helpfuls
  • 1,427 Views
1 REPLY 1

Tone1
Tera Contributor

‎07-23-2023 03:33 AM

I figured it out myself but with my limited JS skills i'm not sure if it's a good idea how i approach this problem:

 

(function(batch, output) {
   for (var i = 0; i < batch.length; i++) {
      var input = batch[i].input; // Value of the input column.

      // Add your code here.
      
      // Create a new GlideRecord object for the table that you want to print.
var gr = new GlideRecord("cmn_cost_center");

// Query the table for all records.
gr.query();

// Loop through the records and print the content of each record.
while (gr.next()) {
  // Match the content of gr.name against the regex.
  var match = gr.account_number.match(input);

  // If the match is successful, print the matched text.
  if (match) {
    output[i] = gr.account_number;
  }
}

      // Set each output element below
      //output[i] = output_variable_name;
   }
})(batch, output);

 

0 Helpfuls