Tag Archives: data integration

Integration Tip: Synchronize Picklist Value Part 1

Different system has different way to manage the picklist values. For integration, it is important to understand how the picklist values are represented in API.

Here are some of the types of picklist values can be appeared as:

1. Value is represented by same value as front-end

For example in Salesforce, the “Ownership” picklist field in Account object:-

Uses the same label value via API as well:-

<select id="acc14" name="acc14" tabindex="13">
	<option value="">--None--</option>
	<option value="Public">Public</option>
	<option value="Private">Private</option>
	<option value="Subsidiary">Subsidiary</option>
	<option value="Other">Other</option>

In this case, when integration process is synchronizing this field, we could just use the same label value as available.

2. Value is represented by ID via API

For example in JIRA, the standard “Priority” field picklist:-

Uses ID instead of the actual label seen in the user interface:-

<select name="priority" id="priority">
	<option value="1" class="imagebacked" style="background-image: url(/images/icons/priority_blocker.gif);">Blocker</option>
	<option value="2" class="imagebacked" style="background-image: url(/images/icons/priority_critical.gif);">Critical</option>
	<option value="3" class="imagebacked" style="background-image: url(/images/icons/priority_major.gif);" selected="">Major</option>
	<option value="4" class="imagebacked" style="background-image: url(/images/icons/priority_minor.gif);">Minor</option>
	<option value="5" class="imagebacked" style="background-image: url(/images/icons/priority_trivial.gif);">Trivial</option>

From the example above, we can see that:-

 Priority Label  Priority ID
 Blocker  1
 Critical  2
 Major  3
 Minor  4
 Trivial  5

For this type of picklist values, we can synchronize this field by maintaining a reference table in the integration process which reflects the actual picklist value and its corresponding ID.

As such, be sure to check the type of picklist involved in your integration process and determine which approach to use instead prior to working on the field mapping.



Magento Integration: Product Type

Magento supports a few product types. Each product type has different behavior. This is important when you design the product synchronisation process.

For example, if you are integrating Salesforce and Magento in the following scenario:

Magento Order > Creates > Salesforce Opportunity

If one of the order line items is a bundle product, you may get more than one line item returned from the API.

Order item from UI

XML data from API

The bundle product and all the associated items will be listed in the API. It is important to filter the list before you push it to Salesforce.

Want to integrate your applications, don’t know where to start?

Need to integrate some systems for your business?  Not sure how much it will cost or where to start?

Our integration planning guide has some information on how to plan your integration.  Maybe you want to get a quote before you plan, to determine your ROI and decide if you want to take it further.

How do you go about that?  What information do you need to provide a company like WDCi to enable them to give you a quote for your integration?

The key is in describing your use-case(s) and your business objective(s).

Here are some simple tips:

1. Describe your business process that you want automated – avoid any technical ideas or data level detail (that can come later).  Some simple business processes (use-cases) might be:

  • When I change my Sales Opportunity to closed/won, I’d like an invoice to be created automatically in my financial system. When the invoice is paid, I’d like visibility of that payment in my sales system OR
  • When I add certain tasks to my projects (in my project management system), I’d like these tasks to generate bug issues in my issue tracking system.  When the bugs are updated or completed, I’d like those updates to be reflected back in the project management tool – providing visibility to the PM. OR
  • I need to synchronise my Customer data across our CRM, ERP and Sales Automation systems.  When a Customer is updated in one of them, I want that information to be reflected in the other two systems.

2. If you understand the process flow and sequence of events, draw that up, it will help you flesh out how it really works.  Your drawing could be just a few simple boxes and arrows, as long as it describes the flow you expect.   If you don’t understand the flow, focus on the high level detail of describing the process in plain language.

3. Identify the systems you want integrated, what version are you running?

4. Contact us

If you focus on the applications involved and the business process you want to automate between these applications – we’ll take it from there, work with you and provide a quick quote for your review.

About to integrate existing systems, how’s the quality of your data?

When we kickoff our integration projects we run through a checklist with the client.

If they have been running the systems for a period of time, we always review the quality of the data, we call it a Data Quality Assessment (someone in marketing came up with that, not me, ok).

Usually the client asks:

Why do I need a Data Quality Assessment?

Consider this:

  1. Have you been using the systems involved for some time?
  2. Have you tried to keep them in sync manually? For example: Create Company ABC in both Salesforce and RightNow manually.

If you have, then our advice is to assess the quality of the data before investing in integrating the systems. If the data isn’t cleaned now, we’ll increase the problem once the systems are integrated and bad data is being synchronized.

What’s involved in a Data Quality Assessment?

This involves profiling your data to review if the data is ‘clean‘ , ie do you have excessive duplication and can we merge records to clean this quickly.

What if I don’t clean the data?

Consider the following example:

1) The process for Account/Contact creation in Salesforce is a manual process done using the information in Right Now:

Click to view full picture

2) Given the manual data entry it’s possible for the following to exist:

Click to view full picture

3) When we integrate we will end up with RightNow Case and Opportunity data linked to the wrong Salesforce Accounts.

This is what would occur after running the integration process on the above data:

Click to view full picture

Looks messy right? Definitely something for you to think about before connecting the systems. IMO some type spent up-front will save a lot of pain and $ in the long-term.

It would be like eating health food for a week.

TIM’s Tip: CSV integration

TIM here again – with another Tip, hope it’s helpful.

Boss: TIM, I need you to integrate CSV to Salesforce.
TIM: CSV integration? How hard can that be? I can do it in one day.

A day after…

Boss: TIM, have you completed that CSV integration?
TIM: Hmmm, Boss, I need more time to do this…  (credibility completely shot at this point)

You may be thinking like me. CSV import? How hard can it be. Well it depends, not all CSV imports can be uploaded easily.  Let me give you an example:

1) David owns a company that sells power tools
2) He would like to export sales from his home grown accounting system and import them into Salesforce

This is what the CSV export looks like:

In this scenario, you may need an advanced tool such as Boomi to work out:

  • Line 1 to 4 is Order #1 and Line 5 to 9 is Order #2
  • Order #1 has only 1 item. Order #2 has 3 items

A lot of people think that CSV integration is simple and it’s just data/field mapping – I wish.

Apart from using the right tool, you also need to have the right method of doing it. Here’s a few things I’ve worked out that you need to know:

  1. How the CSV file will be provided? Email? FTP? Read from a local folder?
  2. How to handle rejected data? Email or write into a file?
  3. Understand what objects are involved during the CSV integration.
  4. Understand all mandatory fields in all related objects.
    • If the column is empty in the CSV, do we use a default value or write those records into a file?
  5. Understand the data format for all fields. For example, the date format for the target system may be yyyy-MM-dd’T’HH:mm but your CSV date format is yyyyMMdd HHmm.
  6. Understand the data constraints of each field. For example, First Name field can only accept 100 characters.
  7. Understand the data type of the field. For example, you may need to use a cross reference table for a select list.

Quite a bit to think about, I definitely need a Pina Colada now.

TIM’s Tip: bi-directional data integration

TIM here again – with another Tip, hope it’s helpful.

In my experience integrating various SaaS applications such as Salesforce, Netsuite, Parature, RightNow, JIRA, etc,  bi-directional integration is a popular requirement.

So, why use bi-directional integration? Consider a bi-directional update between the Salesforce and RightNow Account Objects as an example:

  • When the record in Salesforce is updated, the linked record in RightNow will also be updated  ie it works both ways.

Now, this isn’t ideal for all systems and mostly suitable for real-time integration. There are a few things to be aware of:

  • You need a mechanism to stop an endless update loop. ie:
  1. A Record updated in Salesforce triggers the integration to update the record in RightNow.
  2. RightNow’s updated record will trigger the integration to update Salesforce’s Record and on and on – you get the idea.
  • In this scenario you should define which fields/data is the Master.
  • Make sure you are checking for timestamps to avoid the scenario where out of date data will update what was the correct data.

Time for a Pina Colada now I think.

How to integrate with MYOB Accounting

MYOB Accounting Plus and Premier use an underlying data file to hold the records.

In the current versions this is not a relational databae, making traiditional integration difficult.

MYOB have over the years released a series of ODBC drivers. These have allowed programs such as Word and Excel to extract data and in some cases, insert data to MYOB. WDCi have taken this ODBC driver and created connectors for several integration platforms.

These connectors enable the integration of MYOB into nearly any platform or application, whether hosted or on-premise.

With this framework it is now cost effective to have an online eCommerce site integrated directly with MYOB to post order or invoice data and extract product data. Alternatively you can connect your CRM or ERP system into your MYOB accounting system.

The main objects that are typically integrated are:

Company and individual contact information

Product (Item) information

Opportunity and Sales data to an Orders/Invoices within MYOB

Shopping cart invoice and payment replication to MYOB

Anything in MYOB can be exposed and integrated to a vast array of applications, once you know how.  For more information contact us or check out our ReadyMade solutions for pre-built MYOB integration solutions.