Update data in 2 tables through sheet upload, using Transform map.

[Hrishabh Kumar]

Requirement: 

A sheet contain device data for two different classes "Handheld Scanner" & "Payment Terminal", this is specified by "Class" column in the sheet. Once the sheet in uploaded we want the data to be updated in respective table i.e "u_cmdb_ci_handheld_scanner" & "cmdb_ci_payment". We only want to update the data based on "Serial_number" column and not insert any new record.

My Solution:

• I created a datasource >> Transform map, and set target table as "u_cmdb_handheld_scanner", and I'm changing the target table to "cmdb_ci_payment" conditionally in transform script using <taget.sys_class_name = 'cmdb_ci_payment'> if <source.u_class == 'Payment Terminal'>.
• I have created a mapping and set "serial number" as coalesce field.
• I'm also setting the <ignore = true> if <action == 'insert'>

Here is my "onBefore" Transform script.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
 
    // Set target table based on class.
    if (source.u_class == "Payment Terminal") {
        target.sys_class_name = 'cmdb_ci_payment';
     }
   
    // Ignore transformation is record is inserted
     if (action == 'insert') {
      ignore = true;
     }
 
})(source, map, log, target);

Problem: 

I'm only able to update the records in "u_cmdb_ci_handhel_scanner" table, "cmdb_ci_terminal" records are not being updated even if to upload already existing serial number. It is treated as new record and is ignored.

How to fix this, I need both table records should get updated.

[Aman Kumar S · Accepted solution]

Hi @Hrishabh Kumar 

There are two ways I see you can make it work, first to have two transform maps with different target tables.

Or

Make the parent table of both the tables as target table and then check for classes and set the classes based on source.

The approach that you are taking will only work if "u_cmdb_ci_handheld_scanner" is the parent table to Payments table

[Hrishabh Kumar]

Hi @Aman Kumar S 

Thanks for your reply, I have a question.

If I use 2 transform maps for specific target tables, Will both of them run as I'm using scheduled import and I'm triggering the data source though script. 

In this case, will both transform maps run one by one, or only one will run?

[Aman Kumar S]

Hi @Hrishabh Kumar 

Both would run, provided they are mapped to that data source.

You have order field through which you can control in which order the transform map should execute.