0

Mileage Chart Spreadsheet to Ninox Table

What would be the best way to put the data from a Mileage Chart Spreadsheet into a Ninox Table for look up?

These are the fields that I am thinking of…Origin, Destination, Mileage.

But, it seems that if I did that, then I would need several destination records for each origin record, and probably a lot of unnecessary typing.

I frequently travel between (and get paid for the miles between) the same 20 destinations. So, I am looking for an elegant solution to look up the miles, based on the origin and destination.

Mileage Chart Spreadsheet Sample

7 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view

    You could also use the google API, then you don't need a table. Several years ago I once created a google example API database which you can find here.
    More info about the google distance API can be found here.

    Steven

      • Truck Driver
      • Chris.6
      • 1 yr ago
      • Reported - view

      RoSoft_Steven Thanks for the suggestions. Unfortunately, Google is for cars, and I need information for Commercial Truck Driving. See more context in my reply to the topic below.

    • Fred
    • 1 yr ago
    • Reported - view

    Here are my two cents.

    There are five tables:

    States

    Cities

    Trips

    Log

    LogTrips (child of Log)

    The first two do what their name suggests.

    Trips is where you put in the two cities and the distance.

    Log is where you enter in a date then in a child table track the trips taken that day.

    LogTrips is where you track which trips you made on a particular day. You can add one trip or many trips.

    There is three ways of tracking trips so take a look in the DB.

      • Truck Driver
      • Chris.6
      • 1 yr ago
      • Reported - view

      Fred Thanks for taking a crack at my problem. Your “two cents” might get me on a path to figure this out. Please see my reply to the topic for more context.

    • Truck Driver
    • Chris.6
    • 1 yr ago
    • Reported - view

    Thanks for your suggestions. Allow me to add some context that may help explain how I need to use the Mileage Look Up Table.

    Unfortunately, as a truck driver, the industry standard for mileage is the Household Goods Mileage Guide (aka HHG) which is compiled and sold exclusively through Rand McNally as MileMaker: https://fleet.randmcnally.com/product/mileage-route-planning

    For my purposes, I run between the same 18 or so places, so, at this time, I don’t need (or want) the full Rand McNally suite. The Google API is of no use, since it uses practical miles and is designed for cars.

    I was hoping that I could somehow create a simple look up table using my 18 locations (I can easily plug in and update the Household Goods Mileages between those 18 locations based on my paid mileage history).

    At this time, I don’t need to use the mileage chart to plan a route or track trips; I need the mileage chart to look up paid miles.

    Perhaps, at some point, I could use Rand McNally Milemaker, but at that point, I would negotiate a price for the use of their API. I have many miles to drive before getting to that point. 😉

    Perhaps, after gaining some contextual understanding, you are willing to take another crack at my problem.

    Any help is appreciated, but I understand if you would rather move on to other issues.

    Chris

    • Fred
    • 1 yr ago
    • Reported - view
    Chris Thomasson said:
    At this time, I don’t need to use the mileage chart to plan a route or track trips; I need the mileage chart to look up paid miles.

     I guess I'm missing something, but isn't my example a way to look up paid miles? You create a new trip log and it will look up the distance. I did forget to include a formula to add up all of the distances in a day. You can create a formula field in Log and put:

    Depending on which method you are using:
    
    sum(logtrips.Distance)
    
    or
    
    sum(logtrips.Distance2)
    

    Then this will show you the total distance in a day.

      • Truck Driver
      • Chris.6
      • 1 yr ago
      • Reported - view

      Fred Thanks for the reply. 

      Yes, and Thank You! 

      Your example is definitely a way to look up the paid miles! The work flow got me confused, as I have no need to track miles by the day; I track the miles by the Load ID. Most loads take several days to deliver from point A to point B (think Philadelphia to Los Angeles). Although I don’t need to track the miles on a daily basis, I do sum the paid miles per pay period (weekly pay), month, quarter, and year, so that I can make comparisons. At this point, I am manually entering in the paid miles for each load.

      My trips and miles are attached to a Load ID (which has a start date and end date). Most of the truck loads (trips) take more than one day. Now that I have looked more deeply into your example, I’m pretty sure that I will be able to adapt it to my situation.

      My current work flow is something like this:

      • Create a new Load Information Record (this is where the Load ID is entered, among other things, but EVERYTHING is tied to the Load ID). The Load Information Table is the table that every other table is related to, some as children, some not.
      • Enter Start and End Locations (within the Load Information Record). I am doing this with location fields, which is useless, other than to mark the location in Apple Maps.
      • Create a Mileage Pay Record (a child table of the Load Information Table) and manually enter ‘Empty Miles’ and ‘Loaded Miles’ which is automatically summed in ‘Paid Miles”.

      What I would like the work flow to be:

      • Create a new Load Information Record
        • Enter/Select ‘Start Location’
          • Select whether ‘Empty’ or ‘Loaded’;
        • Enter/Select  ‘Stops’ (these are possible pick up and/or drop off locations, entered as necessary in fields ‘Stop 1’, ‘Stop 2’, ‘Stop 3’, etc.)
          • Select whether ‘Empty’ or ‘Loaded’ for each ‘Stop’
        • Enter/Select ‘End Location’
      • Create new Mileage Pay Record
        • ‘Empty Miles’ and ‘Loaded Miles’ are automatically populated on creation of Mileage Pay Record

      I’m not asking anyone to create my DB, just providing context. But I think having a way to look up and create “trips” is the key to unlocking the work flow that I’m hoping for.

      Thanks again for giving me something to work with.

      Chris

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 7Replies
  • 175Views
  • 3 Following