Looking for a project correspondence solution

Andrea Zenner

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.

1. 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?
2. 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!

Genevieve P.

Hi @Andrea Zenner

1) A workflow could work here, yes. If you have your form users select a few criteria for the type of person it should go to, then you can either use an Assign People workflow to add that contact into a Contact Column not on the form (then use this as the contact to send out to in another workflow), or you can add in Condition Blocks in your workflow and create paths for where this new row gets sent, based on what they've selected.

However if you have the set list of emails anyway, you'd just need to add these emails to the Contact Column Value field (in Column Properties) for the choices to appear in the form. (See: Column Type Reference)

2) This is a tricky one. I would say that the built in Conversations/Comments column is the best way to capture correspondence, but it would require some sort of access to the sheet.

There is a new Commenter permission which would keep the data in the row as locked and view-only, but allow users shared to the sheet to create comments on a row. Would that be acceptable for this company?

You can also send Update Requests to users who aren't shared to the sheet, which would then include the comments on that row within the Update Request form.

It sounds like this may be a bit of a bigger project where a screen share would be helpful to go through the current set-up and desired outcome. I would recommend reaching out to your company's Customer Success owner if you have one, or book a Pro Desk session to go through a coaching call on Screen share.

Cheers,

Genevieve

Andrea Zenner

@Genevieve P. Thanks much for the feedback!

I have found a way, I believe, so that people only have to use forms, not the sheet itself.

I did built in the Request for Update feature so I can have responses on the same row as the request, including the comments field.

Instead of having people put a name in the form to whom the request should be sent, I'm having them pick the project associated with the request and I use a lookup sheet for contact information. So if they fill out a customer request form, the workflow looks for the appropriate customer for the project selected. If they fill out an estimator request form, the workflow looks for the appropriate estimator for the project selected. That seems to work ok. If the business comes back and says there is more than one estimator for the same project I'll have to rethink that, but for now it works.

I have already booked a pro desk session but couldn't get one that was within my working hours (didn't want to get up at 4:30AM... I'm dedicated but not THAT dedicated!). I like to have as much built as I can before I meet with them so I know what questions I need answered or what development roadblocks I'm encountering. I'll definitely need help with the metrics stuff. :-)

Thanks again!

Andrea

Genevieve P.

Hi Andrea,

Thanks for following up! Your workflow sounds great, and I completely agree that it's best to have as much as possible set up before a Pro Desk so you can show what's going on.

Happy holidays!

Genevieve