DX: Export/import products with prices

Question

I'm using the sfdx force:data:tree:export (and import) commands to grab some sample data from by sandbox to use with scratch orgs.

I want to export some products to use with testing. The products need to have a price as well. Here comes the problem:

The Pricebook Entry is a child of both Pricebook2 and Product2, and I need the relationships to be established.

There's the --plan parameter, that works for a parent-child relationship, so I can save PricebookEntry + the parent Pricebook2, but I need both references, it's a parent+parent - child relationship.

Any ideas?

Answer 1

This is my slightly less manual approach during the data import, but fundamentally the same as specimen's.

Before you begin

Replace the dataImportApi.js with this slightly modified rendition: dataImportApi.js

Be sure to backup the existing version. The revised file includes a feature to lookup existing records in the scratch org during the import.

Location: \Users\YOUR_USER\AppData\Local\sfdx\client\node_modules\salesforce-alm\dist\lib\data\dataImportApi.js

This also includes the fix specimen pointed out.

1. Build your exports

sfdx force:data:tree:export --targetusername sandbox --query "SELECT IsActive,Name,ProductCode, (SELECT CurrencyIsoCode,IsActive,Pricebook2Id,Product2Id,UnitPrice,UseStandardPrice from pricebookentries) FROM Product2 where productcode in ('1004','1891','1088')" --outputdir .\data\ --plan

2. Add a Pricebook lookup file

Add the following file to your exrtact folder: standardPricebook-lookup.json. add the following content:

{
    "records": [
        {
            "attributes": {
                "referenceId": "Pricebook2Standard",
                "type": "Pricebook2",
                "query":"IsStandard=true"
            }
        }
    ]
}

3. Edit your plan file and include the lookup file

In the plan file, add reference to the price book lookup file:

[
    {
        "sobject": "Pricebook2",
        "saveRefs": true,
        "resolveRefs": true,
        "files": [
            "standardPricebook-lookup.json"
        ]
    },
    {
        "sobject": "Product2",
        ...

4. Update your PricebookEntrys extract with a referenceId of the standard price book

For all entries, replace the Pricebook2Id with the reference variable we looked up.

{
    "records": [
        {
            "attributes": {
                "type": "PricebookEntry",
                "referenceId": "PricebookEntryRef2"
            },
            "Pricebook2Id": "@Pricebook2Standard1",
            "UnitPrice": 200,
            "IsActive": true,
            "UseStandardPrice": false,
            "Product2Id": "@Product2Ref5"
        },
        {
            "attributes": {
                "type": "PricebookEntry",
                "referenceId": "PricebookEntryRef4"
            },
            "Pricebook2Id": "@Pricebook2Standard1",
            "UnitPrice": 24,

Note that the reference id must be prefixed with "@" and include a record number. In our case, it will allways be "1" as there is only one standard price book in any SFDC org.

Load the data into a scratch org

Lastly, run the import to the org with the plan command.

sfdx force:data:tree:import --plan .\data\Product2-PricebookEntry-plan.json

Other uses

The lookup should work for any other purpose. What triggers the lookup instead of an insert is the addition of the query value. You may only include exactly 1 record per file.

{
  "attributes": {
    "referenceId": "Pricebook2Standard",
    "type": "Pricebook2",
    **"query":"IsStandard=true"**
}

The records returned will include the the record Id and add the Ids to the value map, using the following format: @referenceId#

Answer 2

This is my current workaround. It's far from perfect. Once you done it the first time, the only thing you need to do when creating new scatch orgs is to create a standard price book and replace the Id, then upload.

Fix bug in sfdx command

My SFDX command had some trouble with fields with numbers (Product2, Pricebook2) in them, so I had to fix it. It's just to replace a line in a file.

Open the file

\Users\YOUR_USER\AppData\Local\sfdx\client\node_modules\salesforce-alm\dist\lib\data\dataImportApi.js 

and replace the line starting with const jsonRefRegex with this:

const jsonRefRegex = /[.]*["|'][A-Z0-9_]*["|'][ ]*:[ ]*["|']@([A-Z0-9_]*)["|'][.]*/igm;

Export some products and prices (adjust the SOQL to your liking):

sfdx force:data:tree:export --targetusername sandbox --query "SELECT IsActive,Name,ProductCode, (SELECT CurrencyIsoCode,IsActive,Pricebook2Id,Product2Id,UnitPrice,UseStandardPrice from pricebookentries) FROM Product2 where productcode in ('1004','1891','1088')" --outputdir .\data\ --plan

Create a standard price book in the scratch org

So first create a scratch org, then open it and create a dummy product. This should create the Standard Price Book. Grab it's Id.

Add the following to each entry (use your own Id):

"Pricebook2Id": "01s3E000000XkCqQAK",

in the PriceBookEntrys.json file. With some clever search-replace, this is a one-liner.

Upload test data into scratch org

sfdx force:data:tree:import --plan .\data\Product2-PricebookEntry-plan.json