Transform Map - Mapping Assist

[Ruchi19]

Hi,
I am receiving the data from file and need to load it using transform Map.

File contains the following data
1). USer Id
2). first name
3). Last name
4). Dept ID
5). Dept Name

I want to followng things
1). Update User table based on USer ID
2). look up for Dept ID in Department table and see if it exists, update Dept Name else Insert the record in Dept table.

So I have two Target tables - User and Department. When I map Dept ID to Department table, it throws me null pointer exception error.

Is there anyway to achieve this? please help.

[Ruchi19]

thanks Cheryl. i would try this script. but in the mapping, how should i map the Dept ID field? Would it be mapped it to the target table Dept ? Also, department name is reference field to user table. so in user record dept name also should be updated.

I have tried mapping the Dept Name field to USer.Dept field and Dept Id field to cmn_department.Id field in Mapping with action ignore on all fields. Though it throws 800 - Invalid Map Target Id doesn't exist in table sys_user.

Thanks for your help

[CherylFlood]

The field maps will only write to the table you name in the "target table" of your transform map. So use the field map to send your deptID field to the correct field in your user table (sys_user), and the script will take care of the department table.

Note that if you map a field using a field map, and then map the same field in a transform script to the SAME table, it will run the script first, and then run the field map, overwriting the work done by the script. In your situation, this should not be a problem because you are writing to two different tables.