It does not appear that AutoFetch will return JSON or XML to $Text or another attribute. We need @eastgate to explain how we retrieve the contents of the page that the URL returns. I suspect that AutoFetch ignores non-HTML content. Just a guess.
Yup, got it. This seems to work. I’m confused on how/why you strung these two commands together: callDistanceMatrixAPI_db buildDistanceMatrixAPI_payload_db
I think it would be easier if you could show me something. Some bit of logic/flow is escaping me.
you could merge the code into one function - I try to separate the code as much as possible - my functions should carry out only one part of a task.
The new demo has even one level more:
but you still could change this and put all the code into one function. It is a matter of your personal taste
And those demo files are work in progress. I’m playing around with the code - it’s not final
I’d be looking at nine different sites. They trip won’t always originate from the same point, so it needs to be a distance matrix. (I.e. same column and row headers with distances for each intersection.)
Ok, can you do us/me a favor. I’ll create demo for you.
Can you make up 9 sample addresses in your city (feel free to use real place but not the ones related to to your work, e.g., a Starbucks, local grocery, etc.).
We’ll create a resource note for each site.
We’ll then creat a trip prototype with site origin and site destination.
We’ll the calculate the distance.
We can calculate the reimbursement estimate as well, e.g., $0.33/mile.
Days Inn Marion, 6138 Corridor Dr, Marion, IN 46953
C Boulevard Suites, 2120 W 2nd St, Marion, IN 46952
Super 8 Gas City Marion Area, 5172 Kaybee Dr, Gas City, IN 46933
Super 8 Motel Marion IN, 1615 N Baldwin Ave, Marion, IN 46952
Hampton Inn Marion, 1502 N Baldwin Ave, Marion, IN 46952
Hart Motel, 1411 N Baldwin Ave, Marion, IN 46952
Courtesy Economy Inn, 1370 N Baldwin Ave, Marion, IN 46952
Comfort Suites, 1345 N Baldwin Ave, Marion, IN 46952
Loft Inn, 7911 S 150 E, Fairmount, IN 46928
Accounts payable will only reimburse me for their pre-determined distances. I believe someone in the organization from 35+ years ago (i.e. pre Google Maps) literally made the trip to each site and recorded the mileage to the nearest tenth and we still use that chart to this day. When turning in my mileage, I always must reference the matrix, so we won’t be able to use Google Maps’ routes/distances in the real file, but I understand the need for addresses for demo purposes.
Assuming travelling #1 → #2 is the same mileage as #2 → #1 (and no trips to/from the same location #1 → #1) the n(n-1)/2 formula means your 9 locations inply 36 discrete mileages to place in a dictionary or look-up table, or as 36 discrete notes holding the two end points and the allowed mileage.
Though #1 → #2 is the same mileage as #2 → #1, the start/end stored as a key gives two discrete keys (‘12’ vs. ‘21’. To avoid using 72 mileage keys (36 * 2) use a function where passing in ‘12’ or 21’ results in ‘12’ at output:
function tripTest( iTrip:string){
// we know trip codes are always 2 number characters in this scenario
// split the two digits
var:string vStart = iTrip.substr(0,1);
var:string vEnd = iTrip.substr(1,1);
// a variable for the result
var:string vResult = iTrip;
if (vStart > vEnd){
vResult = vEnd+vStart;
}
return vResult;
}
var: string vTripCode
// for a trip '12'
vTripCode = tripTest( "12"); // returns '12'
// for a trip '21'
vTripCode = tripTest( "21"); // returns '12'
So you only need to store the mileage for ‘12’ and ‘21’ once, i.e. 36 discrete trip codes. Where, you store those codes (as notes or in a dictionary) is up to you.
Here is a mileange expense demo using all the elements of the thread above. The Trip Matrix Calculator will not work until you put a Google Maps API key in the TBXConfig note. The XML parser will not work until the next public release (which I understand is coming soon).
I used @webline’s Google API code to produce a list of trip options (see /Resources Folder/Trips Folder) to produce a trip matrix from @BrianP’s provided hotels.
You can try it by adding a new trip in Expensed Trips and selecting a “Trip Route” from the selector. The Action code will look up the distance and time and calculate the expense. You can name the trip anything you’d like.
and here is my final version of the demo - two stamps to play with the Google Distance Matrix library:
GetDistanceByLatLon will use the lat/lon attributes of a note (“Sample”) to calculate the distance, duration… GetDistanceByAddress will do the same with two addresses (you find them in the code of the stamp).
The main function is callDistanceMatrixAPI_Main_db. You can call it with lat/lon of the origin and destination of your trip:
The returned data format is a dictionary and contains the raw data returned, the addresses of destination and origin, the distance and the duration of the trip.
You can pass optional parameters like “mode” or the departure time if you like.
Don’t forget to enter your Google API key into the gPreferences note before playing with the API.
I want to publicly thank @satikusala for taking time to work with me on this. He has graciously given up his time to develop this and has been super patient with my novice-level knowledge on the subject.
There is a lot of potential power behind keeping my mileage log in TB, so I’m excited about making this a part of my workflow.
As I gave generic sites for the demo, I will need to make revisions to tailor this to my own line of work. I thought it would be as easy as going into the ‘Places Folder’ and renaming the existing places to be in line with my actual sites. That didn’t do the trick, so next I dug deeper and thought I might need to rerun some code @satikusala mentioned that auto-generated the 81 different route combinations that are stored in the ‘Trips Folder’. I opened the action inspector for the ‘Trip Matrix Calculator’, went into the ‘Rule’ tab and clicked the “Run Now” button thinking that might create new route combinations, but no luck.
Since I have a mileage matrix I must adhere to, I won’t need the Google Maps option, but I feel pretty good about manually editing the route distances once I can get them renamed.
This process is exactly what I was looking for though. I think by having a model file from which I can work will help me learn a great deal. Exploration and trial and error is my key to learning, and this has been great, even if I haven’t figured it out yet. Any nudges in the right direction to accomplish this? At the risk of being tedious, I’d prefer nudges over direct answers if possible.
@BrianP here is an idea. Create a spreadsheet like the attached. Save it as CSV. Drag the CSV into TBX. Now you have all your possible routes. You can archive out or delete the existing notes. Take a look at the action code in the Trips Folder Rule, you’lls ee that this is populating the attribute $TripRoute with all the available trip options. That way, these routes will be available for you when you create your trips.
