function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jeremy SwartwoodJeremy Swartwood 

How to UPSERT with Bulk Api 2.0 into Junction object using the 2 master detail records as the externalIdFieldName

I can easily bulk load INSERT into a junction object without issues.

However, I want to (UPSERT) with the Bulk API 2.0, into a many-to-many JUNCTION object (Custom object with 2 master detail records).

However, it appears that I need to use a externalIdFieldName that is WITHIN the JUNCTION object.  It doesn't sound like I can use the Master Detail record references.

What are some suggestions on how to upsert into a junction object using the Bulk Api 2.0?

Some thoughts or work-arounds that are not good are:
1. Create a new field on the JUNCTION object that is an External ID, and is simply a concatonated STRING from both of the "External ID"s from the other two objects
2. First download all the ID's for the Master records on both sides of the many-to-many Junction object, and then use those to "upsert" if needed.

Any help is apprecitaed, Salesforce documentation on the Bulk Api 2.0 is lacking and misses any notes about JUNCTION objects.

 
Habib RajaHabib Raja

To UPSERT with Bulk API 2.0 into a Junction object using two master-detail records as the externalIdFieldName, you can follow these steps:
Create a CSV file with the records you want to upsert. The CSV file should contain the following columns:
Master detail record 1 ID (External ID)
Master detail record 2 ID (External ID)
Junction object field 1
Junction object field 2
Junction object field 3 (optional)
Use the Salesforce Data Loader or any other tool that supports Bulk API 2.0 to upsert the records from the CSV file.
In the upsert request, set the externalIdFieldName to the two master-detail record IDs separated by a period. For example:
markdownCopy code
upsert myJunctionObject__c externalIdFieldName=Master_Detail_Record_1__r.External_Id__c.Master_Detail_Record_2__r.External_Id__c csvfile=myfile.csv
Submit the upsert request and wait for the response.
Check the response for any errors or warnings. If there are any errors, fix them and resubmit the request.
Once the upsert request is successful, the junction object records will be created or updated based on the values in the CSV file and the matching master-detail record IDs.
I hope this helps! Let me know if you have any further questions.
Jeremy SwartwoodJeremy Swartwood
Thank you, I'm a bit confused still.

Perhaps if you could give me an example for the CSV file column names, and the externalIdFieldName's assuming a junction table between say Account and Contact as an example.  It appear that you are seperating the 2 fields in the externalIdFieldName by a period?  

I won't have the internal Salesforce ID's in my CSV file so I won't be able to provide the internal ID for the Account.  What I have is my own Unique ID.

Here is the Master Detail record of the Account link/reference

Field Label: Account
Field Name: Account__c
Data Type: Master-Detail(Account)

I see you put (ExternalID) on the master detail record, I'll add that to see if that works. Maybe that is my only issue?

However, I have tried Account__r.MyUniqueExternalIdField__c and it just says:

InvalidJob : Field name provided, Account__r.MyUniqueExternalIdField__c does not match an External ID for JunctionTableName__c
Jeremy SwartwoodJeremy Swartwood
Update: It doesn't appear I can make the Master Detail Record field an "External ID".  The checkbox is not present.
liza keysliza keys
When using Bulk API 2.0 to upsert records into a many-to-many junction object, you need to specify an external ID field in the junction object to identify unique records. Unfortunately, you cannot use the external IDs from the master detail records to upsert records into the junction object.
One workaround is to create a new field in the junction object that concatenates the external IDs from both master records. You can then use this concatenated field as the external ID field for upserting records into the junction object. However, this approach may not be ideal if the concatenated field contains a lot of data or if the external IDs change frequently.

Another approach is to first download the IDs of the master records on both sides of the many-to-many junction object. You can then use these IDs to perform an upsert operation on the junction object. This approach may be more reliable and efficient, (https://techmiget.com/) but it requires additional steps to download the master record IDs and map them to the junction object records.

Overall, there is no one-size-fits-all solution for upserting records into a many-to-many junction object using Bulk API 2.0. You will need to evaluate your specific use case and choose the approach that works best for you. It's also worth noting that Salesforce documentation on Bulk API 2.0 is sometimes lacking, so it's always a good idea to seek help from the Salesforce community or support if you run into issues.
smith havelsmith havel

While the Salesforce documentation may lack specific notes on Junction objects in a bulk API 2.0 context, experimenting with different approaches and leveraging the Salesforce developer communities can provide guidance.

When upserting into a many-to-many junction object using the Bulk API 2.0, it is absolutely necessary to use an external ID field within the junction object itself. A few tips to consider:

Create a new unique external ID field on the junction object: You can combine external IDs from both master detail records and store them in this field. This will enable you to perform upsert operations based on this field.

Use a two-step approach: first retrieve the IDs of the master records on both sides of the junction object, and then use those IDs to perform the upsert operation. This ensures that you have the necessary references to enter the junction object. For more information to visit: theshadowapk (https://theshadowapk.com/)
Anita MiamiAnita Miami
To UPSERT (insert or update) records into a junction object using Bulk API 2.0 with two master-detail records as the externalIdFieldName, you can follow these steps:

1. Understand the Junction Object: 
A junction object is an object that connects two master-detail relationships, creating a many-to-many relationship between two objects. To UPSERT records into the junction object, you'll need to know the API names of the two master-detail fields on the junction object.

2. Prepare the Data:
Make sure you have the data ready in a format that can be processed by Bulk API 2.0. You can use CSV files, JSON files, or XML files to store the data. Ensure that each row of data includes the external IDs of the two master-detail records and any additional fields required by the junction object.

3. Create an External ID Field on the Junction Object:
You need to create an external ID field on the junction object to uniquely identify each record. The external ID field should be a combination of the external IDs of the two master-detail records. This field will be used as the externalIdFieldName for the UPSERT operation.

4. Use the Bulk API 2.0:
Use the Bulk API 2.0 to process the data and UPSERT the records into the junction object. You can use tools like Salesforce Data Loader, Workbench, or any custom implementation to interact with the Bulk API.

Sample Request Body for MyLabCorp (https://www.mylabcorp.us/)Operation:
```json
[
    {
        "External_Id_Field_Name__c": "External_Id_Master_Detail1_External_Id_Master_Detail2",
        "Master_Detail1__r.External_Id__c": "External_Id_Master_Detail1_Value",
        "Master_Detail2__r.External_Id__c": "External_Id_Master_Detail2_Value",
        "Additional_Field1__c": "Value1",
        "Additional_Field2__c": "Value2"
    },
    // Add more records as needed
]
```

5. Process the Response:
The Bulk API response will indicate the success or failure of each record UPSERT operation. You should handle any errors and take appropriate actions to correct the data if needed.

Remember to test your implementation with a smaller dataset before processing large volumes of data to ensure it works as expected.

By following these steps, you can UPSERT records into a junction object using Bulk API 2.0 with the two master-detail records' external IDs as the externalIdFieldName, establishing many-to-many relationships effectively.
taralextaralex
You can pass the Id field to the Upsert operation as the ext. id field and it will always do Update (except when Id is empty when it will create a new record).
XL-Connector (https://www.xappex.com/knowledge-base/installing-enabler/) app lets you match by any text field on a related record in both Update and Insert operations, so that can be an option too.
mike smith 82112mike smith 82112
In Salesforce, upserting into a junction object using the Bulk API 2.0 can be a bit tricky because you need to specify an external ID field on the junction object itself. You're correct that you cannot directly use the external IDs of the master-detail records to upsert into the junction object. Your suggested workarounds are on the right track. Here's a more detailed explanation and alternative approach:

Create an External ID Field on the Junction Object:

One way to handle this is by creating a custom text field on the junction object. This field should serve as the external ID field, and you can use a formula field to concatenate the external IDs of the master-detail records. This approach, while not ideal, works for upsert operations in the Bulk API.
Download IDs of Master Records and Use Them for Upsert:

To use the Bulk API for upserting into the junction object, you would first need to download the IDs of the master records (both sides of the relationship) that you want to associate with the junction object. iserted in  https://moverinabudhabi.com/
Once you have those IDs, you can use them to create CSV data that includes the junction object's external ID field and the master record IDs.
This CSV data can then be used as input for the Bulk API upsert operation, where the external ID field on the junction object is used as the external ID field for upserting.
Here's a step-by-step guide for the second approach:

Query for the Master Record IDs:

Use Salesforce SOQL queries to retrieve the IDs of the master records that you want to associate with the junction object.
Create CSV Data for Upsert:

Create a CSV file with the following columns:
External ID field (on the junction object)
Master Record 1 ID
Master Record 2 ID
Populate the CSV file with the external IDs and the corresponding master record IDs.
Perform the Bulk API Upsert:

Use the Bulk API 2.0 to perform an upsert operation on the junction object using the CSV data you created.
Specify the external ID field of the junction object as the external ID field for upsert.
Monitor the Bulk Job:

Monitor the progress and status of the Bulk API job to ensure that your records are upserted successfully.
By following this approach, you can effectively upsert records into a many-to-many junction object using the Bulk API 2.0, even though the external ID field needs to be on the junction object itself. This approach allows you to maintain the relationships between the master records and the junction object.