Looking for a project correspondence solution
Good Morning Gurus!
I've been asked to assist in the design and development of a project correspondence solution in Smartsheet. Personally I think SharePoint is a better fit, but they are on a very old version of SharePoint which will be upgraded to SharePoint Online in the next year. They don't want to wait for that, and they have their project plans in Smartsheet.
What is correspondence? - By correspondence they mean requests for information EXTERNAL to the company, requests for quotes INTERNAL to the company, submittals of information internal and external, and approvals for drawings or other documentation.
They do have Salesforce but their IT does not have time or resources to implement that connection. (I work for a sister company that has been on SS longer so asked to help.)
We do have WorkApps which I think would be great for this, but there is no budget to license all the internal and external people that would need to use it.
So I'm stuck with "classic" tools.
They would like all interactions to be via forms, they don't want anyone in the sheets. Not sure why, and I may be able to convince them otherwise using editable reports with locked columns or something.
They also want reporting/dashboards to show per project and per project portfolio metrics (outstanding tasks on projects, outstanding approvals or requests for information from internal and external customers, margin information (per project and overall), resource workloads, etc.
They want dashboards at a Project Manager and Executive level.
My thought is I would set up a Workspace per project portfolio (all projects under a specific type - not sure exactly if that is by PM or by product make up).
Within each workspace would be folders for each project.
Within each folder would be one sheet for the project plan and a second sheet for the "correspondence".
I can then use reporting to link all that together for metrics and dashboards assuming we can figure out a key for each.
I can use workflows for requests for information, sheet updates, reminders, etc.
I'm struggling with a couple of things.
- How to I provide a list of contacts in a form so people don't have to know the email address of the person they need to send the request to? I can import a list from Salesforce, but I can't us a calculated field on a form so doing a lookup from a contact list to the sheet from which the form is created won't work. Maybe I can use workflow if I have the project name and contact type (vendor, estimator, etc) on the form, they select those, then in my workflow I send the communication to the person in my contact list that fits those criteria?
- How do we capture the correspondence in a way that is easy for the recipient to see the whole chain? I can use a "comments" field, but if there is a lot of text those can get ugly in the sheet and you have to be in the sheet to see history. Is there a way to show conversations on a form or report or another way that I could maybe publish a link to somewhere where people could easily see any communications?
Any suggestions would be much appreciated. I did look through the Solution Center and didn't see anything that really fit what they want to do.
Thanks in advance for reading this long one!