Include Pipeliner custom field lookup data to import data source

 

The Goal

*This article assumes that you are using a text file as a data source

There might be time when you need to incorporate existing Pipeliner data into the import data source for various reasons.  What we want to do is to make our main data source include the id of a lookup value which is included in the import data source  (for example, manager names).

In the sample data source below, we have a column named “Udupcheck” which contains the manager for a particular item.  Let’s assume that we also have a custom field in Pipeliner which is a drop down with the values JOY, JAMES, HENRY, or JOHN.

What we need is to have a new column named “UdupcheckId” added to the data source which will have the corresponding Id of the lookup value.

Step 1

Make sure that your import data source file contains an empty column, in this walk-through, we want a column named “UdupcheckId”.

 

Step 2

Add a new task to your existing import process that will get all the lookup data used by custom fields.  To do this click on “Add new task” and select “Data Source -> Pipeliner Database”.

Use the following query:

select id,value from data

Take note of the “Data Source Unique Name” value.  In the screenshot below, it is named “GetLookupData_7”

 

Save the task and close the task window.  Position this task so that it is right below the task that reads the import data source.

The position of the task that gets Pipeliner lookup data is critical.  As you can see below, the “Get lookup data” is right below the import data source task.

 

Step 3

Add a new Data Source Task of type “Query SQL/ODBC Data Source”.  Make sure that this task is setup so that it is right before the Import/Update task.

 

Setup the settings to something similar below.

Take note of the items with an arrow.

  • The “Database To Query” should be pointing at “DBX Database Connection”
  • The “Query Action Type” should be set to “Update/Delete”
  • DO NOT click on the “Refresh Displayed Records” button.
  • The query shown in the image below simply updates the import data source table.
    The biggest challenge in getting the query correct is identifying the correct table names that will be part of the query.  The value “ReadImportData_7” is the “Data Source Unique Name” of the import data source task.

 

 

 

 

 

 

 

  • The “getLookupdata_7” is the destination sql table name for the records read from the text file.Here is the query in raw text:update [ReadImportData_7]
    set [ReadImportData_7].UDUPCHECKID = lookupdata.id
    from getlookupdata_7 lookupdata inner join
    [ReadImportData_7] source2
    on source2.udupcheck = lookupdata.value

    All it will do is update the value of UDupcheckId column in the ReadImportData_7 table with the corresponding id of the lookup value.

 

In the end you will have something like the following:

Lets review the tasks that are shown above.

  1. DBX will read the import data source and it will be stored in an SQL table named “ReadImportData_7”.
  2. DBX will retrieve the Pipeliner lookup data and store it to a table named “GetLookupData_7”
  3. DBX will run an update query to populate the UDupcheckId in the import data source table.
  4. DBX will import the records available from the import data source.

Conceptually, the new field “UDupCheckId” can be used by the import task for record matching purposes.  Given that a drop down value can contain a limited list of entries, you will need to make sure that the value is combined with another value that would make the match criteria properly identify a unique record.

 

 

 

endusersoftware has written 27 articles