Help with Transform Map script

[joelw]

Hi All,

I have a requirement to import a csv daily in to a case management solution and I'm a little stuck on the transform map script.

I have the one data source and three transform maps.   The first transform map imports a 'customer' records, the second 'account' records and the third 'case' detail which includes the customer number (it's a reference field in the target table) which was loaded on the first transform.

The onBefore transform map script is below - where I want to check the 'case' table to see if there is an 'active' case and the source customer number = the target customer number in the case record.   And if so, don't insert.     They currently all go and insert so obviously I missing something in my script - any assistance would be greatly appreciated.

Cheers,

Joel

//Check to see if record exists in CASE table

//Compare CUSTOMER NUMBER and ACTIVE flag

var customer = source.u_customer_number;

var rgcase = new GlideRecord('x_taipl_case_mgmt');

rgcase.addQuery('active', true);

rgcase.addQuery('customer.customer_number', customer);

rgcase.query();

//IF RECORD ALREADY EXISTS SKIP THE RECORD AND GO TO THE NEXT

    if(rgcase.next()){

            ignore = true;

    }

[Geoffrey2]

Hi Joel,

Your code looks fine to me. I can only suggest some debug logging to try and figure out what's going on.

//Check to see if record exists in CASE table
//Compare CUSTOMER NUMBER and ACTIVE flag

var customer = source.u_customer_number;
gs.info('customer: ' + customer);

var rgcase = new GlideRecord('x_taipl_case_mgmt');

rgcase.addQuery('active', true);
rgcase.addQuery('customer.customer_number', customer);

rgcase.query();

//IF RECORD ALREADY EXISTS SKIP THE RECORD AND GO TO THE NEXT
     if(rgcase.next()){
             ignore = true;
       gs.info('Existing Case found - ignoring');
     } else {
       gs.info('No Case found - inserting');
}

[Mwatkins]

Hi Joel,

There is nothing theoretically wrong with the way you are doing this. Your GlideRecord.addQuery() method uses a dot-walked field name, which means the database will do a join on the customer table and x_taipl_case_mgmt table. You are assigning source.u_customer_number to a variable. This will effectively make a pointer that is dynamic to the runtime but that shouldn't be a problem. The object ought to be cast to a hard datatype when the addQuery() method is run.

Here's a couple things I'm thinking:

1) If you run this script in Scripts - Background and populate the "customer" variable with a value that you know matches a customer_number what happens?

2) Maybe one of the field names is written wrong. Should it be u_customer.u_customer_number?

3) Maybe there is a capitalization difference in the values? What comes out in the logs for gs.log(customer)?

4) Try forcing the "customer" variable to a String type during the addQuery() method like this:

rgcase.addQuery('customer.customer_number', customer + "");

5) Do you have any other onBefore scripts that set ignore = false? Maybe these are overriding your ignore = true.

6) Are you sure the script is running? Does anything come out if you put a gs.log() statement in the code?

7) Are there any log statements in the Warning or Error logs?

[Geoffrey2]

I'm assuming by the table name "x_taipl_case_mgmt" that this is a Scoped Application. One thing to keep in mind is that gs.log() does not work in Scoped Apps.   A gs.log() call in a Scoped App script will cause the script to fail an stop executing.

[Mwatkins]

Right, good point Geoffrey. Use gs.debug() if that is the case.

Scoped Script Logging - ServiceNow Wiki