How to insert import table fields as new records into multiple tables and set them on Target record?

[Manoj KV]

Hi ,

I have an import set which contains "Asset" records with multiple fields few of them are Manufacturer , Model and then details related to Asset information.

Step1:The details from "Manufacturer" column needs to be inserted on to companies table first.
Step2: Then Model details from Model column to be inserted in Model table and also the manufacturer (reference field) inserted in step1 should be populated as well based on the import set records.
Step3: Now prior to asset record is inserted based on the import data the model should be populated from theStep2 and then the asset record to be created.

How do we write a script for this kind of scenario where it has multiple dependencies on other tables prior to the asset record being created using Transform map?

[4b0d3]

Create multiple Transform maps.  There's an order that you can set for each map internally to the transform map.   and when you assign Map(s) , that's right you can click on multiple for one import.  To make your life easier try to match the column names with your field names (not labels).  Enabling Coalesce will help allowing to update or insert (if you don't want to update, could leave it off).
1. Hopefully you can insert with field maps without any scripts.  So add to your manufacturer table.  If you have data there, make a note that probably the company name have to be perfectly matched, unless you have other data that would be perfectly matched.  So that it updates and doesn't insert..
2. 2nd map will probably have field maps with a script to look up the sys_ids from the manufacturer to add to it's table.

3. 3rd, will have to be a script with field maps.  the script would use the source manufacturer name to query the manufacturer table to gain the sys_id.  & same for the model.

[Abhay Kumar1]

@Manoj KV There can be multiple approach here and i will prefer to go with below approach if i were you,

1. For import set, my source table (stagging table) will be as per importanting file and target would be asset table.

2. I will create a script include which will take care of update/create in company & model with passing source as parameter in function of this script include.

3. Will use Script include either in tun script or transform script ,whatever suits you, to be take care of reference table to be updated created and then target table.

Hope this will help you.

[Manoj KV]

Hi, 

I was able to complete this mapping on multiple tables using script on the Transform map

1)I did a GlideRecord query on manufacturers table , initialized it and then inserted the record captured the sys_id in a "data1" variable.
2) Created another GlideRecord query on Models table, initialized it and then updated the manufacturer on Models using the "data1" variable and captured sys_id of inserted record in "data2" variable
3) Finally I just put target.model = data2

This populated data in all tables as required.