NetSuite: Sync Customer Addresses by Address Label - workaround

Scenario: Customer address data in an external system needs to be refreshed in NetSuite, based on the Address Label. The Customer records exist in both systems, and no new Customer records should be created in this scenario. Customer Addresses must be updated or created, using the Address Label as if it were the key to the Address.

Issue: NetSuite treats addresses as a sublist under the Customer record; native Celigo Import “Add or Update” may add a Customer record rather than adding a new Address. There is no direct Import option into an Address record. NetSuite permits duplicate Address Labels and does not treat labels as unique keys.

Workaround: Perform a lookup to get the existing Address ID; configure the Import mapping for the ID to Discard if Empty. This prevents duplicate addresses from being created, and creates Addresses cleanly during a Customer “Update”.

Details: After setting up the export and lookup steps, ensure that the Customer Internal ID and Address Label have the desired values.

1. Assumed: the flow has generated or exported a desired Address Label, unique per Customer.
2. In the step where the address fields are being set in Transform, include a field for the Address Internal ID, set to a hard-coded empty string value (input field Settings button, then Field transformation type Hard-coded then How would you like to hard-code the value? Use empty string as default value.
3. Create a Customer saved search in NetSuite including the Customer Internal ID, Address Label, and Address Internal ID fields. 
4. Add a Lookup on the NetSuite Customer saved search, filtered by Customer Internal ID and Address Label (For Additional Search Criteria include Customer Internal ID any of {{internalid}} and Address Label is {{YourGeneratedLabel}}.)
5. In the Lookup results mapping, include the Address Internal ID, overwriting the default empty string value set earlier. This overwrites with an ID, if the label already exists for the Customer.
6. Create an Import to NetSuite Customer configured as follows:
   1. Operation: Update
   2. How we can find existing records: InternalId is Internal ID.
7. Import mapping: Configure the field mapping as follows:
   1. Customer Internal ID to Internal ID.
   2. Address Internal ID to Address : ID; select the gear icon and pick Discard if empty. This is important.
   3. All other Address fields (Label, address lines, City, State, Postal Code, etc) map as usual.

Result: The flow will correctly “add or update” the Address details under the Customer, using Address Label as if it were a unique key. 

Caveat and WARNING: This flow makes an assumption that differs from NetSuite's default behavior. NetSuite permits duplicate Address Labels, but this flow treats the Address Labels as if they are unique. This may cause unexpected side effects! Ensure that the Address Labels generated in the Export phase are unique per Customer to prevent collisions. This may cause issues if a Customer has multiple duplicate Address Labels already.