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]

It's still a bit unclear on how you are thinking.

Could you provide example on which tables you are using (which tables are used to store what data), and an example of Excel file you are importing?

[MercBuilding]

Hi @OlaN ,

   Iam using alm_hardware table to import the records. There is an excel file which will have all the column details in this particularly IMEI, ISDN and Sim No are important. Based on these value i need to create/update two records, one record will contain Serial Number as IMEI and the other record has serial number as combination of ISDN and SIM No. The problem is iam unable to figure out how to update the existing records using same row since we can process one row at a time and only one record can be updated/inserted. Please let me know how to proceed on this.

Thanks!!

[OlaN]

Well, basically I can think of 2 different ways of going forward.

You can create 2 transform maps, that transforms into the same table (there is no restriction there), only problem would be the coalesce. You might have to struggle a bit with that and set it up to not create records on empty coalesce and such.

Another option, is to create an onAfter transform script, that creates the additional record on the same table on your given conditions. This option would require some more scripting than the first option.

[MercBuilding]

Hi @OlaN ,

    I have thought of second solution and written the script. But now i realize that for existing records i have already divided the records into two device types, now going forward if excel file needs to be transformed i need to update/create two records in the database, one with Serial Number as IMEI and the other record with Serial Number as combination of ISDN and Sim no and then update all other fields for both the records using single row. So iam just thinking now to wite a on Before script and look up into database to see if both the records exists and then update it, if it doesn't exists i need to create two records with all the data. What do you think of this approach now?

[OlaN]

In general, having the same field (Serial number) to act as several different fields (IMEI on one hand, and ISDN+Sim number on the other) is a bad idea.

You will almost always get stuck at some point when you don't know how to proceed.

My tip would be to separate the data in your instance, and use different fields for different purposes (one for serial, one for IMEI, one for ISDN and one for Sim number). That way you can more easily determine how you want to import new data (and match coalesce).