Create multiple records using transform map

[MercBuilding]

Hi,

  We have an excel file which will be imported into Servicenow instance on daily basis and there is separate form to import the data using the excel. Both the methods are using the same transform map to import and update/create the asset records in the system. The requirement has changed right now, in the excel i have multiple columns, out of them there are three specific columns which i need to look and create two records based on that.

Example we have Column A, Column B, Column C. If the data is present in all the columns then i need to create two records with different device types in Asset records. if the data is not present in Column A or Column B then i can create only one record through transform map.

How to achieve the above requirement? I can use business rules to insert another record and delete the details of few columns from previously created record but its not good idea as it will effect the performance.

Writing a schedule job is the second option i could think of but this option doesnt work in case of Catalog form as the changes needs to be reflected asap.

Please let me know if its possible to insert using same row in excel using transform map or what is the best approach in this scenarion?

Thanks!!

[OlaN]

Hi,

I would suggest that you create two separate transform maps, both process the same file, but insert records in the two separate tables, one for each transform map.

Then you can create a transform script that skips records in one of the transforms if data is missing in some of the columns.

[MercBuilding]

Hi @OlaN ,

   Thank you for the reply. Actually the data needs to be inserted into same table i.e., Hardware table. Creating two transform map may not be good option for this because the data may not be present in all the columns. If it is present then i need to create another record if the data is not present in column or column B i just need to update the device type and update few other fields. Please let me know if the solution you suggested still works for this requirement?

Thanks!!

[OlaN]

I'm having trouble in understanding the use case.

Why would you want to create two records in the same table ?

Can you give an example how help understand your setup?

How will you configure coalesce fields? If Multiple records can be created from the same file like this, it sounds like you're not using coalesce.

Basically you could use this solution, with two transform maps, even if you would want to import to the same table, but it kinda seems over complicated then, might be easier to just create an onAfter transform script to create the extra record on certain conditions.

[MercBuilding]

Hi @OlaN ,

   Current script uses coalesce and inserts the record, these are mobile records. Now the design is changed a little bit to maintain Mobile Device details and SIM details as separate entitites in Hardware Table. This information is coming from excel file on daily basis, currently it creates only one record as the mobile device and sim details are maintained in single record but going forward this will change.

The scenarios will be:

1. Mobile Device IMEI num is identified as Serial Number. A mobile device can be there in the inventory without sim no.

2. SIM No is treated as Serial Number for Device type- 'Mobile Line' (custom created device type). SIM No can be present without the device.

3. We can have both Mobile and SIM no details for the same record.

For #1. We can update the serial number as IMEI number if SIM No column is empty for that row and device type will be 'mobile' for this.

For #2. We can update the serial number as SIM no if IMEI is empty for that row and device type will be 'Mobile Line' for this.

For #3. We need to update the serial number with only IMEI number, device type as 'Mobile' (parent) and Create a new record (child) with device type as 'mobile line' and sim no as serial no for newly created device, and copy all other details from Mobile device type(parent) record to this one (child).

All the existing record has serial number as Combination of IMEI number+ SIm No. Using Fix script iam able to create new records for existing records if the above condition satifies. But iam trying to implement the same scenario using transform map as the excel file will be sent on daily basis to servicenow instance.

Hope you understand what iam trying to achieve, if not please let me know.

Thanks!!