how to check condition in Transform map script

[Akki1]

Hi,

I have excel file which contains two column sys_id1 & sys_id2

Now I am importing records in u_m2m_table where i have two fields field1 and field2. These two fields are references to table 1 and table 2 respectively. Now while importing records in  u_m2m_table I want to check if sys_id1 and sys_id2 are valid and then insert else ignore.

Also I want to insert only if sys_id1 & sys_id2 these both fields are not empty.

I know to use onBefore scripts. But how can it be done? What to write the content?can anyone help with this?

[Saurav11]

Yes if the record is already present in another instance and you create a new record over here the sysid will be autogenerated you cannot control that

[Akki1]

@Saurav11 can you think of any solution for this?

[Saurav11]

Hello,

The first question would be why would the data be different on different instances. Shouldn't it be in sync in Dev, Test and Prod?

[Akki1]

@Saurav11 

yes but that sync doesn't happens regularly

[Mahendra RC]

Hello @Akki1 

Can you please check with the below approach:

1. You create the field mapping on transform map for sys_id1 and sys_id2 and set the Coalesce true for both the field mapping. So that when you import the data in your m2m table it will check if there is any record already existing with sys_id1 and sys_id2 on your m2m table. If there is any record it will try to update the record but since there are only 2 fields sys_id1 and sys_id2 it will not update anything. In case if there is no record in m2m table with sys_id1 and sys_id2 then if will create the record. If you do this then if any of the one field sys_id1 or sys_id2 is empty it will not create any record. So empty field validation will be handled.

2. Then you can take the export xml from instance 1 and import to your instance 2. so all the records will then be created in your instance 2. but suppose there are some record records in table1 to which your sys_id1 field is referring where present in your instance 1 but not present in instance 2 then on your m2m table you will see the sys_id1 as empty on list view but at backend it will store the sys_id. So it is better that you import the excel file separately on instance 1 and instance 2. I believe if the sys_id for the records in your m2m table is different between you both the instance if should not make much difference.

OR else you may need to create some kind of integration between the 2 instances to sync the data on regular basis. Using Integration you can create the records using the same sys_id from instance 1 to instance 2.