Skip to main content
Answer

Shopify Connector - Separate street and house number

  • October 19, 2023
  • 1 reply
  • 71 views
J
Jr Varsity III
Forum|alt.badge.img

Greetings,

We are currently utilizing the Acumatica Shopify Connector to establish a connection between our Shopify Store and Acumatica (specifically Haufe X360). We have encountered an issue with PayPal Express checkouts that we are eager to resolve. As a result, we are seeking assistance and suggestions on how to address this problem.

When customers opt for PayPal Express checkouts, the connector combines the street and house number into a single field. However, in Haufe, we maintain street and house number as separate entities. Therefore, we are in need of a solution to convert the incorrect data format from something like "[Sample Street 374]" to the desired format of "[Sample Street]” “[374]". Is there a feasible way to achieve this, and if so, what would be the recommended approach or is there maybe a customization project already out there in the wilds to use?

Your input and guidance would be greatly appreciated.

 

 

Best answer by MasterLuke

Hi Jannik,

i’ve encountered a similiar problem. I used this code in the entities mapping to adress this issue. I’ve just tested for a few adresses… it looks for the first number in the adress an declares everything before as the street and everything after as housenumber. For germany this seems ok..

 

Bill to Adress - Street:

=Left([OrderData -> BillingAddress.Address1], Switch(    InStr([OrderData -> BillingAddress.Address1], '0') > 0, InStr([OrderData -> BillingAddress.Address1], '0'),    InStr([OrderData -> BillingAddress.Address1], '1') > 0, InStr([OrderData -> BillingAddress.Address1], '1'),    InStr([OrderData -> BillingAddress.Address1], '2') > 0, InStr([OrderData -> BillingAddress.Address1], '2'),    InStr([OrderData -> BillingAddress.Address1], '3') > 0, InStr([OrderData -> BillingAddress.Address1], '3'),    InStr([OrderData -> BillingAddress.Address1], '4') > 0, InStr([OrderData -> BillingAddress.Address1], '4'),    InStr([OrderData -> BillingAddress.Address1], '5') > 0, InStr([OrderData -> BillingAddress.Address1], '5'),    InStr([OrderData -> BillingAddress.Address1], '6') > 0, InStr([OrderData -> BillingAddress.Address1], '6'),    InStr([OrderData -> BillingAddress.Address1], '7') > 0, InStr([OrderData -> BillingAddress.Address1], '7'),    InStr([OrderData -> BillingAddress.Address1], '8') > 0, InStr([OrderData -> BillingAddress.Address1], '8'),    InStr([OrderData -> BillingAddress.Address1], '9') > 0, InStr([OrderData -> BillingAddress.Address1], '9'),    Len([OrderData -> BillingAddress.Address1]) + 1) - 1)

 

Bill to Adress - Housenumber:

=Trim(Right([OrderData -> BillingAddress.Address1], Len([OrderData -> BillingAddress.Address1]) - Switch(    InStr([OrderData -> BillingAddress.Address1], '0') > 0, InStr([OrderData -> BillingAddress.Address1], '0'),    InStr([OrderData -> BillingAddress.Address1], '1') > 0, InStr([OrderData -> BillingAddress.Address1], '1'),    InStr([OrderData -> BillingAddress.Address1], '2') > 0, InStr([OrderData -> BillingAddress.Address1], '2'),    InStr([OrderData -> BillingAddress.Address1], '3') > 0, InStr([OrderData -> BillingAddress.Address1], '3'),    InStr([OrderData -> BillingAddress.Address1], '4') > 0, InStr([OrderData -> BillingAddress.Address1], '4'),    InStr([OrderData -> BillingAddress.Address1], '5') > 0, InStr([OrderData -> BillingAddress.Address1], '5'),    InStr([OrderData -> BillingAddress.Address1], '6') > 0, InStr([OrderData -> BillingAddress.Address1], '6'),    InStr([OrderData -> BillingAddress.Address1], '7') > 0, InStr([OrderData -> BillingAddress.Address1], '7'),    InStr([OrderData -> BillingAddress.Address1], '8') > 0, InStr([OrderData -> BillingAddress.Address1], '8'),    InStr([OrderData -> BillingAddress.Address1], '9') > 0, InStr([OrderData -> BillingAddress.Address1], '9'),    Len([OrderData -> BillingAddress.Address1]) + 1) + 1))

 

Shipt to Adress - Street (first check if delivery adress is empty, if not use billing adress):

=IIf(    [OrderData -> ShippingAddress.Address1] <> Null,    Left([OrderData -> ShippingAddress.Address1],         Switch(            InStr([OrderData -> ShippingAddress.Address1], '0') > 0, InStr([OrderData -> ShippingAddress.Address1], '0'),            InStr([OrderData -> ShippingAddress.Address1], '1') > 0, InStr([OrderData -> ShippingAddress.Address1], '1'),            InStr([OrderData -> ShippingAddress.Address1], '2') > 0, InStr([OrderData -> ShippingAddress.Address1], '2'),            InStr([OrderData -> ShippingAddress.Address1], '3') > 0, InStr([OrderData -> ShippingAddress.Address1], '3'),            InStr([OrderData -> ShippingAddress.Address1], '4') > 0, InStr([OrderData -> ShippingAddress.Address1], '4'),            InStr([OrderData -> ShippingAddress.Address1], '5') > 0, InStr([OrderData -> ShippingAddress.Address1], '5'),            InStr([OrderData -> ShippingAddress.Address1], '6') > 0, InStr([OrderData -> ShippingAddress.Address1], '6'),            InStr([OrderData -> ShippingAddress.Address1], '7') > 0, InStr([OrderData -> ShippingAddress.Address1], '7'),            InStr([OrderData -> ShippingAddress.Address1], '8') > 0, InStr([OrderData -> ShippingAddress.Address1], '8'),            InStr([OrderData -> ShippingAddress.Address1], '9') > 0, InStr([OrderData -> ShippingAddress.Address1], '9'),            Len([OrderData -> ShippingAddress.Address1]) + 1        ) - 1    ),    Left([OrderData -> BillingAddress.Address1],         Switch(            InStr([OrderData -> BillingAddress.Address1], '0') > 0, InStr([OrderData -> BillingAddress.Address1], '0'),            InStr([OrderData -> BillingAddress.Address1], '1') > 0, InStr([OrderData -> BillingAddress.Address1], '1'),            InStr([OrderData -> BillingAddress.Address1], '2') > 0, InStr([OrderData -> BillingAddress.Address1], '2'),            InStr([OrderData -> BillingAddress.Address1], '3') > 0, InStr([OrderData -> BillingAddress.Address1], '3'),            InStr([OrderData -> BillingAddress.Address1], '4') > 0, InStr([OrderData -> BillingAddress.Address1], '4'),            InStr([OrderData -> BillingAddress.Address1], '5') > 0, InStr([OrderData -> BillingAddress.Address1], '5'),            InStr([OrderData -> BillingAddress.Address1], '6') > 0, InStr([OrderData -> BillingAddress.Address1], '6'),            InStr([OrderData -> BillingAddress.Address1], '7') > 0, InStr([OrderData -> BillingAddress.Address1], '7'),            InStr([OrderData -> BillingAddress.Address1], '8') > 0, InStr([OrderData -> BillingAddress.Address1], '8'),            InStr([OrderData -> BillingAddress.Address1], '9') > 0, InStr([OrderData -> BillingAddress.Address1], '9'),            Len([OrderData -> BillingAddress.Address1]) + 1        ) - 1    ))

 

 

Ship to Adress - Housenumber:

=Trim(    IIf(        [OrderData -> ShippingAddress.Address1] <> Null,        Right([OrderData -> ShippingAddress.Address1],             Len([OrderData -> ShippingAddress.Address1]) -             Switch(                InStr([OrderData -> ShippingAddress.Address1], '0') > 0, InStr([OrderData -> ShippingAddress.Address1], '0'),                InStr([OrderData -> ShippingAddress.Address1], '1') > 0, InStr([OrderData -> ShippingAddress.Address1], '1'),                InStr([OrderData -> ShippingAddress.Address1], '2') > 0, InStr([OrderData -> ShippingAddress.Address1], '2'),                InStr([OrderData -> ShippingAddress.Address1], '3') > 0, InStr([OrderData -> ShippingAddress.Address1], '3'),                InStr([OrderData -> ShippingAddress.Address1], '4') > 0, InStr([OrderData -> ShippingAddress.Address1], '4'),                InStr([OrderData -> ShippingAddress.Address1], '5') > 0, InStr([OrderData -> ShippingAddress.Address1], '5'),                InStr([OrderData -> ShippingAddress.Address1], '6') > 0, InStr([OrderData -> ShippingAddress.Address1], '6'),                InStr([OrderData -> ShippingAddress.Address1], '7') > 0, InStr([OrderData -> ShippingAddress.Address1], '7'),                InStr([OrderData -> ShippingAddress.Address1], '8') > 0, InStr([OrderData -> ShippingAddress.Address1], '8'),                InStr([OrderData -> ShippingAddress.Address1], '9') > 0, InStr([OrderData -> ShippingAddress.Address1], '9'),                Len([OrderData -> ShippingAddress.Address1]) + 1            )        ),        Right([OrderData -> BillingAddress.Address1],             Len([OrderData -> BillingAddress.Address1]) -             Switch(                InStr([OrderData -> BillingAddress.Address1], '0') > 0, InStr([OrderData -> BillingAddress.Address1], '0'),                InStr([OrderData -> BillingAddress.Address1], '1') > 0, InStr([OrderData -> BillingAddress.Address1], '1'),                InStr([OrderData -> BillingAddress.Address1], '2') > 0, InStr([OrderData -> BillingAddress.Address1], '2'),                InStr([OrderData -> BillingAddress.Address1], '3') > 0, InStr([OrderData -> BillingAddress.Address1], '3'),                InStr([OrderData -> BillingAddress.Address1], '4') > 0, InStr([OrderData -> BillingAddress.Address1], '4'),                InStr([OrderData -> BillingAddress.Address1], '5') > 0, InStr([OrderData -> BillingAddress.Address1], '5'),                InStr([OrderData -> BillingAddress.Address1], '6') > 0, InStr([OrderData -> BillingAddress.Address1], '6'),                InStr([OrderData -> BillingAddress.Address1], '7') > 0, InStr([OrderData -> BillingAddress.Address1], '7'),                InStr([OrderData -> BillingAddress.Address1], '8') > 0, InStr([OrderData -> BillingAddress.Address1], '8'),                InStr([OrderData -> BillingAddress.Address1], '9') > 0, InStr([OrderData -> BillingAddress.Address1], '9'),                Len([OrderData -> BillingAddress.Address1]) + 1            )        )    ))

 

Jannik W.

1 reply

M
Freshman II
  • MasterLuke
  • Freshman II
  • Answer
  • February 10, 2025

Hi Jannik,

i’ve encountered a similiar problem. I used this code in the entities mapping to adress this issue. I’ve just tested for a few adresses… it looks for the first number in the adress an declares everything before as the street and everything after as housenumber. For germany this seems ok..

 

Bill to Adress - Street:

=Left([OrderData -> BillingAddress.Address1], Switch(    InStr([OrderData -> BillingAddress.Address1], '0') > 0, InStr([OrderData -> BillingAddress.Address1], '0'),    InStr([OrderData -> BillingAddress.Address1], '1') > 0, InStr([OrderData -> BillingAddress.Address1], '1'),    InStr([OrderData -> BillingAddress.Address1], '2') > 0, InStr([OrderData -> BillingAddress.Address1], '2'),    InStr([OrderData -> BillingAddress.Address1], '3') > 0, InStr([OrderData -> BillingAddress.Address1], '3'),    InStr([OrderData -> BillingAddress.Address1], '4') > 0, InStr([OrderData -> BillingAddress.Address1], '4'),    InStr([OrderData -> BillingAddress.Address1], '5') > 0, InStr([OrderData -> BillingAddress.Address1], '5'),    InStr([OrderData -> BillingAddress.Address1], '6') > 0, InStr([OrderData -> BillingAddress.Address1], '6'),    InStr([OrderData -> BillingAddress.Address1], '7') > 0, InStr([OrderData -> BillingAddress.Address1], '7'),    InStr([OrderData -> BillingAddress.Address1], '8') > 0, InStr([OrderData -> BillingAddress.Address1], '8'),    InStr([OrderData -> BillingAddress.Address1], '9') > 0, InStr([OrderData -> BillingAddress.Address1], '9'),    Len([OrderData -> BillingAddress.Address1]) + 1) - 1)

 

Bill to Adress - Housenumber:

=Trim(Right([OrderData -> BillingAddress.Address1], Len([OrderData -> BillingAddress.Address1]) - Switch(    InStr([OrderData -> BillingAddress.Address1], '0') > 0, InStr([OrderData -> BillingAddress.Address1], '0'),    InStr([OrderData -> BillingAddress.Address1], '1') > 0, InStr([OrderData -> BillingAddress.Address1], '1'),    InStr([OrderData -> BillingAddress.Address1], '2') > 0, InStr([OrderData -> BillingAddress.Address1], '2'),    InStr([OrderData -> BillingAddress.Address1], '3') > 0, InStr([OrderData -> BillingAddress.Address1], '3'),    InStr([OrderData -> BillingAddress.Address1], '4') > 0, InStr([OrderData -> BillingAddress.Address1], '4'),    InStr([OrderData -> BillingAddress.Address1], '5') > 0, InStr([OrderData -> BillingAddress.Address1], '5'),    InStr([OrderData -> BillingAddress.Address1], '6') > 0, InStr([OrderData -> BillingAddress.Address1], '6'),    InStr([OrderData -> BillingAddress.Address1], '7') > 0, InStr([OrderData -> BillingAddress.Address1], '7'),    InStr([OrderData -> BillingAddress.Address1], '8') > 0, InStr([OrderData -> BillingAddress.Address1], '8'),    InStr([OrderData -> BillingAddress.Address1], '9') > 0, InStr([OrderData -> BillingAddress.Address1], '9'),    Len([OrderData -> BillingAddress.Address1]) + 1) + 1))

 

Shipt to Adress - Street (first check if delivery adress is empty, if not use billing adress):

=IIf(    [OrderData -> ShippingAddress.Address1] <> Null,    Left([OrderData -> ShippingAddress.Address1],         Switch(            InStr([OrderData -> ShippingAddress.Address1], '0') > 0, InStr([OrderData -> ShippingAddress.Address1], '0'),            InStr([OrderData -> ShippingAddress.Address1], '1') > 0, InStr([OrderData -> ShippingAddress.Address1], '1'),            InStr([OrderData -> ShippingAddress.Address1], '2') > 0, InStr([OrderData -> ShippingAddress.Address1], '2'),            InStr([OrderData -> ShippingAddress.Address1], '3') > 0, InStr([OrderData -> ShippingAddress.Address1], '3'),            InStr([OrderData -> ShippingAddress.Address1], '4') > 0, InStr([OrderData -> ShippingAddress.Address1], '4'),            InStr([OrderData -> ShippingAddress.Address1], '5') > 0, InStr([OrderData -> ShippingAddress.Address1], '5'),            InStr([OrderData -> ShippingAddress.Address1], '6') > 0, InStr([OrderData -> ShippingAddress.Address1], '6'),            InStr([OrderData -> ShippingAddress.Address1], '7') > 0, InStr([OrderData -> ShippingAddress.Address1], '7'),            InStr([OrderData -> ShippingAddress.Address1], '8') > 0, InStr([OrderData -> ShippingAddress.Address1], '8'),            InStr([OrderData -> ShippingAddress.Address1], '9') > 0, InStr([OrderData -> ShippingAddress.Address1], '9'),            Len([OrderData -> ShippingAddress.Address1]) + 1        ) - 1    ),    Left([OrderData -> BillingAddress.Address1],         Switch(            InStr([OrderData -> BillingAddress.Address1], '0') > 0, InStr([OrderData -> BillingAddress.Address1], '0'),            InStr([OrderData -> BillingAddress.Address1], '1') > 0, InStr([OrderData -> BillingAddress.Address1], '1'),            InStr([OrderData -> BillingAddress.Address1], '2') > 0, InStr([OrderData -> BillingAddress.Address1], '2'),            InStr([OrderData -> BillingAddress.Address1], '3') > 0, InStr([OrderData -> BillingAddress.Address1], '3'),            InStr([OrderData -> BillingAddress.Address1], '4') > 0, InStr([OrderData -> BillingAddress.Address1], '4'),            InStr([OrderData -> BillingAddress.Address1], '5') > 0, InStr([OrderData -> BillingAddress.Address1], '5'),            InStr([OrderData -> BillingAddress.Address1], '6') > 0, InStr([OrderData -> BillingAddress.Address1], '6'),            InStr([OrderData -> BillingAddress.Address1], '7') > 0, InStr([OrderData -> BillingAddress.Address1], '7'),            InStr([OrderData -> BillingAddress.Address1], '8') > 0, InStr([OrderData -> BillingAddress.Address1], '8'),            InStr([OrderData -> BillingAddress.Address1], '9') > 0, InStr([OrderData -> BillingAddress.Address1], '9'),            Len([OrderData -> BillingAddress.Address1]) + 1        ) - 1    ))

 

 

Ship to Adress - Housenumber:

=Trim(    IIf(        [OrderData -> ShippingAddress.Address1] <> Null,        Right([OrderData -> ShippingAddress.Address1],             Len([OrderData -> ShippingAddress.Address1]) -             Switch(                InStr([OrderData -> ShippingAddress.Address1], '0') > 0, InStr([OrderData -> ShippingAddress.Address1], '0'),                InStr([OrderData -> ShippingAddress.Address1], '1') > 0, InStr([OrderData -> ShippingAddress.Address1], '1'),                InStr([OrderData -> ShippingAddress.Address1], '2') > 0, InStr([OrderData -> ShippingAddress.Address1], '2'),                InStr([OrderData -> ShippingAddress.Address1], '3') > 0, InStr([OrderData -> ShippingAddress.Address1], '3'),                InStr([OrderData -> ShippingAddress.Address1], '4') > 0, InStr([OrderData -> ShippingAddress.Address1], '4'),                InStr([OrderData -> ShippingAddress.Address1], '5') > 0, InStr([OrderData -> ShippingAddress.Address1], '5'),                InStr([OrderData -> ShippingAddress.Address1], '6') > 0, InStr([OrderData -> ShippingAddress.Address1], '6'),                InStr([OrderData -> ShippingAddress.Address1], '7') > 0, InStr([OrderData -> ShippingAddress.Address1], '7'),                InStr([OrderData -> ShippingAddress.Address1], '8') > 0, InStr([OrderData -> ShippingAddress.Address1], '8'),                InStr([OrderData -> ShippingAddress.Address1], '9') > 0, InStr([OrderData -> ShippingAddress.Address1], '9'),                Len([OrderData -> ShippingAddress.Address1]) + 1            )        ),        Right([OrderData -> BillingAddress.Address1],             Len([OrderData -> BillingAddress.Address1]) -             Switch(                InStr([OrderData -> BillingAddress.Address1], '0') > 0, InStr([OrderData -> BillingAddress.Address1], '0'),                InStr([OrderData -> BillingAddress.Address1], '1') > 0, InStr([OrderData -> BillingAddress.Address1], '1'),                InStr([OrderData -> BillingAddress.Address1], '2') > 0, InStr([OrderData -> BillingAddress.Address1], '2'),                InStr([OrderData -> BillingAddress.Address1], '3') > 0, InStr([OrderData -> BillingAddress.Address1], '3'),                InStr([OrderData -> BillingAddress.Address1], '4') > 0, InStr([OrderData -> BillingAddress.Address1], '4'),                InStr([OrderData -> BillingAddress.Address1], '5') > 0, InStr([OrderData -> BillingAddress.Address1], '5'),                InStr([OrderData -> BillingAddress.Address1], '6') > 0, InStr([OrderData -> BillingAddress.Address1], '6'),                InStr([OrderData -> BillingAddress.Address1], '7') > 0, InStr([OrderData -> BillingAddress.Address1], '7'),                InStr([OrderData -> BillingAddress.Address1], '8') > 0, InStr([OrderData -> BillingAddress.Address1], '8'),                InStr([OrderData -> BillingAddress.Address1], '9') > 0, InStr([OrderData -> BillingAddress.Address1], '9'),                Len([OrderData -> BillingAddress.Address1]) + 1            )        )    ))

 

Chris Hackett
Terms & ConditionsCookie settingsAccessibility statement
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved