Six Stars

Oracle DB Table Columns to Salesforce Rows

Hello Everyone,

 

I am stuck with one of the design problem in one of the Talend job. Any help or suggestion is welcome.

 

Situation:

 

the source table has multiple columns with value Yes or No, however the column header itself map to multiple fields on the salesforce object. I am not sure how to resolve this problem. the table below shows the true representation of the problem:

 

For Example : PURPOSE_A_POST is a column in the DB but this column itself break into two parts i.e. Purpose A and POST map to Purpose field in Salesforce and Channel field respectively.

 

Consent_Flow.JPG

 

please help

 

A

  • Talend Integration Cloud
1 ACCEPTED SOLUTION

Accepted Solutions
Nine Stars TRF
Nine Stars

Re: Oracle DB Table Columns to Salesforce Rows

As said, nothing magic, you need to use tSplitRow to split input records and populate the desired fields.

Here is an example (limited to the first 4 fields, you need to complete with others).

The job looks like this (tFixedFlowInput just for the test):

Capture.PNG

tSplitRow is used to generate 1 record per field (4 records in this example, you need to continue with the others 16).

Here is the tSplitRow component configuration with the corresponding schema:

Capture.PNG

And here is the result:

Capture.PNG

Hope this helps.


TRF
8 REPLIES
Nine Stars TRF
Nine Stars

Re: Oracle DB Table Columns to Salesforce Rows

Hi,

If I understand your question, Salesforce fields Purpose and Channel value depend on which source field is set to "Y". Right?

If so, I'm afraid there is no magic solution for such a case, you need to do it by yourself.

The challenge is to identify the better solution (probably using tMap or tJavaRow to construct the appropriate logic).

Let me know.


TRF
Six Stars

Re: Oracle DB Table Columns to Salesforce Rows

Hey TRF,

 

Thanks for the prompt reply, let me explain this with one more example:-

 

Input File:

 

PURPOSE_A_POSTPURPOSE_A_EMAILPURPOSE_A_PHONEPURPOSE_A_SMS
YNYN

 

In Salesforce this should be the output, multiple records based on the above

 

1.

Purpose <Picklist>   :  Appeals & Fundraising Activities

Channel <Picklist>   :  Post

Status   <Picklist>    :  Opt-in

 

2.

Purpose <Picklist>   :  Appeals & Fundraising Activities

Channel <Picklist>   :  Email

Status   <Picklist>    :  Opt-out

 

3.

Purpose <Picklist>   :  Appeals & Fundraising Activities

Channel <Picklist>   :  Phone

Status   <Picklist>    :  Opt-in

 

4.

Purpose <Picklist>   :  Appeals & Fundraising Activities

Channel <Picklist>   :  SMS

Status   <Picklist>    :  Opt-out

 

does it make sense?

Nine Stars TRF
Nine Stars

Re: Oracle DB Table Columns to Salesforce Rows

As said, nothing magic, you need to use tSplitRow to split input records and populate the desired fields.

Here is an example (limited to the first 4 fields, you need to complete with others).

The job looks like this (tFixedFlowInput just for the test):

Capture.PNG

tSplitRow is used to generate 1 record per field (4 records in this example, you need to continue with the others 16).

Here is the tSplitRow component configuration with the corresponding schema:

Capture.PNG

And here is the result:

Capture.PNG

Hope this helps.


TRF
Six Stars

Re: Oracle DB Table Columns to Salesforce Rows

TRF,

 

I cant believe you went to two extra miles to create a job for me, appreciate your time and efforts on this - THANK-YOU. I believe i am pretty near to the solution however one last thing. My input is a Database and when i run the job it pulled 4875 rows, however there are only 108 rows in the database table. Can you suggest how i can restrict these rows? I am pasting the screen shots below:

 

Secondly, if i want to pull a primary key field, can i pull that as well in the tsplit row?

 

 

 

 

Nine Stars TRF
Nine Stars

Re: Oracle DB Table Columns to Salesforce Rows

You're welcome, you'll thank me when your case will be solved!

 

1st, as you use a standard tXxxxxInput (Oracle or MS SQL or other) component, you can be sure that if you have 4875 rows in the flows it's because there is at least 4875 records in the database.

I suggest you to verify which table is used be the component or how the select is written.

 

2nd, yes you can pull all the fields you need but of course, as 1 input record will produce n output records, at the end the values will not be unique anymore. If you want to use it as an external Id on Salesforce side, it should be a problem. In such a case, you can make it unique again if you concatenate an other value with it (for example, let's say PK is this field for both input and output, you can have an expression like row5.PK + "-" + row5.Channel in a tMap just after the tSplitRow component).

Is that what you're looking for?


TRF
Six Stars

Re: Oracle DB Table Columns to Salesforce Rows

TRF,

 

Please take a bow, I am marking this as accepted solution. In case i run into problems, i will contact again.

 

btw where are you based?

 

A

Six Stars

Re: Oracle DB Table Columns to Salesforce Rows

Hey TRF,

 

Just wondering, do i need to use another tsplit for Purpose B,C,D,E respectively or can i map in a single tsplit?

Nine Stars TRF
Nine Stars

Re: Oracle DB Table Columns to Salesforce Rows

I suggest you to use a single tSplitRow for this case.

TRF