Category Archives: Uncategorized

Wedid: Netsuite to CSV


Purpose Migrate data from Netsuite to CSV file format
Applications Netsuite
Versions Netsuite
Tool XML and CSV Export
Information Customer
General Ledger
Data Formats XML and CSV
Volumes < 1,000,000 records
Error Handling Nil, data was exported to required format only
Complexity Simple


Boomi: Boomi Sub-Process Call Execution Behavior

In one of the previous post, we talked about the option of enabling a Boomi sub process call to be executed in a asynchronous manner. However, do you know that this is actually also depending on the Start Shape Option of the sub process?

Start Shape Option Support Synchronous Execution Support Asynchronous Execution
Data Passthrough Yes No
No Data Yes Yes
Connector Call Yes Yes


S-Docs: negative currency

Scenario: Creating S doc templates and pulls out value with negative number. ie: -400.

When we pull out number fields, S Doc will define number format, ie: {{!Opportunity.amount #,###.##}}. Using this format will return value in a form of 1,234.00 But if the value is in negative form, it will return as (1,234.00).

So how do we make S Doc show the negative sign?


1) Create a custom NUMBER formula field.

2) Copy value of the field to display. ie: UnitPrice

negative value formula


3) Pull out the text formula in S Doc template.

This method is applicable to both positive and negative value. 

Talend: Processing Large Data when Using tUniqRow

In Talend, tUniqRow is a useful component which allows you to filter out only the distinct unique row from a set of data. While using this, you may hit into Java Heap Space issue (java.lang.OutOfMemoryError) if the data to be processed is very large (millions of rows).

One possible solution to this is by increasing the JVM so you can process more records.


However, there is a limit to this setting depending on the Physical Memory available in the system. Setting it too high will cause performance issue to the system.


Another way is to use the Use of disk setting in the tUniqRow advanced settings. By using this setting, data will be stored temporarily in the local disk drive and Talend will process the data using the files instead.


This approach is somehow more efficient and will prevent Talend from using excessive system memory to process the data.






Salesforce Tip: Monitoring Time-Based Workflow

Ever wonder if there is a way to monitor the outgoing email queue triggered from the Time-Dependent Workflow?

1. Go to Setup > Administrator Setup > Monitoring > Time-Based Workflow
2. After that you will see a search page. You can do a search of the email queue by filters or search all of them. You will get the list of email queue from the search result.
3. Another thing you can do here is to delete any email queue that you want so that it will not be sent out.

Integrating Microsoft Dynamics Great Plains with eConnect

eConnect is a medium that allows integration between Microsoft Dynamics Great Plains (Dynamics GP) with other applications. The following are some information related to eConnect:

  • provide programmatic access to Dynamics GP data
  • uses XML documents for data communication
  • implemented as a set of database stored procedures installed on the Dynamics GP  database server
  • the stored procedures ensure that the data import are valid and compliant with Dynamics GP

For detailed information about eConnect:

In order to configure the integration between the applications, the eConnect connection needs to be established with the application’s underlying database – Microsoft SQL Server. eConnect stores the available objects that can be executed on. One should be able to retrieve/send data from/to Dynamics GP based on the supported objects.

Note that this list has the corresponding physical table name for the objects as well as the associated fields. For example:

The Cash Receipt object is referenced as table name: RM10201 within the database

In our work of integrating with Dynamics GP, most of the object queries are supported in eConnect. However, there were also times where eConnect does not support every object query and send method that we need. Hence, when there is a need for more advanced queries, one of the options is to:

1. insert a new row into eConnect_out_Setup and specify the table/trigger to use
2. create stored procedure to link with the records

To identify the corresponding table, see:

Understanding Salesforce Object Relationships

There are many skill variations when it comes to Salesforce system implementation and Salesforce integration. One of it requires an understanding on the object relationship within the application. It is not hard to comprehend the relationship once you can identify the data model that you are about to build. The following are some of the common object relationships that are used to define the data model:

1. One to One
2. One to Many/Many to One
3. Many to Many

One of the ways to get yourself equipped with the knowledge is to read through the Salesforce Fundamentals documentation. Well, that’s how I got to it while undertaking the Salesforce Certification – Developer. However, if you find that going through the materials a hassle, you can always refer to the following explanation on a quick run-down on how to build the data model.

One to One object relationship

One School can only have one Headmaster

This object relationship requires that one of the associated object to be unique.
– School (Master object)
– Headmaster (Detail/Child object)
– Create a unique field in Headmaster object
– Create workflow to copy the School id to the unique field for every record creation

One to Many/Many to One object relationship

One school can have many teachers

– School object
– Teacher object: School field (Lookup to School)

When you view the relationship from School, it’s ONE School to MANY Teachers
When you view the relationship from Teacher, it’s MANY Teachers to ONE School

Many to Many object relationship

Student can enrol in many subjects

– This is where junction object comes into play for this relationship
– Student Subject (junction object)
– Subject (Master object to Student Subject)
– Student (Master object to Student Subject)

This model explains that there are many students taking up many subjects and vice versa.

Hope this clears off any confusion you have with regards to the object relationships.

Furthermore, if you plan to perform a Salesforce migration with other applications and require to figure out the object relationship via API, you can simply generate the Salesforce WSDL of your current instance via:

Setup > App Setup > Develop > API > Generate WSDL

From there, you should be able to determine the definition of the object relationship. The following extensions explain on the references:

__c – in reference to the custom field id values

__r – in reference to the custom object relationship with the current object view

Tips: Retrieving the Product Quantity from the Magento API

Getting the product stock quantity in Magento is a bit tricky as you can’t get it from API call.

You need to use cataloginventory_stock_item.list API call. It has only one argument:
  • array products – list of products IDs or Skus
The array or products here means that you can request multiple product id in a request. Here’s a snippet of what the product quantity soap request looks like:

<sessionId xsi:type=”xsd:string”>59bc9730d567c9351e2507acb1d97bd0</sessionId>
<products xsi:type=”urn:ArrayOfString” soapenc:arrayType=”xsd:string[]”>


And the result return:
<item xsi:type=”ns1:catalogInventoryStockItemEntity”>
<product_id xsi:type=”xsd:string”>16</product_id>
<sku xsi:type=”xsd:string”>n2610</sku>
<qty xsi:type=”xsd:string”>996.0000</qty>
<is_in_stock xsi:type=”xsd:string”>1</is_in_stock>
<item xsi:type=”ns1:catalogInventoryStockItemEntity”>
<product_id xsi:type=”xsd:string”>17</product_id>
<sku xsi:type=”xsd:string”>bb8100</sku>
<qty xsi:type=”xsd:string”>797.0000</qty>
<is_in_stock xsi:type=”xsd:string”>1</is_in_stock>
<item xsi:type=”ns1:catalogInventoryStockItemEntity”>
<product_id xsi:type=”xsd:string”>18</product_id>
<sku xsi:type=”xsd:string”>sw810i</sku>
<qty xsi:type=”xsd:string”>989.0000</qty>
<is_in_stock xsi:type=”xsd:string”>1</is_in_stock>

Tips: Authenticating to Pearson LearningStudio with oAuth1.0

In the Pearson application there are 2 types of authentication methods, oAuth1.0 and oAuth2.0.

Pearson LearningStudio API supports both OAuth 1.0 and OAuth 2.0 authentication methods.

When building an integration to communicate with Pearson LearningStudio we used the OAuth 1.0 module. Here is an example of how to get the authentication working:

1) Prepare all the required credentials:

  1. request_url
  2. consumer_key
  3. application_id
  4. consumer_secret
  5. timestamp
  6. nonce

2) Generate the timestamp. You should convert current time to seconds.

Code snippet:

long timeStamp = new Date().getTime()/1000;

3) Generate nonce, a random alphanumeric string. You can only use numbers or letters, you can’t just use letters. This nonce needs to be unique per request. Any duplicate nonce value will be rejected. The nonce must not exceed 32 characters.

Code snippet:

Random nonceGenerator = new SecureRandom();
// Any number between 0 to 999999999
long nonce = nonceGenerator.nextInt(999999999);

4) Prepare the signatureBaseString.

Code snippet:

// Method such as GET or POST in upper case

String method = method.toUpperCase();

// Prepare the resource path and encode it

String resourcePath = “/courses/10000”;

String encodedResourcePath = URLEncoder.encode(resourcePath, “UTF8”);

// Make sure that this string is URL encoded. For example ‘=’ is encoded to %3D

String signatureBaseString = method + “&” + encodedResourcePath + “&application_id%3D” +

applicationID + “%26oauth_consumer_key%3D” + consumerKey + “%26oauth_nonce%3D” + nonce + “%26oauth_signature_method%3DCMAC-AES%26oauth_timestamp%3D” + timeStamp;

5) Prepare the signature by signing the signatureBaseString with the consumer secret.

6) Use Base64 to encode the signature to produce the encodedSignature.

7) Once it’s ready you can use it to build the X-Authroization header using the follow format:

String authHeader = “OAuth realm=\”” + baseURL + “\”,oauth_consumer_key=\”” + consumerKey + “\”,application_id=\”” +

applicationID + “\”, oauth_signature_method=\”CMAC-AES\”,oauth_timestamp=\”” + timeStamp + “\”,oauth_nonce=\”” + nonce + “\”,oauth_signature=\”” + encodedSignature + “\””;