transform map dilema

[El Cuchi]

Hi all,

i trust this email finds you well.

here is what i need. I do have a custom table call 'cust_table', that contains 3 fields. 'ID', 'name', 'active'.

i would like to update table via import set as follows.

- when there is a match name will update

- when source has an ID that does not exist in 'cust_table' a new record will be created in the target table.

- when a record in the target table 'cust_table' does not have a match in the source, it will set the active field to false.

At the moment, i created the data source and transform map. mapped fields with ID as coalesce and the first 2 points are covered.

what i cannot do is the last point so ID no longer in use are set to false in the target table.

source (xls file) has columns ID, name, active.

would you help me please.

regards.

[Nikhil Bajaj9]

Hi @El Cuchi ,

As per my understandin you need to write transform script to achieve point 3.

Regards,

Nikhil Bajaj

[RaghavSh]

Hi El, There is no direct solution for this like coalesce. Try below in "on complete" transform script.

 var usr = new GlideRecord('cust_table'); // tarhet table
    usr.query();
    while (usr.next()) {
        var iTable = new GlideRecord('import_set_staging_table'); // add your import set table name
        iTable.addQuery('u_id', usr.id); // replace import set table id field/ target table id field.
        itable.addQuery('sys_import_set', source.sys_import_set); // will query only current import set records
        itable.query();
        if (!itable.next()) {
            usr.active=false;
			usr.update();
        }

[Sharique Azim]

If i understand your requirement you  need two coalesce not one. One on ID another on Name. i.e. only combination of both are unique it needs to create a record in the target table, else not unique then update.

Now for active logic, you can do run script and put following logic

if(action == "insert"){

target.u_active= "false";

}

else{

target.u_active= "true";

}

You can try this simple solution .

1. dont need to add new field called last updated.

2.  both onbefore and onstart will not work because you dont know if the final action is going to be insert or update yet.

3. oncomplete is a later operation in the sequence of transform scripts, viable but run script solution can be attained during runtime. 
You decide based on your requirements.

Shariq