Use of LISTMERGE and LISTMERGEPRIMARY functions in DataRaptor Formulas

In this article, let us see how to use the functions ‘LISTMERGE’ and ‘LISTMERGEPRIMARY’ in the DataRaptor formulas.

LISTMERGE function:

This function is used to merge any number of lists by combining their entries when the values of the specified keys match. 

To achieve this purpose, 

• the keys used as a matching criteria should be specified as a comma-separated list of quoted key names and

• the lists to be merged should be specified with the LIST function, separated by a comma.

If the lists contain ‘identically named nodes’ that contain different values, subsequent values overwrite earlier values in the result list. 

The final output contains all the keys from all the lists.

Example 1: Single matching key (‘Id’)

This example uses the LISTMERGE function with a single key.

Input:

{
  "CurrentAccounts": [
    {
      "Name": "Existing Account A",
      "Id": "0015i00000ecMumAAE",
      "BillingCity": "New York",
      "BillingCountry": "US"
    },
    {
      "Name": "Existing Account B",
      "Id": "0015i00000ecMumAAJ",
      "BillingCity": "Delhi",
      "Employees": 789
    }
  ],
  "ProspectiveAccounts": [
    {
      "Name": "New Account A",
      "Id": "0015i00000ecMumAAE",
      "BillingCity": "Paris",
      "Employees": 980
    },
    {
      "Name": "New Account B",
      "Id": "0015i00000ecMumAAJ",
      "BillingCity": "Chennai",
      "BillingCountry": "India"
    },
    {
      "Name": "New Account C",
      "Id": "0015i00000ecMumAAO",
      "BillingCity": "Bangalore"
    }
  ]
}

Formula:

LISTMERGE("Id", LIST(CurrentAccounts), LIST(ProspectiveAccounts))

Here, “Id” is the key based on which the lists ‘CurrentAccounts’ and ‘ProspectiveAccounts’ will be merged.

Formula Result Path:  FinalAccountsFromListMerge

Output:

If we observe the output, since we have used ‘Id’ as the matching criteria key,

• The accounts with the same ‘Id’ (0015i00000ecMumAAE, 0015i00000ecMumAAJ) in both the arrays ‘CurrentAccounts’ and ‘ProspectiveAccounts’ are merged into a single entry in the final output array.

• The nodes ‘Name’ and ‘BillingCity’ are present in both arrays (identically-named nodes), so in this case, the LISTMERGE function picks the node value from the last array, which is ‘ProspectiveAccounts’ in our example. So in the final output, we get the values as shown below,

Id	Name	BillingCity
0015i00000ecMumAAE	New Account A	Paris
0015i00000ecMumAAJ	New Account B	Chennai

• For the remaining (non-matching) nodes, ‘BillingCountry’ and ‘Employees’, the values are picked up from the respective array elements. Hence, we get the output nodes corresponding to the Ids, as shown below.

Id	Employees	BillingCountry
0015i00000ecMumAAE	980	US
0015i00000ecMumAAJ	789	India

• Finally, there is an extra element in the array ‘ProspectiveAccounts’ with the ‘Id’ : ‘0015i00000ecMumAAO’ and all the node values for this element are completely picked from the same array since there is no such element with the same ‘Id’ value in the first array ‘CurrentAccounts’.

Example 2: Multiple matching keys (‘Id’ and ‘Name’)

This example uses the LISTMERGE function with two keys.

Input:

{
  "CurrentAccounts": [
    {
      "Name": "Account A",
      "Id": "0015i00000ecMumAAE",
      "BillingCity": "New York",
      "BillingCountry": "US"
    },
    {
      "Name": "Existing Account B",
      "Id": "0015i00000ecMumAAJ",
      "BillingCity": "Delhi",
      "Employees": 789
    }
  ],
  "ProspectiveAccounts": [
    {
      "Name": "Account A",
      "Id": "0015i00000ecMumAAE",
      "BillingCity": "Paris",
      "Employees": 980
    },
    {
      "Name": "New Account B",
      "Id": "0015i00000ecMumAAJ",
      "BillingCity": "Chennai",
      "BillingCountry": "India"
    },
    {
      "Name": "New Account C",
      "Id": "0015i00000ecMumAAO",
      "BillingCity": "Bangalore"
    }
  ]
}

Formula:

LISTMERGE("Id,Name", LIST(CurrentAccounts), LIST(ProspectiveAccounts))

Here, “Id,Name” are the two keys based on which the lists ‘CurrentAccounts’ and ‘ProspectiveAccounts’ will be merged.

Formula Result Path:  FinalAccountsFromListMerge

Output:

Now, since we have used two matching keys: ‘Id’ and ‘Name’, the accounts in which both the nodes (‘Id’ and ‘Name’) match are merged into a single element. 

"Id": "0015i00000ecMumAAE",

"Name": "Account A"

The above values match for the elements in both arrays, so a single entry is created for ‘Account A’.

And the other elements that do not find a match are just picked up as a whole, and the final output array is created. 

So we get the below elements in the final array, 

* ‘Existing Account B’ from the ‘CurrentAccounts’ array.

* ‘New Account B’ and ‘New Account C’ from the ‘ProspectiveAccounts’ array.

NOTE: If you have a requirement to just combine two array elements without any matching key, you can use the LIST function instead. 

LISTMERGEPRIMARY function:

This function behaves similar to the LISTMERGE function, except that the output list contains only the keys from the first list.

Example 1: Single matching key (‘Id’)

This example uses the LISTMERGEPRIMARY function with a single key.

Input:

{
  "CurrentAccounts": [
    {
      "Name": "Existing Account A",
      "Id": "0015i00000ecMumAAE",
      "BillingCity": "New York",
      "BillingCountry": "US"
    },
    {
      "Name": "Existing Account B",
      "Id": "0015i00000ecMumAAJ",
      "BillingCity": "Delhi",
      "Employees": 789
    }
  ],
  "ProspectiveAccounts": [
    {
      "Name": "New Account A",
      "Id": "0015i00000ecMumAAE",
      "BillingCity": "Paris",
      "Employees": 980
    },
    {
      "Name": "New Account B",
      "Id": "0015i00000ecMumAAJ",
      "BillingCity": "Chennai",
      "BillingCountry": "India"
    },
    {
      "Name": "New Account C",
      "Id": "0015i00000ecMumAAO",
      "BillingCity": "Bangalore"
    }
  ]
}

Formula:

LISTMERGEPRIMARY("Id", LIST(CurrentAccounts), LIST(ProspectiveAccounts))

Here, “Id” is the key based on which the lists ‘CurrentAccounts’ and ‘ProspectiveAccounts’ will be merged.

Formula Result Path:  FinalAccountsFromListMergePrimary

Output:

If we observe the output, since we have used ‘Id’ as the matching criteria key, 

• The accounts with the same ‘Id’ (0015i00000ecMumAAE, 0015i00000ecMumAAJ) in both the arrays ‘CurrentAccounts’ and ‘ProspectiveAccounts’ are merged into a single entry in the final output array.

• The nodes ‘Name’ and ‘BillingCity’ are present in both arrays (identically-named nodes), so in this case, the LISTMERGEPRIMARY function picks the node value from the last array, which is ‘ProspectiveAccounts’ in our example. So in the final output, we get the values as shown below,

Id	Name	BillingCity
0015i00000ecMumAAE	New Account A	Paris
0015i00000ecMumAAJ	New Account B	Chennai

• For the remaining (non-matching) nodes, ‘BillingCountry’ and ‘Employees’, the values are picked up from the respective array elements. Hence, we get the output nodes corresponding to the Ids, as shown below.

Id	Employees	BillingCountry
0015i00000ecMumAAE	980	US
0015i00000ecMumAAJ	789	India

NOTE:

The remaining elements in the last array that do not find a corresponding match in the first array are ignored altogether. This is the only difference between the LISTMERGE and LISTMERGEPRIMARY functions.

So in our example, the element with “Name: New Account C” in the ‘ProspectiveAccounts’ array is ignored.

Example 2: Multiple matching keys (‘Id’ and ‘Name’)

This example uses the LISTMERGEPRIMARY function with two keys.

Input:

{
  "CurrentAccounts": [
    {
      "Name": "Account A",
      "Id": "0015i00000ecMumAAE",
      "BillingCity": "New York",
      "BillingCountry": "US"
    },
    {
      "Name": "Existing Account B",
      "Id": "0015i00000ecMumAAJ",
      "BillingCity": "Delhi",
      "Employees": 789
    }
  ],
  "ProspectiveAccounts": [
    {
      "Name": "Account A",
      "Id": "0015i00000ecMumAAE",
      "BillingCity": "Paris",
      "Employees": 980
    },
    {
      "Name": "New Account B",
      "Id": "0015i00000ecMumAAJ",
      "BillingCity": "Chennai",
      "BillingCountry": "India"
    },
    {
      "Name": "New Account C",
      "Id": "0015i00000ecMumAAO",
      "BillingCity": "Bangalore"
    }
  ]
}

Formula:

LISTMERGEPRIMARY("Id,Name", LIST(CurrentAccounts), LIST(ProspectiveAccounts))

Here, “Id,Name” are the two keys based on which the lists ‘CurrentAccounts’ and ‘ProspectiveAccounts’ will be merged.

Formula Result Path:  FinalAccountsFromListMergePrimary

Output:

Now, since we have used two matching keys: ‘Id’ and ‘Name’, the accounts in which both the nodes (‘Id’ and ‘Name’) match are merged into a single element. 

"Id": "0015i00000ecMumAAE",

"Name": "Account A"

The above values match for the elements in both arrays, so a single entry is created for ‘Account A’.

And for the other elements in the first (primary) array that do not find a match, they are just picked up as a whole, and the final output array is created.

"Id": "0015i00000ecMumAAJ",

"Name": "Existing Account B"

In our example, the second element with the above values from the ‘CurrentAccounts’ array is just picked up as a whole and added to the final output array.

The remaining elements in the second array, ‘ProspectiveAccounts’ are ignored altogether.

Please read this blog for the usage of other LIST functions listed below: 

• MAX

• MIN

• SUM

• AVG

• LISTSIZE

• ISBLANK

• ISNOTBLANK

• LIST

• SORTBY

• FILTER

References:

https://help.salesforce.com/s/articleView?id=sf.os_use_formulas_in_dataraptors_47540.htm&type=5

https://help.salesforce.com/s/articleView?id=sf.os_function_reference_56716.htm&type=5