Tracking system for submission packages

[VerneUngohe]

As part of my job, I am required to assemble document packages and submit them to a governmental regulatory agency.  I am trying to develop a system to track the submissions and to save the relevant documentation, and I am wondering what approach is best.

The data I am tracking includes project names and numbers, names of people, milestone dates, contact information, review status, etc.  The submission packages are large and can include 20 or more individual files.  We receive communication back from the agency during the review and approval process, and these letters need to be saved and easy to locate.

I initially created a table in Excel to track the submissions and a network folder to sort the files.  This worked very well, but I suspect there is a more modern approach that would work better with our company's systems.

My second attempt was to use MS Lists.  Lists allows me to display the data very nicely, but I find the ability to attach files to each entry is lacking.  As far as I'm aware, there is no way to create folders within a Lists entry.  Without being able to sort my files into subfolders, important communications are time consuming to find among all the submission files.

My most recent attempt has been to create a SharePoint document library.  This seems to work well enough.  I can create a folder and customize the columns to list the tracking data I'm concerned with.  Within the folder, I can create all the folders and subfolders I want.  My complaints with this approach are that it seems to lack the colour coding that conditional formatting in Excel and Lists allows, and it's not very user friendly to create a new library entry or update an existing one (compared to Lists or Excel).

Does anyone have any recommendations?  Is there a better tool or approach I could be using?

[#2]

You could use a combination of the two. Use the list for your tracking data and use a hyperlink or look up column to refer to the documents within a library. It may also be worth utilising metadata rather than a folder structure that can improve searchability and you can create custom views for your files. You could use the metadata as the look up column to add each file to your tracking item. Happy to discuss my thoughts in more detail

[#3]

These are interesting ideas.  Please provide me with more detail.  I am very new to creating sites with Lists or with SharePoint, so I would be grateful for as much information as you can provide.  @Josh_Wickes 

[#4]

@VerneUngohe 

With the information you have provided I would create a basic library and add a column for the project name and file name, I would then create a workflow to update the title of the document to a naming convention such as [Project name] - [File Name]. then create your tracker, list in the list create a look up column that looks up the title column of your document library, you can the add you documents to the library and fill in the relevant metadata. Then return to the tracker and create your tracking item in the look up you can reference each of the documents with the correct project name. You could also create a flow that updates the look up column automatically with files. I will mock something up for you soon for more info 

[#5]

Thank you. A mock-up of this would be very helpful.