This template is meant to help managers of car dealerships, especially those who deal with vehicle repairs and maintenance. With the help of this template, you can efficiently manage your mechanics or service advisors. You can also use it to keep track of your customers’ vehicle service histories. There are also different table views that can better meet the needs of your employees, customers, and their cars. To get up to speed with this template, populate the lookup tables listed below first, followed by the others. Start with Makes and models, then Branches, Staff, Parts and labor, and then Service packages. After you have set up all of these tables, you can move on to the tables you will use most often. Go ahead and continue with Customers, Customer cars, and then the main table, Service logs. Start managing your car dealership services better with the use of this template, and understand how the entire database works by going through each table:

Service logs

This is the core table for this database. You’ll probably find yourself using this table the most. All service transactions at your dealership should be recorded in this table. It also automatically figures out the total cost of each transaction. The background of records where the vehicle and owner don’t match is highlighted to help you fill in your data.

Fields

• ID. An autogenerated primary key that serves as the unique identifier for this service record or row.
• Service date. The date a vehicle was brought into the dealership for service.
• Release date. The date a vehicle was released to the customer after servicing.
• Status. The current status of the service (being) rendered on a vehicle.
• Duration (hours). The amount of time, in hours, that a service was done on a vehicle.
• Service description. A full list of all the services done on a vehicle.
• Customer. This field is linked to the Customers table, specifying the customer who brought the vehicle for service.
• Customer address. This field is a lookup based from the Customer field, showing the customer’s address.
• Vehicle ID. This field is linked to the Customer cars table, specifying the vehicle being serviced.
• Year. This field is a lookup based from the Vehicle ID field, showing the year the vehicle being serviced was manufactured.
• Make. This field is a lookup based from the Vehicle ID field, showing the manufacturer or brand of the vehicle being serviced.
• Model. This field is a lookup based from the Vehicle ID field, showing the model and trim of the vehicle being serviced.
• Color. This field is a lookup based from the Vehicle ID field, showing the color of the vehicle being serviced.
• Type. This field is a lookup based from the Vehicle ID field, showing the body type of the vehicle being serviced.
• Plate number. This field is a lookup based from the Vehicle ID field, showing the plate number of the vehicle being serviced.
• VIN. This field is a lookup based from the Vehicle ID field, showing the vehicle identification number of the vehicle being serviced.
• Engine number. This field is a lookup based from the Vehicle ID field, showing the engine block number of the vehicle being serviced.
• Current mileage. The current odometer reading or mileage of the car when it was brought into the dealership for service.
• Service branch. This field is linked to the Branches table, indicating the branch of the dealership where the service was rendered.
• Services executed. This field is linked to the Service packages table, specifying all services rendered to a vehicle.
• Service technician. This field is linked to the Staff table, indicating the mechanic who worked on a vehicle.
• Discount (%). The (variable) discount percentage from promotions or special transactions given to the customer for this service.
• Total cost. The overall price of all the work done on a vehicle during this session.
• Invoice number. A reference to the customer’s sales or billing invoice number for this transaction.
• Supervised by. This field is linked to the Staff table, specifying the staff supervising the service and communicating with the customer.
• Transaction files. This is where files and receipts related to the service are uploaded.
• Owner verified. A formula field that checks whether the customer matches the vehicle’s owner. This field is hidden by default.

Views

• All service logs. Displays all service transaction logs without filters, sorted by Service date from the most recent to the oldest.
• Add service log. Displays a form view that facilitates adding new service transactions to this table.
• Released. Displays service transactions that are marked as Released, sorted by Service date from the oldest to the most recent.
• Ongoing. Displays service transactions that are still ongoing, sorted by Service date from the oldest to the most recent, then by Status.
• CD Fremont. Displays all service transactions in the CD Fremont dealership branch, sorted by Service date from the most recent to the oldest, then by Status.
• CD La Mesa. Displays all service transactions in the CD La Mesa dealership branch, sorted by Service date from the most recent to the oldest, then by Status.
• CD Pomona. Displays all service transactions in the CD Pomona dealership branch, sorted by Service date from the most recent to the oldest, then by Status.
• CD San Leandro. Displays all service transactions in the CD San Leandro dealership branch, sorted by Service date from the most recent to the oldest, then by Status.
• By status. Displays a kanban view of all service logs, stacked by Status.

Service packages

Your dealership may offer a lot of helpful services to your customers. With this table, you can manage all of your dealership’s services into packages that are easy to index and can be used in customer transactions.

Fields

• Title. A unique name or title given to a group of services or a job order that is put together for customers.
• Description. A full description of all services within this service package.
• Items. All vehicle parts and labor included in this service package.
• Cost. Total cost of this service package.
• Job order count. The number of times this service package was delivered to customers.
• Related service logs. This field is linked to the Service logs table, linking all services that included this service package. This field is hidden by default.

Views

• All service packages. Displays all service packages without filters.
• Most used packages. Displays service packages, sorted by Job order count from most to least.
• Priciest packages. Displays service packages, sorted by Cost from most to least.
• Cheapest packages. Displays service packages, sorted by Cost from least to most.

Parts and labor

When your service items come in groups or sets, they can be bought and sold more quickly. This table lets you keep track of your auto parts and even labor fees in sets. If rows are found to be bundles, a gray bar appears on the left border of the record.

Fields

• Name. A unique name or title given to a set of vehicle parts or labor fee.
• Quantity. The number of items in a set.
• Description. A full description of the all the items in the set.
• Price. The standard retail price of an item in the set.
• Discount (%). The specified fixed discount for the set of vehicle parts.
• Total. The total price, after discounts, of this set of vehicle parts or labor.
• Availability. A single select field that indicates whether the auto part is available, low stock, or out of stock.
• Sourced by. This field is linked to the Staff table, indicating the parts technician who procured this set of auto parts.
• Use count. A formula that counts the number of service packages that included this set or item.
• Related packages . This field is linked to the Service packages table, linking the service packages that included this set or item. This field is hidden by default.

Views

• All parts and labor. Displays all auto parts and labor fees without filters.
• Most availed parts. Displays all auto parts, sorted by Use count from most to least.
• By availability. Displays a kanban view of all auto parts, stacked by Availability.

Customer cars

This table holds information about all of your customers’ vehicles. It also works as a lookup for other tables, such as Service logs and Customers.

Fields

• ID. An autogenerated primary key that serves as the unique identifier for this vehicle record or row.
• Owner. This field is linked to the Customers table, specifying the customer who own this vehicle.
• Photo. Any photo or photos of the actual vehicle/stock/unit can be uploaded and attached to this field. You may include photos of this vehicle’s engraved ID numbers.
• Model. This field is linked to the Makes and models table, indicating this vehicle’s model and variant/trim.
• Make. This is a lookup field that specifies this vehicle’s maker or brand; the information was referenced from the Model field.
• Year. The year this vehicle was manufactured or released from factory.
• Type. This is a lookup field that shows the type of body or category of this vehicle; the information was referenced from the Model field.
• Color. This vehicle’s exact color name defined by the manufacturer.
• Plate number. The vehicle’s specific registration plate number.
• VIN. This vehicle’s unique chassis number or frame number.
• Engine number. The vehicle’s unique engine block identification number.
• Remarks. Any other notes or brief description about this vehicle.
• Service count. A formula that counts the number of times this vehicle was serviced by the dealership.
• Model for sorting. A formula field that converts the Link-to-table field, Model, into text for sorting purposes. This field is hidden by default.
• Make for sorting. A formula field that converts the Link-to-table field, Make, into text for sorting purposes. This field is hidden by default.

Views

• All vehicles. Displays all customers’ vehicles without filters, sorted alphabetically by Make for sorting, Model for sorting, and Year.
• Add a customer car. Displays a form view that facilitates adding new customers’ vehicles to this table.
• Cars. Displays a gallery of all customers’ vehicles, sorted alphabetically by Plate number.

Makes and models

This is a table where you can look up the make, model, and trim of all vehicles, as well as details about the variant.

Fields

• Model and variant. The manufacturer’s defined model and variant.
• Make. The manufacturer’s name or brand.
• Photo. The official marketing photo or photos of the vehicle can be uploaded and attached to this field.
• Year model. This is the year this specific model or trim was launched by the manufacturer.
• Type. The type of body or category of this vehicle
• Engine size (L). The total volume of the engine’s cylinders in Liters.
• Transmission. A single select field specifying the type of transmission for this vehicle model and variant.
• Fuel type. A single select field indicating the type of fuel required for this vehicle model and variant.
• Seat count. The number of passenger seats, including the driver’s, inside this vehicle model and variant.
• Body length. The overall body length of this vehicle model and variant.
• Body width. The overall body width of this vehicle model and variant.
• Body height. The overall body height of this vehicle model and variant.
• Customer car count. A formula field that counts all customer vehicles that have ever been serviced by your dealership with this make, model, and variant.
• Related customer cars. This field is linked to the Customer cars table, specifying all customer vehicles with this make, model, and variant. This field is hidden by default.

Views

• All makes and models. Displays all vehicle makes and models, sorted alphabetically by Make and Model and variant fields, and finally by Year, from most recent to oldest.
• Makes and models. Displays a gallery of all vehicle makes and models, sorted alphabetically by Make and Model and variant fields, and finally by Year, from most recent to oldest.
• By manufacturer. Displays a kanban view of all vehicle makes and models, stacked by Make.
• By type. Displays a kanban view of all vehicle makes and models, stacked by Type.
• By transmission. Displays a kanban view of all vehicle makes and models, stacked by Transmission.
• By fuel type. Displays a kanban view of all vehicle makes and models, stacked by Fuel type.

Branches

Some dealerships have more than one place where they service or sell cars. Use this table to find out more about each branch of the dealership.

Fields

• Location. The name of the dealership branch.
• Photo. Any photo or photos of the actual dealership branch can be uploaded and attached to this field.
• Street. This is the street where this dealership branch is located.
• City. This is the city where this dealership branch is located.
• State. This is the state where this dealership branch is located.
• Zip code. This is the zip code where this dealership branch is located.
• Phone number. This dealership branch’s main phone number.
• Current job orders. A formula that counts the current number of vehicles being serviced in this dealership branch.
• Service count. A formula field that counts all customer vehicles that have ever been serviced by this dealership branch.
• Personnel count. A formula that counts all the staff members assigned to this dealership branch.
• Related staff. This field is linked to the Staff table, specifying all the staff members assigned to this branch. This field is hidden by default.
• Related service logs. This field is linked to the Customer cars table, specifying all the customer vehicles in this branch’s history. This field is hidden by default.

Views

• All branches. Displays all dealership branches, sorted alphabetically by State, City, and Location fields.
• Branches. Displays a gallery of all dealership branches, sorted alphabetically by Location.

Staff

This table shows how to get in touch with the people on your staff. It is also a lookup for other tables in the database.

Fields

• Name. The full name of the staff member.
• Photo. Any photo or photos of the staff member can be uploaded and attached to this field.
• Role. A single select field specifying the role, title, or responsibility of this staff member.
• Branch. The dealership branch this staff member is currently assigned.
• Local extension number. The staff member’s local extension number.
• Phone number. The staff member’s phone number.
• Email address. The staff member’s email address.
• Remarks. Any other notes or brief description about this staff member.
• Current job orders. A formula field that counts all job orders in progress assigned to this mechanic, service technician, or supervisor.
• Supervision count. A formula field that sums up all job orders this staff has supervised.
• Service count. A formula field that sums up all job orders this staff has worked on as a mechanic or service technician.
• Is active. A checkbox field that indicates whether this staff member is still active at the dealership.
• Related service logs. This field is linked to the Service logs table, linking all job orders that this staff member has worked on. This field is hidden by default.
• Supervised services. This field is linked to the Service logs table, linking all job orders that this staff member has supervised and communicated to customers. This field is hidden by default.
• Related parts and labor. This field is linked to the Parts and labor table, linking all auto parts that this staff member has procured for the dealership. This field is hidden by default.

Views

• All staff. Displays all staff members from all dealership branches, sorted alphabetically by Name.
• Add staff member. Displays a form view that facilitates adding new staff members to this table.
• Staff members. Displays a gallery of active staff members from all dealership branches, sorted alphabetically by Name.
• By role. Displays a kanban view of all staff members from all dealership branches, stacked by Role.

Customers

Use this table to figure out how to contact your customers and learn more about them.

Fields

• Name. The customer’s full name.
• Photo. Any photo or photos of the customer can be uploaded and attached to this field.
• Street. This is the street of the customer’s address.
• City. This is the city of the customer’s address.
• State. This is the state of the customer’s address.
• Zip code. This is the zip code of the customer’s address.
• Phone number. The customer’s phone number.
• Email address. The customer’s email address.
• Transaction count. A formula that counts all service transactions this customer had with your dealership.
• Car count. A formula that counts all vehicles that this customer owns and that were serviced by your dealership.
• Avg transaction. A formula for finding the average price this customer has paid for all of his service transactions.
• Total paid. A formula that adds up all the payments this customer has made for services.
• Remarks. Any other notes or brief description about this customer.
• Related services. This field is linked to the Service logs table and shows all of the service transactions that this customer has had with your dealership. This field is hidden by default.
• Related customer cars. This field is linked to the Customer cars table and shows all of the vehicles that this customer owns. This field is hidden by default. Views
• All customers. Displays all customers from all dealership branches, sorted alphabetically by Name.
• Add customer. Displays a form view that facilitates adding new customers to this table.
• Top customers. Displays customers, sorted by Total paid from most to least, then by Avg transaction from most to least.
• Customers. Displays a gallery of all customers from all dealership branches, sorted alphabetically by Name.