- A separate note for each trip sounds perfect.
- 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.)
Yup, we just tested in backstage and it works great!
1 Like
Sorry for the typo using ( instead if [ - I’m getting old 
But I think the XML parser has the same problem as the JSON parser: no access to the 2nd element in a list.
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.
Let’s start with the demo data first. 
1 Like
Thanks, Michael:
Here are 9 hotels:
- 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.
Thanks!
Brian
[1], ][2], [3] give you access to specific elements in a list. More useful in most cases is .each
$Text.xml.each(/response/row){...}
1 Like
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.
I then use this matrix to calculate individual trip expenses (assume $0.33/km, see TBXConfig).
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.
TBX L - Mileage with Google Maps ShareR3.tbx (491.5 KB)
2 Likes
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:
var:dictionary target;
var:dictionary optional;
var:dictionary theResult;
target["start_lat"] = $startLat;
target["start_lon"] = $StartLon;
target["dest_lat"] = $destLat;
target["dest_lon"] = $destLon;
optional["mode"] = "walking";
optional["departure_time"] = "now";
theResult = callDistanceMatrixAPI_Main_db(target, optional);
$Text("result") = theResult["raw"];
doDebugLog_db("Destination Adress: " + theResult["dest"]);
doDebugLog_db("Origin Adress: " + theResult["orig"]);
doDebugLog_db("Distance: " + theResult["dist"]);
doDebugLog_db("Duration: " + theResult["dura"]);
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.
geolocation.tbx (179.7 KB)
2 Likes
@BrianP and I hoped on a zoom and we made a few updates to the demo file. For example, added basic reporting:
TBX L - Mileage with Google Maps ShareR3.tbx (491.5 KB)
2 Likes
just a minor one - I would change in the tTripReports template:
<tr>
<td><b>Trip Route</b></td>
<td><b>Date Trip</b></td>
<td><b>Explanation</b></td>
<td><b>Expense</b></td>
<td><b>Distance (KM)</b></td>
<td><b>Duration (Min)</b></td>
<td><b>Reimburasement Rate</b></td>
</tr>
to
<tr>
<th>Trip Route</th>
<th>Date Trip</th>
<th>Explanation</th>
<th>Expense</th>
<th>Distance (KM)</th>
<th>Duration (Min)</th>
<th>Reimburasement Rate</th>
</tr>
2 Likes
Yup, that would work. We were just doing it fast, not really thinking about some of the styling details.
Here you go (@BrianP), v3, updated the styling.
TBX L - Mileage with Google Maps ShareR3.tbx (491.5 KB)
This version has also turned the units (metric vs. imperial) as a variable. I also removed my Google API key.
1 Like
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.
Thanks!
Brian
1 Like
@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.
Matrix.csv.zip (1.4 KB)
1 Like
Wonderful! This worked very well! Thank you so much for the guidance. 
I notice I still have a leftover route in the matrix as well as the dropdown when I choose my TripRoute. (two screenshots attached) It almost seems to be associated with the ‘asString To asString’ route in the Trips Folder, but then again I can’t seem to explain the routes shown in the screenshot that contains the dropdown. Is it okay to delete the ‘asString’ file, or does it serve some other function? And any other thoughts on where I might find those two extra routes shown in the screenshot?
I just answered part of my own question pertaining to the C Boulevard Suites routes in the dropdown list. They were from other trip entries from our Zoom call. I deleted those two trip logs and now the extra two entries in the dropdown have disappeared.
So my last question, so far, still stands. Is it okay to delete the asString entry, or does that serve some other function?
Yes, you can delete the as string entries; they are just chaff from earlier automation. I’m glad this is working for you now.
Okay, so I’m making an attempt at contributing back to the community with my very humble additions to the TBX file shared in an earlier post.
Changes:
- Because I prefer entry via keyboard as much as possible, I learned how to utilize stamps to parse information from a note title and place it into various attributes. This allows me to quickly enter routes and dates and move on to my next entry. (Formatted as startingPoint/endingPoint/mm/dd/yy in the note title then applying a stamp) I learned this through an earlier Tinderbox Meetup video. Thank you for hosting this session!
- I slightly adjusted the formatting and layout of the reports.
- I added a Readme to document the file for future mileage loggers.
- I revised the CSV spreadsheet to accommodate my own needs. (attached)
Everything is included as attachments below. Nothing profound for sure, but I think it’s still worthwhile to share - if nothing more than for my first entry above.
Thank you again to everyone involved in this process. I never would have gotten this far without your help!
Brian
Mileage - Demo.tbx (272.0 KB)
Mileage Matrix - Demo.csv.zip (851 Bytes)
4 Likes
I’ve made some updates. I initially used the text response in the XML, which was fine for short trips, but once the trips got longer the display of the results got wonky. I switched to pulling the value from the google API, which use uses meters for distance and seconds for duration. I simply applied math to get the correct distance depending on the unit (i.e., imperial or metric) and converted seconds to minutes.
Here is the updated share file.
TBX L - Mileage with Google Maps R4.tbx (536.7 KB)
2 Likes
@BrianP here are answers to your outstanding questions
You need to use offset in your export code
$AttributeName(Offset).
An offset pulls data from another note’s attributes. You can do this by specifying a path, note name, and other ways. In this case, I specify the note name “TBXConfig.”
Grocking the concept of offsets is incredibly important as it lets you interact with data throughout our TBX file.
I’ve updated your readme (added an attribution, noted the template update, and removed two of your known issues). I’ve not tested the google API in this file.
BrianP Mileage - DemoR2.tbx (409.1 KB)
1 Like