Google Maps reading results from populated table into.....

[#1]

Hello Everyone,

I need help with this part of the project.  I have everything working, but since database is pretty big worldwide, I came up with idea when we run these reports for the company and our sales team which travel by area in the particular state.

As you can see the selection underneath the map is selected to "FL" which shows the list of customers on the left of the map.  So i would need to map out and get the latitude and longitude of that area. I put a button as an idea below and hoping to grab the coordinated based in that list.

Is it possible for that button to grab the list from the table box which is "TB01" the object id and get only latitude & longitude of that list.  This database is from SAGE 50 and i tell you that database is just not right, the way it pulls the address from so many tables.  But everything is working and pulling correctly.  What I’m trying to say there is no such table where all customers’ addresses are just stored.  That table has address of vendors, employees, and so on.  So I’m hoping somehow we can grab from the table that populated already that’s on the left of the map.  I hope this makes sense.  Can anyone help me?

Any ideas would be greatly appreciated.

[#2]

should it not happen seamlessly with the data model association. I mean if you make a selection the map should reflect that. Hope alternate states aren't used.

[#3]

well my data dont have the lat & long to the address, i need to get that data from google?

[#4]

can't you get/create a file with the longitudes/latitudes and link that with the data model rather than relying on google for that? It'll be a one off pain rather than applying all improvised logic to address that.

[#5]

well i did think about that, and probably will be better off.

But since this wont be used so much and address change and clients change i was hoping i can grab from that table.

I just cant figure out how to grab info from a object id inside table for the function to work.

example

this calls direct from the table

Supplier:
SQL SELECT TOP 1000 AddressTypeDesc,
Name,
AddressLine1,
AddressLine2,
City,
State,
Zip
FROM METLABCORPORATION.Address;

but is there a way to call from that list that already been populated as shown on exmaple?

[#6]

i know this syntax is incorrect, but is it possible to make this code work

Supplier:
LOAD ShipToName,
ShipToAddress1,
ShipToAddress2,
ShipToCity,
ShipToState,
ShipToZIP
FROM TB01;                    

*****NOTE    "TB01"  this is the OBJECT ID name of the table list on the left of the image above******

let noRows = NoOfRows('Supplier')-1;

for i=0 to $(noRows)

let d=peek('Name',$(i),'Supplier');

let address=peek('AddressLine2',$(i),'Supplier') & ' ' & peek('City',$(i),'Supplier') & ' ' & peek('State',$(i),'Supplier');

Data:
LOAD
'$(d)' as SupplierName,
'$(address)' as SupplierAddress,
([result/geometry/location/lat]) as latitude,
([result/geometry/location/lng]) as longitude
FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(address)&sensor=false] (XmlSimple, Table is [GeocodeResponse]);
next i;

[#7]

can anyone help with the line of code i need, please

[#8]

really no help? no other ideas?

any different way to run the code?