This is a continuation of part 1 and part 2.  Part 1 I discussed setting up a SQL environment for importing and Part 2 talked about how to load in a source file into the import environment.  In this post I am going to discuss actually transforming the data source so that it is in the format of SalesLogix.

The transformation is the most crucial part of the process as it is where you can clean the data and apply any business rules necessary prior to actually doing the imports. 

Since your source data is likely not in the same format as SalesLogix, I typically use a series of SQL views to transform the data into a format that I want.

Lets take a look at the source table again, remember based on part 2 we loaded the source file into a SQL table called ImportTable.

Now for demonstration purposes this is a very simple table but it will allow us to demonstrate the important points.  The first step is to separate out the base table into the buckets of data that SalesLogix uses: Account and Address data.  Normally your file would probably contain Contact and other information as well.

The first thing I notice about my table is I don not have a unique identifier that allows me to identify records.  I could use the Company column, but it may not be unique.  I will add a SQL identity column to the table:

• Open the SQL Enterprise manager and expand out your database to the tables listing.
• Under the table that you imported into, right click and select Design table.
• In the blank row after the last Column Name in the grid on the top half of the Design Table dialog, enter a name for your unique column.  Select the data type of integer (Int).
• In the lower half of the dialog, select “yes” for Entity.
• Save your changes.  You now have a unique incrementing ID for each row in the table.

To separate out the file I am going to create two views.  These views will split the data out into the Account level fields and into the Address level fields.  I normally try to name my views logically so I know what purpose the serve, so I will do so here.  Now to create a view I will normally just script it in SQL Query analyzer.  Here are my SQL view scripts:

Create view sysdba.vbaseAccount
as
Select
Company as Account,
upper(Company) as Account_UC,
replace(replace(replace(replace(Phone,'(‘,”),’)’,”),’-‘,”),’ ‘,”) as Mainphone,
‘Prospect’ as Type,
Mgr,
ID
from sysdba.ImportTable with(NOLOCK)

Create view sysdba.vbaseAddress
as
Select
Address as Address1,
Address2,
City,
State,
Zip as PostalCode,
‘USA’ as Country,
ID
from sysdba.ImportTable with(NOLOCK)

Now you will notice that the views have a little bit of data manipulation going on.  I have replaced the characters (,),-, and spaces from the phone field.  I have also hard coded the Country field.  This is just a small sample of what can be done but should give you some ideas.  Note also that I have added some SalesLogix specific requirements, like adding the Account_UC field.    I also utilize the With(NOLOCK) statement to prevent the views from locking the SQL objects.

The next step is to link my data to the SalesLogix system so I can determine which records I have in SLX and which I do not.  Again with my sample to keep things simple I will just use the Company name to look for existing records.  My next set of views are built upon my previous views.  I am going to create two views per data bucket, one for Adding new Accounts, one for Editing existing Accounts and again a set for the address data:

Create view sysdba.vnewAccount
as
Select
b.accountid,
a.Account,
a.Account_UC,
a.Mainphone,
a.Type,
a.Mgr,
a.ID
from sysdba.vbaseAccount a with(NOLOCK) left join
SalesLogix.sysdba.Account b with(NOLOCK) on a.account = b.account
where
b.accountid is null

Create view sysdba.vexistingAccount
as
Select
b.accountid,
a.Account,
a.Account_UC,
a.Mainphone,
a.Type,
a.Mgr,
a.ID
from sysdba.vbaseAccount a with(NOLOCK) left join
SalesLogix.sysdba.Account b with(NOLOCK) on a.account = b.account
where
b.accountid is not null

You can see I have used a left join to the SalesLogix database to look for matching records.  That way I can just change the final where clause to determine those records that do and do not exist.

Now we do a similar set for the address data:

Create view sysdba.vnewAddress
as
Select
c.addressid
b.accountid as entityid,
c.Address1,
c.Address2,
c.City,
c.State,
c.PostalCode,
c.Country,
‘Primary’ Description,
‘T’ isprimary,
‘T’ ismailing
from sysdba.vbaseAccount a with(NOLOCK) left join
SalesLogix.sysdba.Account b with(NOLOCK) on a.account = b.account left join
sysdba.vbaseAddress c with(NOLOCK) on a.ID = c.ID left join
SalesLogix.sysdba.Address d with(NOLOCK) on b.Accountid=d.entityid
where
b.accountid is null and
c.Description <> ‘Primary’

Create view sysdba.vExistingAddress
as
Select
c.addressid,
b.accountid as entityid,
c.Address1,
c.Address2,
c.City,
c.State,
c.PostalCode,
c.Country
from sysdba.vbaseAccount a with(NOLOCK) left join
SalesLogix.sysdba.Account b with(NOLOCK) on a.account = b.account left join
sysdba.vbaseAddress c with(NOLOCK) on a.ID = c.ID left join
SalesLogix.sysdba.Address d with(NOLOCK) on b.Accountid=d.entityid
where
b.accountid is null and
c.Description = ‘Primary’

The address views have an additional join to the SalesLogix ADDRESS table to determine if the “Primary” address exists.  These views do not have a check to see if the Account exists as it is assumed that if they Account needs to be added it will be done prior to this view executing.  This will make more sense later.

Now a final view that needs to be created is used to join the Account records that have been added with any new Address records also added.  In SalesLogix the ACCOUNT table stores the primary and shipping address for an account in the ADDRESSID and SHIPPINGID fields.  Yet when we insert the Account record we have not yet added the Address record.  We add the Address record second as the ACCOUNTID that is generated by SalesLogix is needed to populate the ADDRESS ENTITYID field. So now we need to build a view that goes back and updates the ACCOUNT table with the ADDRESSID that is created when we add the ADDRESS records during the import:

create view sysdba.z_UpdateAccount
as
Select
b.addressid,
b.addressid shippingid
from
SalesLogix.sysdba.Account a left join
SalesLogix.sysdba.Address b on a.accountid=b.entityid
where
a.addressid is null and a.shippingid is null

That is it.  A simple example but it shows how you can utilize SQL views to transform a data source into a format for SalesLogix.