Posts Tagged 'crm'

Null data in resultset? Use an Alias

Consider a Scribe job that reads from CRM 4.0, and joins two entities (in this example we’re joining Activity to Contact)

I want to retrieve all the columns from the activity records, along with some columns from the Contact (in this example, the Owner of the Contact).

The source query would look something like this:

Now, I want to retrieve all fields from the activity record.

From the contact record, I want just the name of the owner.

So the Fields tab looks like this:

Easy. What could possibly go wrong?

Now I ‘test’ the DTS and the results aren’t what I’m expecting:

How can the ownerid of the contact be null?

The activity must be linked to a contact (because I ticked the ‘Required (inner join)’ box on the screenshot above), and every contact has an owner. Moreover, if I open the contact record in CRM, then I can see the owner.

The problem is because the activity record is also returning an ownerid field, and Scribe doesn’t like resultsets with duplicate column names. A similar issue occurs with SQL as the source (perhaps a topic for a later post).

How to fix it?

You need to specify an alias to make the column name unique:

And now when I test the DTS, the field is populated:

Scribe Certified Partner
Scribe MVP