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.

[Ivan Martez]

The ID field on the department table is a string field, so you should be able to put almost anything in it. On your transform maps, which one is first in the order? It should be the department as it is a reference field on the user record. Can you put up screen shots of what your transform maps look like? Do you have any transform scripts? What are you coalescing on?

[Ruchi19]

Yes, ID field is not an issue.
I have 2 columns received in a file. Dept ID and Dept Name. So I want to update both the fields on Dept table if it exists else it should insert the new record in Dept table.

I am coalescing on User ID field on User table. Also, currently I have no script on transform map.

[CherylFlood]

Your post says you are coalescing on the User table, but want to update the Department table. Are you saying that when a user record imports with a department name and ID, you want to update the department table (or insert into it) from within a transform map that is actually writing to the User table?

[CherylFlood]

Try this in an "onBefore" transform script in the same transform map as your user table:

var deptID = "Dept ID"; // substitute the incoming field name. ex. "u_dept_id"
var deptName = "Dept Name" // substitue the incoming field name ex. "u_dept_name"
var dept = new GlideRecord("cmn_department");
dept.addQuery("id", deptID); //use the correct field name within the quotes, followed by the value of deptID
dept.addQuery("name", deptName);
dept.query();
if (dept.next()) {
log.info("Department found...writing to "+deptName+" record");
target.name.setDisplayValue(deptName);
target.deptID.setDisplayValue(deptID);
} else {
log.info("Department not found...inserting new department "+deptName);
dept.initialize();
dept.name = deptName;
dept.id = deptID;
dept.insert();
}