Inventory Solution for Tech Services: Tracking parts requested, checked out, and status updates

Hello everyone,

I work in inventory at a growing MNC that handles equipment sales and service. As our location’s team has expanded (added remote technicians and dispatchers across the state), our current system for tracking part requests and checkouts for service can’t keep up. We use Microsoft Dynamics, Office 365, and Front; most of our crucial information is logged in shared excel sheets (P.Os, transfers, outgoing shipments..) stored in a company shared drive. I am intermediate with Excel, largely self taught.

So. I already created a structured email/ticket system for requesting parts, which has significantly reduced the amount of reqs missed. Basically, dispatch uses a specific template to submit their request via email, which will then route to a ticketed inbox just for part requests. However, tracking parts *after* staging/shipping is still a major issue.

Key Problems:

1. Too many variables:

   Jobs can last months, involve multiple parts, techs, and locations, and change constantly. Updates come from many sources with no consistent tracking.

2. Duplicate work:

The same info is entered and uploaded repeatedly across logs, tickets, emails, comments, and calendars, then relayed to multiple people—and all must be redone whenever a change occurs. Each time. For every part. For every request.

1. Poor visibility:

Ticket statuses (“open,” “waiting,” “resolved”) are too vague to reflect real progress. Tickets could be marked as ‘waiting’ for 10 different reasons. There is also the issue of someone accidentally marking a ticket as resolved prematurely and causing chaos.

1. No easy filtering:

We can’t quickly sort requests by tech, location, or status if needed. I can’t see at a glance what is pending where and why, each open ticket must be skimmed.

1. Errors from outdated info:

Our shipping team references a printed a copy of the original request for shipment, which is non-editable due to being an email. This does not reflect any changes requested via comments within the ticket. To prevent a mishap, shipping must pull up the ticket and read the dozens of comments beneath.

Do you think a centralized Excel tracker would be able to address/fix these issues?

The following would be tracked per service. Most information is included in the request, but the issue is whenever there’s a change:

* Dispatcher, assigned inventory team member, and assigned technician

* Internal reference #

* Service location/date

* Shipping instructions

* Requested vs. supplied parts

* Part source (each part can have a different source: stock, PO, transfer, etc.)

* Shipment/pickup dates of each item

* Detailed, changeable status (New, staging, backordered, pending pickup, etc.)

Ideal Features:

* Auto-create entries from incoming requests

* Auto-Link to related documents (e.g., packing slips)

* Email notifications to various parties when updates are made

* Can select an entry and instantly create a printable sheet with that info

* Centralized notes/communication for all stakeholders

* Highlights entry when listed date of service passes

I’m open to changing the request process entirely if there’s a better approach. Is Excel a viable solution for this, or should I be looking at something else? I don’t know where to begin with this or if it would even be possible.

Also open to paying someone for a consult.