Greetings! I’m a TB owner (v9.1.0) but have yet to make a breakthrough in being able to grasp it. I figured I’d take another shot at it and see if I could recreate a tool I already use, which is a mileage tracker that I keep in Google Sheets. It may not make any sense at all to keep track of this in TB, but I figured it’s worth a shot. I currently travel between sites on a semi-daily basis. There are days I may visit multiple sites, and there may be days where I stay in my office. Generally speaking, no two days are alike. I built my Google Sheet with three tabs:
Distance sheet, which is a cross-referenced table that stores the distance between each site.
Entry sheet, which is where I enter into three columns the date of my travels, the starting site and the destination site.
Result sheet, which is where the sheet performs calculations. It references my starting and ending points against the combination in the distance sheet and returns the distance back to the result sheet. I never need to keep track of calculations - I just need to know where I started and where I ended. I then export the result sheet, massage the layout a bit to satisfy AP, and submit.
This all works well enough… again, I just thought attempting this in TB might be the gateway that I need to make some breakthroughs in understanding TB.
I was thinking I could keep a note per month and record the same data (date, starting point, destination point). The OCD part of me was thinking I could add a table to a note, but I haven’t had any success in experimenting or searching the forums or the web. So then I decided maybe I would just skip the idea of a nicely-formatted table and enter plain text row-by-row. (i.e. 27-June-2023, siteA, siteB) Either way will require some parsing of the data stored within the note and that’s a whole other rabbit hole.
It just feels like this might not be the best tool for the job, but again I was looking for a gateway into this - taking something familiar and recreating it in TB. To extend, I thought in time I would layer on some additional functionality that would help me see which sites I spend more of my time, and if I go deeper on the logging, perhaps which sites require additional needs and/or support.
Any thoughts, advice, or resources that you could share would very much be appreciated. I’m also open to you just saying this isn’t the right tool for what you have in mind. Maybe I just need to stick with Google Sheets for this.
Oh, sure: I can see a bunch of ways to do that in Tinderbox. Of course, it’s pretty straightforward in a spreadsheet! But sure, we can do it in Tinderbox.
One approach might be to have a container for each month, that contains a bunch of notes — each describing one trip:
▸ August 2024
August 1 | Paris | Lisbon
August 2 | Lisbon | Madrid
August 3 | Madrid | Barcelona
August 4 | Barcelona | Montpelier
We’d view this in an outline view with columns ($DisplayName, $Start, $End). Later, we’ll compute the distance and add another column for $TripDistance.
Further to the last. Tinderbox doesn’t have a spreadsheet view as such, although a few views (Outline and Attribute Browser view) allow a display of columns that look like the grid of a spreadsheet.
An analogy is that to put your data simplistically into Tinderbox, for each spreadsheet row you’d make a Tinderbox note. The values of spreadsheet’s column #1 goes as the note’s name and then you’d need to choose a note attribute to which to assign each other column’s data.
So the spreadsheet metaphor is unhelpful to you her given you can’t simply recreate the sheet verbatim in Tinderbox and whilst functions like totalling distances are available they aren’t interest in the way or place you do so in a spreadsheet. So think less about the physical layout of the spreadsheet but instead the parts of the problem. This should help make the advice above.
Also consider how how want to use the arising data and how it is created. Do you want (need?) a note per trip (segment) or a note per day?
Wow! I feel like one of the contestants on Shark Tank - I get to talk to three big sharks right out of the gates!
Thank you so much @eastgate, @mwra, and @satikusala for your responses! Maybe for the potential benefit for others, I’d be game to play this out here. Then perhaps, if Michael is still willing, we can do a Zoom call a little later in case I have missed some things.
@mwra, thank you for your helpful response re: the spreadsheet. That helps put me at ease that I was not finding the right way to add a table to a note. Your analogy and layout guidance is also helpful. Thanks for your help!
@eastgate, I think your suggestion makes a lot of sense. This prompts a new question: Let’s say I visited three different sites on August 1. Would I need to have three different notes for August 1, or would I do three different lines in the note for August 1? I may be putting the cart before the horse with that question, but it was front of mind, so I offered it up.
I really appreciate everyone’s input on this as I climb this hill. I’ll get there!
Tinderbox is no spreadsheet (glad it isn’t) - so you need a different approach. Based on what eastgate suggested I would add a note for each trip. Writing down the results of the visits and other things so the distances traveled is just one information of many.
You could add two simple functions:
function callDistanceMatrixAPI_db(){
// build the CURL command to call the OpenAI interface
// IMPORTANT: you need to enter your Google API key to run this
var:string myCURL_CMD = 'curl -s "' + buildDistanceMatrixAPI_payload_db($startLat,$StartLon,$destLat,$destLon) + '"';
doDebugLog_db("Step0:" + myCURL_CMD);
var:string curlRet = runCommand(myCURL_CMD);
doDebugLog_db("Step1:" + curlRet);
return curlRet;
};
The first functions takes four parameters of the current not: lat/lon coordinates of the start of your journey and the destination. It will pass those parameters to the 2nd function and return the distance between the two points and the travel time with your car:
@webline, thank you so much for taking the time to share your insights! I need to apologize that I wasn’t as descriptive as I needed to be in my original message. My workplace will only reimburse for the distance they have calculated between each site. I have never checked to see how Google Maps differs compared to their route distances, but I will have to use their pre-determined values in my calculations.
I do appreciate your response though, and hope it wasn’t too much trouble for your efforts.
@webline this is cool. I got it working. I’m not familiar with the API. Is it possible to pass an address, rath erthan a lat/log for the origin or destination? How would we have the JSON return populate the text of a note?
and the JSON will be copied into your note.
You could also add a parameter &mode with the values “driving” for going by car and “walking” for going by feet…
The distance is calculated via routing not the direct line between the points.
@BrianP I was just thinking about a solution that will use specific features where TBX has an advantage over a spreadsheet. Getting rid of a third tab in your sheet was the idea behind the code - no big trouble to do the little demo. And that’s what TBX is famous for: no 2nd community out there with this support - yeah! I got so much help here it is more then fair to give some back
Yes, let’s make a separate note for each trip. I think that’s probably the simplest thing that could possibly work
If we were using map distances, don’t forget the distanceTo() operator!
But we’re going to use a lookup table for distances. That’ll be fine, and I can think of several ways to do that. How many different sites need to be in the table?
is much easier to use - but it is a great circle route…
With the Google API you could specify the departure_time or arrival_time - think about trip planing with TBX
Or use the mode “bicycling” or “transit” (with transit_mode=train|tram|subway)…
You’re right. Works great. Here is a simple question. How can one get TBX to fetch the contents of the URL response and feed that into a variable so that it can be parsed?
For example, I have a URL returning this:
I’d love to have the page results return to a variable.
the variable returnedDestAddress, returnedOrigAddress, returnedDistance all work fine (expected content). The variable returnedDuration is empty because the parser is only able to return the first element of a list. Same problem: returnedElements contains “distance:text:19.0 km” nothing else.
So how could I retrieve the value of the “duration” in the valid JSON sample?
Yes, I understand that I can use the Stream operator. What I don’t know how to do is to scrape the results of the webpage that the API returns. The content is NOT in the URL, but rather displayed on the returned pages. Thoughts? Use a Fetch, maybe? I tried Fetch, did not sem to work. @eastgate, will AutoFetch return raw JSON? Does not seem so.
