The TIBCO Platform is a real-time, composable data platform that will bring together an evolving set of your TIBCO solutions - and it's available now!
A chart showing the TIBCO Platform vision
Jump to content
Forums
Ask questions and gain insight from discussions

What's the best way to translate an address to a GeoCode for NetSuite


Kevin Tipa

Recommended Posts

I'm going to start with: NetSuite is super convoluted and obscure. I am not a fan of working with it at all. It's so confusing that I'm not even sure if GeoCode is a built-in concept or not.

I have a Dynamics CRM entity with address information including Country (from a lookup entity) and State (from another lookup entity for U.S. States), and that seems to be what GeoCode in NetSuite is based on. If I want to use Scribe to figure out what code it needs to store on the NetSuite side based on whether it's a non-U.S. country, or if it is the U.S., use the state instead... how do I do that

I've considered creating a separate lookup table in Dynamics that has the correct number values that NetSuite is expecting based on country and based on state if the country is the U.S. but then I have to do a lookup that can check if the country is a particular one, and then if necessary use the state field to look up the GeoCode ID. And I don't know of a way to do that in Scribe.

I recall something about being able to set up a look up data source directly in Scribe but I'm not familiar with it, and I'm not sure it answers the question of how to handle testing against state if the country is a particular one.

Or maybe NetSuite has a way to get the GeoCode from an address

I could use help on how to handle this data manipulation in transit.

Link to comment
Share on other sites

Hi Kevin,

It sounds like you are trying to do a lookup against a table / list of US states, if there are no match (matchresult =0) you know it is a non-US country.

To do this logic, you can use a separate sql table with US states or in CRM entity list of US states, and use a Lookup block in scribe to look up for the statecode, with an IF block you can check if the results.Recordsmatched is 0 or 1 and build your logic there.

You can do the same for a list of countries or countrycodes.

There might be also REST API services in the cloud that allows you to look up country lists and US statecodes, or a service that allows geocoding/validation, you can use a REST Connector for such data services.

Note: If you do not maintain your own reference list of countries and statecodes and leverage thirdparty lists or services, they may change over time and affect your integrations in an unexpected way.

Link to comment
Share on other sites

I understand the logic of what you are saying, but there are a couple pieces missing for me...

 

I can do the lookup of one part of the address, put in the if-statement, and do the other lookup if needed, but when it comes time to put something into the field mapping to say "store X in NetSuite.Customer.GeoCode", I don't know what X would be since the results of two separate lookups might be needed.

 

I have the lookup tables in CRM that provide the pick-list values within CRM, but nothing that relates a given location with the GeoCode integer that is expected.  I suppose adding GeoCode information to those tables is best, but the Dynamics CRM admin is going through a lot right now and I'd like to consider other options as a favor to him.  How else can I translate a country & state to an integer

 

I appreciate the help working through this.  Thank you.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...