Transforming customer with multiple shipping address, and combining a field into a comma separate field
Below is our current company sync to our B2B system. Take note that a company can have multiple shipping addresses. In this example, company 8512472 has two shipping addresses, while company 8571161 has only one.
{
"page_of_records": [
{
"record": {
"id": "8512472",
"Name": "Pursuit Equipment & Sport Ltd",
"Company Code": "8512472",
"Currency Code": "CAD",
"Price Sheet": "33% CAD",
"Address Line 1": "19000 North Fork Rd",
"Address Country": "CA"
}
},
{
"record": {
"id": "8512472",
"Name": "Pursuit Equipment & Sport Ltd",
"Company Code": "8512472",
"Currency Code": "CAD",
"Price Sheet": "33% CAD",
"Address Line 1": "7466 2 St",
"Address Country": "CA"
}
},
{
"record": {
"id": "8571161",
"Name": "MOB Motosports",
"Company Code": "8571161",
"Currency Code": "USD",
"Price Sheet": "35% USD",
"Address Line 1": "161 May Hollow",
"Address Country": "US"
}
}
]
}
We are trying to include the "pricing group" and below is the result when we modify the netsuite saved search.
{
"page_of_records": [
{
"record": {
"id": "8512472",
"Name": "Pursuit Equipment & Sport Ltd",
"Company Code": "8512472",
"Currency Code": "CAD",
"Price Sheet": "33% CAD",
"Address Line 1": "19000 North Fork Rd",
"Address Country": "CA",
"Pricing Group": "NC 45"
}
},
{
"record": {
"id": "8512472",
"Name": "Pursuit Equipment & Sport Ltd",
"Company Code": "8512472",
"Currency Code": "CAD",
"Price Sheet": "33% CAD",
"Address Line 1": "19000 North Fork Rd",
"Address Country": "CA",
"Pricing Group": "NC 60"
}
},
{
"record": {
"id": "8512472",
"Name": "Pursuit Equipment & Sport Ltd",
"Company Code": "8512472",
"Currency Code": "CAD",
"Price Sheet": "33% CAD",
"Address Line 1": "19000 North Fork Rd",
"Address Country": "CA",
"Pricing Group": "NC 75"
}
},
{
"record": {
"id": "8512472",
"Name": "Pursuit Equipment & Sport Ltd",
"Company Code": "8512472",
"Currency Code": "CAD",
"Price Sheet": "33% CAD",
"Address Line 1": "7466 2 St",
"Address Country": "CA",
"Pricing Group": "NC 45"
}
},
{
"record": {
"id": "8512472",
"Name": "Pursuit Equipment & Sport Ltd",
"Company Code": "8512472",
"Currency Code": "CAD",
"Price Sheet": "33% CAD",
"Address Line 1": "7466 2 St",
"Address Country": "CA",
"Pricing Group": "NC 60"
}
},
{
"record": {
"id": "8512472",
"Name": "Pursuit Equipment & Sport Ltd",
"Company Code": "8512472",
"Currency Code": "CAD",
"Price Sheet": "33% CAD",
"Address Line 1": "7466 2 St",
"Address Country": "CA",
"Pricing Group": "NC 75"
}
},
{
"record": {
"id": "8571161",
"Name": "MOB Motosports",
"Company Code": "8571161",
"Currency Code": "USD",
"Price Sheet": "35% USD",
"Address Line 1": "161 May Hollow",
"Address Country": "US",
"Pricing Group": "NC 45"
}
},
{
"record": {
"id": "8571161",
"Name": "MOB Motosports",
"Company Code": "8571161",
"Currency Code": "USD",
"Price Sheet": "35% USD",
"Address Line 1": "161 May Hollow",
"Address Country": "US",
"Pricing Group": "NC 60"
}
},
{
"record": {
"id": "8571161",
"Name": "MOB Motosports",
"Company Code": "8571161",
"Currency Code": "USD",
"Price Sheet": "35% USD",
"Address Line 1": "161 May Hollow",
"Address Country": "US",
"Pricing Group": "NC 75"
}
}
]
}
How can we transform that to combine the pricing group into one separated by comma? Below is what we want to achieve.
{
"page_of_records": [
{
"record": {
"id": "8512472",
"Name": "Pursuit Equipment & Sport Ltd",
"Company Code": "8512472",
"Currency Code": "CAD",
"Price Sheet": "33% CAD",
"Address Line 1": "19000 North Fork Rd",
"Address Country": "CA",
"Pricing Group": "NC 45,NC 60,NC 75"
}
},
{
"record": {
"id": "8512472",
"Name": "Pursuit Equipment & Sport Ltd",
"Company Code": "8512472",
"Currency Code": "CAD",
"Price Sheet": "33% CAD",
"Address Line 1": "7466 2 St",
"Address Country": "CA",
"Pricing Group": "NC 45,NC 60,NC 75"
}
},
{
"record": {
"id": "8571161",
"Name": "MOB Motosports",
"Company Code": "8571161",
"Currency Code": "USD",
"Price Sheet": "35% USD",
"Address Line 1": "161 May Hollow",
"Address Country": "US",
"Pricing Group": "NC 45,NC 60,NC 75"
}
}
]
}
-
Raul Bernales now you're getting more complex on me and we can't use simple handlebar expressions here and will have to use a script.
First, you'll need to enable grouping on your NetSuite export step, like so:
Next, you'll need to create a preSavePage hook on the NetSuite export step and utilize this script:
function preSavePage (options) {
let output = [];
for (let d of options.data) {
//get the unique combinations of record id, address line 1, and country
let uniqueCombinations = JSON.parse(JSON.stringify(d.filter(function (a) {
var key = a["id"] + '|' + a["Address Line 1"] + '|' + a["Address Country"];
if (!this[key]) {
this[key] = true;
return true;
}
}, Object.create(null))));
//loop through the rows and concat pricing group to the unique combinations
for (let u of uniqueCombinations) {
u["Pricing Group"] = '';
for (let r of d) {
if (r.id == u.id & r["Address Line 1"] == u["Address Line 1"] && r["Address Country"] == u["Address Country"]) {
if (u["Pricing Group"]) {
u["Pricing Group"] = u["Pricing Group"] + ',' + r["Pricing Group"];
} else {
u["Pricing Group"] = r["Pricing Group"];
}
}
}
output.push(u);
}
}
return {
data: output,
errors: options.errors,
abort: false,
newErrorsAndRetryData: []
}
}This should give you the following output:
Since the grouping feature on the NetSuite export only allows grouping based on internal id, we had to do this. If we could have grouped on id, address line 1, and address country on the export settings, then we could have had a simple transform and handlebar expressions down the road.
0 -
Hi Tyler,
I'm not sure if I was able to follow your instructions, but I'm getting the error message below
Message: TypeError: d.filter is not a function
Location: <anonymous>:7
Stack: TypeError: d.filter is not a function
at preSavePage (<anonymous>:7:58)here is a screenshot for your reference.
Thanks!
0 -
Raul Bernales it looks like you didn't enable grouping on your NetSuite export. See the screenshot on my first comment.
0 -
Hi Tyler,
Finally, I was able to find time for this and we got it working this time. Thank you for all your help.
Best regards,
Raul
1
Please sign in to leave a comment.
Comments
4 comments