Shopify Collective x NetSuite - Syncing (some) Orders to a Designated Customer

[ZSwitaj (Zach Switaj)]

Hello,

We currently use the Shopify - NetSuite Integration App, and our sales teams has decided to launch Shopify Collective to sell our products on other Shopify stores and theirs on ours. The Shopify side of things is fairly straightforward, but I am hitting some walls trying to adapt the IA to work with the new workflows.

The main issue is that each Collective order we get from a partner's site comes with a plus/subaddressed email (e.g. order+12345@example.com and order+67890@example.com). This means a new customer is created in NetSuite for each order, with the Collective partner's store name as the customer name, as that is the "Customer" according to Shopify. This gets messy quick, but also won't fly with our finance team.

Our Finance team is requesting we sync all orders for each partner to a single customer account in NetSuite. This ensures we can use the required resale certificates (managed through Avalara) for a single customer per partner.

The logic for customer lookup/creation is wrapped up in a locked preMap hook on the Shopify order to NetSuite order flow, so I'm not sure how to tackle this. My current thoughts (in order of most to least ideal) are:

1. Modify mapping in the Shopify customer to NetSuite customer flow to modify the subaddressed emails before order creation
2. Create a beforeSubmit script in NetSuite that fires after order creation, but before the record is sent to Avalara for tax calculation
3. Create an afterSubmit script in NetSuite that changes the customer and deletes the previously created customer

Has anyone integrated Shopify Collective with NetSuite through the IA before? Or have similar experience in trying to write to a single customer (but selectively )?

Appreciate any insights or ideas I might be overlooking!

Cheers!

[tylerlamparter]

So your normal customer in NetSuite is without the + and the partner version is with the plus? Can you try using a formula field that normalizes the email in the criteria for how to find customers?

[ZSwitaj (Zach Switaj)]

Thanks for the reply @tylerlamparter. I didn't know that secondary lookup criteria was able to be modified, the tooltip shows it is a "read-only setting". That said, I was able to update the criteria to ["formulatext:regexp_replace({email},'\\+[^@]*','')","is","{{{email}}}"].

Unfortunately, that didn't seem to do the trick. Prior to testing I moved all NetSuite transactions to a customer with the standard test@example.com email address and deleted the customer profiles with the test+xyz@example.com emails.

Steps to reproduce:

• Update Shopify Customer to NetSuite Customer (add or update) flow with above secondary customer lookup criteria
• Update mapping with "Updated by Shopify IA" appended to customer profile comment field
• Modify customer in Shopify with the 'test+123@example' email address
• Run flow

Expected Results:

• NetSuite customer with 'test@example.com' email is updated with "Updated by Shopify IA" comment

Actual Results:

• NetSuite customer with 'test+123@example.com' email is created and "Updated by Shopify IA" comment is added

This very well could be an artifact of the way I am testing this, and it might be better to do so with an actual new test order from the Collective Supplier, but from my understanding this should produce the same end result.

An interesting note - looking at the JSON output of the 'Post Customers to NetSuite' import step using ZangaBox (a recommendation of yours from elsewhere I believe!) it does not show the secondary lookup criteria as a lookup at all. Not sure it's related to what I'm seeing, but it seemed odd when I looked there to ensure formatting was correct.

[tylerlamparter]

Maybe it's because it's hitting the primary logic first? The secondary logic is only used when the primary logic doesn't find something.

[nileshkumar]

Hi @ZSwitaj

Apart from what @tylerlamparter suggested, you can leverage the below handlebar in the order flow as an alternative approach.
{{#if email}}{{#compare (regexSearch email "@celigo\.com$" "g") ">" -1}}38427{{else}}{{#compare (regexSearch email "@clickstop\.com$" "g") ">" -1}}38227{{else}}{{netsuitecustomerid}}{{/compare}}{{/compare}}{{/if}}

This Mapping need to be updated using Postman, as it is locked in the UI. You can connect with support for help here as needed for updating the mapping.

Limitation: Please note that you will not be able to use the customer deposit flow in case you update the above mapping. In this customer deposit flow, we are picking the customer from PreMap data, which will result in an error.

Thanks
Nilesh.

[ZSwitaj (Zach Switaj)]

@tylerlamparter

Maybe it's because it's hitting the primary logic first? The secondary logic is only used when the primary logic doesn't find something.

It shouldn't have. It's looking for a Celigo Shopify Customer Id Map with a matching Shopify Customer id. I did a search for that Id and there aren't any.

I tried to add the email regex formula as an OR to the above logic, but it wouldn't let me save as an OR, only to add it as an AND statement.

I really feel like this should be this simple, so not sure why it wouldn't be working. I'll continue to try to finesse it.

@nileshkumar

Apart from what @tylerlamparter suggested, you can leverage the below handlebar in the order flow as an alternative approach.

Thanks for the reply Nilesh. I didn't know we could update locked mappings via Postman. I will have to look into that. Is it possible to add a transformation step to the flow via Postman as well? That would be the easiest solution I can think of off the top of my head.

[tylerlamparter]

You could put everything into the primary condition and manage it by using the formula field. Essentially, just use case statements in the order you want conditions to match.