Determining value from

[William_Cain]

Hi everyone, first day on Airtable. I am currently transitioning my spreadsheets from Excel.

Basically, I have two records with the same field (Trip #) with unique data in each record. The unique data that i am trying to compare are the odometer readings and determine the exact miles that were driven from the pickup of record one to the delivery record for record two.

I changed the Trip # to a date format in attempts to use the “is_same” function but it keeps returning the individual mileage for the each row and not an aggregated total of the odometer readings into the loaded miles field.

Thank you in advance for any help or insight that you might have.

[Julian_E_Post]

Hi William! Welcome to the forum. The problem that you’re running into highlights a fundamental difference between Excel and Airtable. Airtable treats every record the same, whereas Excel is more free-form. While it may be frustrating at the beginning, I’ve found that Airtable ultimately pushes me to create better, more organized tables.

Can you change the way you record pickups and deliveries so that they’re part of the same record? One “pickup” date field, one “delivery” date field, a “pickup mileage” odometer reading, and a “delivery mileage” odometer reading. If you can’t do this, and you really want to keep the pickup and delivery records separate, would you share some screenshots of your excel doc? I’d be happy to look at it and see if there’s a way to achieve the same system in Airtable

[William_Cain]

Good Morning Julian,

Thank you. I really do enjoy the functionality of AirTable. It is much cleaner than Excel. I have added some screenshots the original post. I would like to keep the original data that is in the spreadsheet because, as you will see, each data set is unique to itself but can also have overlapping data with another record (as in mileage).

The goal is to make it simple for team members to input data with out further manipulation of the table.

Is there a way to make primary record and then attach or link subsequent records to the primary one? I think that this could resolve the problem. Have a primary trip one that would record the overall pickup odometer and delivery odometer but the linked records could just show the specifics for that load… like I said I’m new to this program and don’t fully understand the capabilities and limitations of the operating system. Thanks again for the help, I look forward to hearing back from you.

[Julian_E_Post]

Yes! This is a fun one. Take a look at this example base that I think does what you’re looking for. If I can summarize your goal: You would like the driver to be able to fill out one form to tell you about both a delivery and a pickup, and have both groups of information (delivery info and pickup info) record to the right job. For this to work in the base I shared with you, you need to enter job names into a “Jobs” table ahead of time, so that the driver can pick the job that their odometer etc. relates to. When filling out the form, the driver then can select the pickup job and the delivery job.

Airtable

Airtable | Create apps that perfectly fit your team’s needs

Airtable is a low-code platform for building collaborative apps. Customize your workflow, collaborate, and achieve ambitious outcomes. Get started for free.

Does this help?

[William_Cain]

I can see how that can simplify the operation and make it easier for drivers to input data. However, it still doesn’t have the complexity of solving for overlapping loads. Since my drivers are flatbed operators, we can haul multiple loads from/to multiple pickup/delivery locations on the same trip. The goal is to obtain an accurate rate per mile (RPM) for each customers load and for the totality of that trip.

If you look at trip 2020-01-21 in the screenshots, the first load was picked up in Rhode Island and was delivered in New Mexico. Along the trip the driver picked up an additional load in Tennessee that was being delivered in California. It is reflecting the proper RPM for each individual load but on the “Loaded Miles” it reflects the sum of the two loads and not the aggregated total. Its the shared miles from the 2 loads that is the problem because it is double counted when it comes to finding the combined RPM.

Sorry for over explaining it just want to be clear.

[William_Cain]

Alrighty, I’ve been plugging away at this all morning. The best I can figure out to do is to create a separate table that rolls up information from the specific “Trip #”. I used the the ‘min’ and ‘max’ on the new table with the “roll-up” function. this gives me the aggregated miles for loads that share overlapping odometer readings. I think this is the best way to present this data. Thanks for the help!