Need suggestions on how to make a database more efficient or functional

[rcortez]

Hello! 

I am currently at a standstill. To make my long story short... I have a database that keeps track of vehicles and equipment. This included Maintenace records, mileage, locations, etc.  (Keep in mind I am not the only one who uses it) Because I have seniority with it, I am more familiar with it. However, I have come across a problem. In my database we have a tab names Mileage where we keep track of the monthly mileage for vehicles that we will be submitted to an outside program. I created a form to send out to employees that are responsible for vehicles (1 employee, 1 vehicle). This form automatically creates a record into the tab which I like but I do not want it to do that. or at least want it to do something else. 

I would want to do the following or get close to it: 

•  list of vehicles showing the 49 vehicles that need mileage information.
•  The mileage that gets submitted through the form updates the list (this would have to be monthly, and we cannot delete the past month) 
• Flags the vehicles that need mileage information. 

I have tried creating another base that syncs with my main base, but I can't get it to work. I have also tried creating a monthly place holder that would merge with the form data but have been unsuccessful. 

Any suggestions would be greatly appreciated. 

[TheTimeSavingCo]

> list of vehicles showing the 49 vehicles that need mileage information.
> The mileage that gets submitted through the form updates the list (this would have to be monthly, and we cannot delete the past month) 

Your current system with the form should work fine for this I think.  I assume you have a table that has all 49 vehicles in it, with one record per vehicle?  If so, link that table to the form (I think you've already done this)

After that, use conditional rollups and lookups on the created date of the form submission to display the data from the latest form submission.  Here's a link to a base where this can be seen in action

Creation / explanation:

1. Have all the records linked to a single record called `Rollup`
2. In the `Rollup` table, create a rollup field on the `Created Date` value from `Table 1` with the formula `MAX(values)`
- This will give us the latest date
3. In `Table 1`, create a lookup field to pull over the most recent `Created Date` from the `Rollup` table
4. Use a formula field to check the `Created Date` value against the most recent date pulled in via step 3

> Flags the vehicles that need mileage information. 
For this, you'd pull the form's submission date over to the table that holds all your vehicle data, and use a formula field to check whether that vehicle needed it's mileage information submitted, does that make sense?  Without knowing the exact criteria you use for this I can't suggest anything I'm afraid