upsert into salesforce (lookup)

Anonymous · ‎2018-07-09

Hi!!

I'm trying to upsert data into 'Asset' object in Salesforce.

I want to upsert the field ExternalId, for that I'm using the AccountId (lookup) like key column.

The problem is the next one:

failed to release the resource in tSalesforceOutput_1 :[InvalidFieldFault [ApiQueryFault [ApiFault exceptionCode='INVALID_FIELD'
exceptionMessage='Field name provided, Account does not match an External ID for Asset'
extendedErrorDetails='{[0]}'
]
row='-1'
column='-1'
]
]
Exception in component tSalesforceOutput_1 (Ucav_Sfdc_Ps_Id_Titulaciones_Prueba)
java.io.IOException: [InvalidFieldFault [ApiQueryFault [ApiFault exceptionCode='INVALID_FIELD'
exceptionMessage='Field name provided, Account does not match an External ID for Asset'
extendedErrorDetails='{[0]}'
]
row='-1'
column='-1'
]
]

at org.talend.components.salesforce.runtime.SalesforceWriter.doUpsert(SalesforceWriter.java:458)
at org.talend.components.salesforce.runtime.SalesforceWriter.upsert(SalesforceWriter.java:415)
at org.talend.components.salesforce.runtime.SalesforceWriter.write(SalesforceWriter.java:192)
at ucav_2.ucav_sfdc_ps_id_titulaciones_prueba_0_1.Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.tFileInputDelimited_1Process(Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.java:2354)
at ucav_2.ucav_sfdc_ps_id_titulaciones_prueba_0_1.Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.tWarn_7Process(Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.java:4705)
at ucav_2.ucav_sfdc_ps_id_titulaciones_prueba_0_1.Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.tFTPGet_1Process(Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.java:4573)
at ucav_2.ucav_sfdc_ps_id_titulaciones_prueba_0_1.Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.tSetGlobalVar_1Process(Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.java:9331)
at ucav_2.ucav_sfdc_ps_id_titulaciones_prueba_0_1.Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.runJobInTOS(Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.java:9846)
at ucav_2.ucav_sfdc_ps_id_titulaciones_prueba_0_1.Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.main(Ucav_Sfdc_Ps_Id_Titulaciones_Prueba.java:9415)
Caused by: [InvalidFieldFault [ApiQueryFault [ApiFault exceptionCode='INVALID_FIELD'
exceptionMessage='Field name provided, Account does not match an External ID for Asset'
extendedErrorDetails='{[0]}'
]
row='-1'
column='-1'
]
]

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at java.lang.Class.newInstance(Unknown Source)
at com.sforce.ws.bind.TypeMapper.readSingle(TypeMapper.java:673)
at com.sforce.ws.bind.TypeMapper.readObject(TypeMapper.java:556)
at com.sforce.ws.transport.SoapConnection.parseDetail(SoapConnection.java:236)
at com.sforce.ws.transport.SoapConnection.createException(SoapConnection.java:210)
at com.sforce.ws.transport.SoapConnection.receive(SoapConnection.java:156)
at com.sforce.ws.transport.SoapConnection.send(SoapConnection.java:99)

at com.sforce.soap.partner.PartnerConnection.upsert(PartnerConnection.java:1151)
at org.talend.components.salesforce.runtime.SalesforceWriter.doUpsert(SalesforceWriter.java:438)
... 8 more

I've try to write the lookup component in the advances settings and I've try to change the name of the field as well ( trying with the api name, target name, child name....), but it doesn't work....

Anyone knows what I'm forgetting??

I attach screenshot.

Thank you in advance.

Kind regards,

Jorge

Anonymous · ‎2018-07-10

Hello,

Have you tried to declare a field as an External Id used by salesforce? Could you please share your tSalesforceOutput settings with schema?

Best regards

Sabrina

Anonymous · ‎2018-07-11

Hi @xdshi

Thank for your asnwer.

I attach the tOutSalesforce settings and the schema.

What do you mean when you say to declarate the field like a extenal Id used by salesforce? for my AccountId field?

Thx

schema.PNG
q1e.PNG

manodwhb · ‎2018-07-13

@Moem, Can you confirm that AccountId and Id_Externo__C fields are there in Asset ?

tjohn · ‎2018-07-13

hi

- as suggested by manodwhb , please check api name for Account field

- then to use upsert mode, check this field as a key

Thanks

tjc

Anonymous · ‎2018-07-14

yes @manodwhb, they are,

In fact in the tSalesforceOutput I got in Talend these fields when I configure the tSalesforceOutput with the Asset objet

Thx

Jorge

Anonymous · ‎2018-07-14

hi @tjclearner

I tried to do that with the API name first, didn't work and I tried with lookup mode (using the label name, API name...) and didn`t work neither.

Thx

Jorge

tjohn · ‎2018-07-14

Hi jchamorro
From salesforce side, make sure this field is declared as an external id
Thanks

TRF · ‎2018-07-14

@@Moe when you want to upsert records, you have only 2 choices:

- use the record Id for the "Upsert Key Column" (that means you know it and the record will be updated)

- use a field declared as an external Id (that means the record will be updated if it already exists and inserted if it doesn't)

Of course, the fields you try to manipulate from the job, must be visible for the user used for the salesforce connection (check Field-Level Security in salesforce Setup).

That's for the basics.

Now, what are you trying to do?

It seems you want to update existing records with a field which is probably null before that.

So, the 1rst step is to get the Ids from existing Assets using a tSalesforceInput. This field will be the key for the upsert operation but you can also change for an update as soon you are sure the records exist.

At the same time you need also to get a field which allow you to join these records with those from your input CSV file. If this field doesn't exists, I can't figure out how you expect to retrieve the record to be updated.

From your design, it seems you get records from the Contact object then you move the Contact Ids to the Asset AccountId field. This is not possible as Contact and Account are not the same objects. What you can do is to get AccoundId field from Contact to populate Asset AccountId field but if you just want to update the ID_Externo__c field, you don't nedd the AccountId.

At this stage I can't give you a solution as the objective and the way to get the right data are not well explained, in particular what's in the input file and why the use of Contact object.

Anonymous · ‎2018-07-16

@TRF

Hi!!

sorry maybe I didnt explain well my problem.

My csv has these fields: EMPLID and Id_Extenal.

EMPLID is an account field (NIF), which is already in Salesforce for each account. Now what I have to do is to upsert Id_external, but in another object, in Asset.

Asset is related with Account, so what I want to do is upsert id_external (asset) using EMPLID (account). In every asset record I have AccountId field (lookup). So for that I'm using EMPLID from the csv to find AccountId form Account, and use this one like Key to upsert the Id_external in asset.

CSV : EMPLID|ID_EXTERNAL

MAP: EMPLID

ID_EXTERNAL ID_EXTERNAL

(csv file)

-------------

AcountId AccountId (key, already fill)

EMPLID

(Account object) (Asset object)

So, my problem is that the upsert doesn't work because supposedly the field AccountId doesn't exit in Asset or is not an external id, but it exist and my profile is able to edit it.

Java

SOAP

SQL

Talend Data Integration

v7.x