Whether you’re working on a short-term project with a small team or managing an entire project pipeline with dozens of stakeholders, document management is an important part of any project management workflow. Document management is the process of collecting, storing, and tracking documents like contracts and certifications.
In this guide, we’ll take a look at a document management workbook built for a construction project and explain how key Spreadsheet.com features like Related rows, Form views, and data types can be combined to create an all-in-one document management solution.
Related rows, one of Spreadsheet.com’s data types, let you link rows in multiple worksheets so that your sheets can work like tables in a relational database where editing one changes the data in another.
For our document management workbook, Related rows connect our Subcontractors and Documents worksheets so that the two can share information.
Our document management workbook has two sheets – a list of Subcontractors and a list of Documents. Each document originates with one of our subcontractors. Although this information is held in different worksheets, the records are linked with Related rows.
Column D - Documents Submitted in our Subcontractors worksheet and Column A - Subcontractor in our Documents worksheet are connected by a Related row with a 2-way link. With a 2-way link, the relationship between records is reflected in both sheets; the Subcontractors worksheet shows each subcontractor’s submitted documents, and the Documents worksheet shows each document’s associated subcontractor.
By using a Related row with a 2-way link, we can use additional Related row data types like lookups and rollups in both sheets.
Column E - Documents in our Subcontractors worksheet is a Related row lookup column that “looks up” the attachments in Column E - Document from our Documents worksheet for each related record in the adjacent column. The Related row lookup lets us collect all of the documents for each subcontractor in one cell.
Just like the original Attachment column in the Documents worksheet, we can expand and view the attachments in our Related row lookup column.
Column F - Latest Submission in our Subcontractors worksheet is a Related row rollup column that aggregates data from Column F - Date Submitted in our Documents worksheet. Related row rollups support a variety of aggregation functions. Here, we’re using the MAX function to calculate the latest date that a subcontractor submitted a document.
As subcontractors submit more documents, the Related row rollup will automatically recalculate the latest submission date.
By adding a Form view to our workbook, we can collect new document submissions from subcontractors or other consultants and automatically add the responses as new rows in our Documents worksheet.
By adding a Form view to our Documents worksheet, we can collect incoming documents directly from subcontractors whether or not they have a Spreadsheet.com account or access to our workbook.
The Form view has a shareable link that can be distributed to our subcontractors. When they open the form, they can select the type of document that they’re submitting and attach a copy of it.
As subcontractors use the form to submit documents, each submission will be added to our Documents worksheet as a new row.
When new documents are submitted via the Form view, Column G - Date Submitted in our Documents worksheet automatically logs when the response was submitted with the Created at data type.
By adding an automation to our workbook, we can automatically notify a member of our team when a new document is submitted via the form. When new documents are submitted, the Related row lookup in Column G - Assigned to automatically assigns them to the subcontractor’s point of contact listed in Column C - Internal Contact of our Subcontractors worksheet. When a new document is submitted, our automation will automatically notify that contact via email.
In creating our automation, we can customize the notification message to include information directly from our worksheet, like the document type from Column C and the subcontractor name from Column A.
Now, when a subcontractor or consultant submits a new document via the Form view…
…all without any manual data entry or additional user input.
As subcontractors submit new documents via our Form view, we can use additional Spreadsheet.com features like conditional formatting and row grouping to manage document approvals and easily identify documents that require our team’s attention.
When new documents are submitted via our workbook’s Form view, they are automatically marked as “Not Reviewed” in Column H - Status of our Documents worksheet. This column is a Select column with additional options for “Approved” and “Rejected”. As members of our team review the incoming documents, they can update their status by choosing a new option from the Select dropdown.
The Select column is used to define our conditional formatting rules that change the row color based on the document’s status. Unreviewed documents are colored gray, approved documents are colored green, and rejected documents are colored red.
When the status of a document is changed, the conditional formatting rules will automatically change the color of the row to reflect its new status.
By creating an additional Sheet view in our Documents worksheet and using filters and row grouping, we can easily identify documents that require attention. The “Action Items” view groups rows by Column H - Status and filters out those that are approved.
Ready to get started? Browse Spreadsheet.com’s Template Gallery to find ready-to-use document management templates, as well as templates for project management, sales, finance, and more.