Send the data to Target table based on condition is Transform Map Run Script

cnu1916 · ‎04-14-2019

HI All,

Could you please help me with below script. My requirement is that in table transform map I do have have one field called "Device type" and the data is for Device type is router, switch, firewall etc. So now my requirement is I would like to Run a script to send only Device type is firewall then the respective data only should be inserted into target table. After that I will map the fields accordingly. Can you please help at earliest.

Thanks,

Cnu

Ankur Bawiskar · ‎04-15-2019

Hi,

So if device type is not firewall and not switch then you want to stop insertion?

you should use & condition for this.

if(source.device_type !='Firewall' && source.device_type !='Switch'){
ignore = true;
}

If you use OR then what will happen is when device type is firewall the first condition will evaluate as false and second will evaluate to true and total expression will evaluate to true and hence it will ignore record insertion which you don't want.

same will happen when device type is switch

Mark Correct if this solves your issue and also mark Helpful if you find my response worthy based on the impact.
Thanks
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

cnu1916 · ‎04-15-2019

Hi Ankur,

Thanks for your response. It is working perfect 🙂

However, I am getting another issue when I use onbefore transform script. The main requirement is.

By using Solarwinds Data Base the data is coming to one staging table (Initially there are 900+ records in staging table) and based on device type condition I need to send the data to different tables. Like if Device type is Router then the data is send to Router table and device type is switch then the data send to switch table, device type is firewall then the data is send to firewall etc.. the same way I need to send the data based on the device type condition in staging table.

So I have used your onbefore transform script and in each table transform map and mapped the fields as well for the target table. It is working fine but the problem is that when I try to run transform and select more than one table then the staging tables records are inserting (when the load all records it should happen but here when I run transform map the staging table records are inserting and count is keep on increasing) like when I load the all records the staging table records are 900+ after I run the transform map with 2 tables selected now the count is showing apprx 1800+. If select run transform map with 3 tables thne count is increasing with 2700+ records. I dont know why staging table's records are updating/inserting when I try to run the transform map. Is it happening with onbefore script or any other issue. Can you please advise.

Thanks,

Cnu

Ankur Bawiskar · ‎04-15-2019

Hi,

I don't think that might be the issue. run a fresh load and check once.

Regards

Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

cnu1916 · ‎04-15-2019

Hi Ankur,

Yes it is happening. I have tried with fresh load data even though the records are inserting into staging table. Can you please advise.

Thanks,

Cnu

Ankur Bawiskar · ‎04-15-2019

Hi,

So 900 records in excel file you are loading? how many are being shown for that import set number?

Can you empty the staging table i.e. delete all the records and try fresh load?

Regards

Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

cnu1916 · ‎04-15-2019

Hi Ankur,

Initially, when I load the data to staging table the records are 900+ but after run the transform map and send the data to firewall, switch etc based on Device condition using run transform the data count is increasing now and the data in staging table is 1800+ records.

I have tried with fresh data load as well I mean I have entire data from all my source(staging table) and target table as well even though it is happening. Can you please advise.

Thanks,

Cnu