Please Help with importing excel Data - Transform map

[Jumy]

Hello,

I have an excel data to import into the Business Services and Service offering table respectively. I was able to import the Business service successfully. But my company requested that I import a list of offerings to the Business services (as shown in the image). Unfortunately, I could not achieve the offerings transform map after I coalesced the Service Name. Only one offering for each Business Services was successfully transformed

Please I need help on How to go about it.

PS: the image are just sample. The data is huge.

[Kristen Ankeny]

Are you using regular transform maps? If so, I suggest leveraging IntegrationHub ETL which is purpose built to populate the CMDB including creating multiple records and relationships between them. 

[Jumy]

Hi Kristen, 

Thank you very much for your suggestion.

I unfortunately do not know how to go about this. But I will check it out. Or do you have idea of procedure to implement it?

[Community Alums · Accepted solution]

Hi @Jumy ,

You need to perform the below steps-

step1 - create the Transform Map for the service offering

Step 2 - Map the fields from your import set to the target table

step 3 - Create an transform script to associate service offering with business services:

The script should look like-

(function transformRow(source, target, map, log, isUpdate) {
    var businessServiceGR = new GlideRecord('cmdb_ci_service');
    businessServiceGR.addQuery('name', source.u_service_name);
    businessServiceGR.query();
    if (businessServiceGR.next()) {
        target.u_business_service = businessServiceGR.sys_id; // Reference field to business service
        target.name = source.u_service_offering; // Set the name of the offering
    } else {
        gs.info('Business service not found for: ' + source.u_service_name);
    }
})(source, target, map, log, isUpdate);

Adjust fields based on your instance-

If my response has resolved your query, please consider giving it a thumbs up ‌‌ and marking it as the correct answer‌‌!

Thanks & Regards,

Sanjay Kumar

[Jumy]

Hi Sanjay,

Thanks for the step-by-step guide. I tried it out, It works! I am so grateful