Smartsheet to allow for multiple updates to a line

mragans23

Hi there,

I am looking for a way to be able to have our employees update a single line each time they reach out to a person with the date they reached out and any notes. They may reach out multiple times in a months time, just not for sure how many time.

For example:

We have a ton of clients. Hospital X: Reached out on 6/1/23. Reached out again on 6/10/23. Reached out again on 6/15/23. Reached out again on 6/20. Scheduled a meeting with them during the 6/20 reach out call.

I wanted to be able to create a dashboard that shows the latest contact status, but I need a sheet that allows for our employees to keep track of the dates they reached out without having them add new lines each time and repeat entering all of the data about that hospital again.

How do i begin?

Andrée Starå

Hi @mragans23

I hope you're well and safe!

Would they use the Conversation feature to add comments? If yes, you could use the Latest Comment Column.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

mragans23

Yes and No. I like the conversation part, if I am able to bring it in to a report as we need to have a running history on each line. However, my biggest concern is that we need to be able to update the date column, but still keep a history on it. For example, I need to know that we reached out to Hospital X on (all dates that they were reached out to). But our big boss just wants a report on the latest date reached out and status.

mragans23

I need a way for our employees to have a way to update the smartsheet (possibly daily or every few days) with new dates and comments while keeping a running history on that without having to add a new row EVERYtime they want to update the date and status regarding that practice. There will be multiples lines of different practices on this sheet. Right now they are adding a new line each time they reach back out and having to reenter all of the data every time they enter a new line.

Paul Newcome

You could create an "Update Form". Just a generic form with all of the fields that need filled out. Then you can use a formula to generate a custom URL with prefilled data based on the row data. From there, users can click the URL link in the column to open a form that already has some of the information pre-filled and allow them to fill in the rest. This will create a new row in the sheet so that you still have those historical entries but keeps them from having to enter the same data over and over again.

Basically you would take the base form URL and then use a formula to append custom data points to the end of it.

="base form URL" + "?" + SUBSTITUTE("Field Name 1=" = [Field 1]@row, " ", "%20")

There are a number of threads here in the Community outlining various tips and tricks on how to get the URL generated, but here is the "official" help article from Smartsheet that could help you get started:

https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

mragans23

I created the form and they are using it and loving it. Now I need to pull the data. So what I need to be able to do is

1.) See how many Practices that Michael Birdsong is reaching out to and how many practices that Tammy Shartzer is reaching out to. (Example: for Full Circle Women's Care that Tammy is reaching out to should only count as 1 for her, however, everytime she adds a new update it creates a new line and adds additional numbers to her. It currently shows her with 13 when it should be 4.

2.) Create a report that shows how many are in Follow Up Status. For example on Full Circle Women's Care, it should pull in 1 line for Full Circle Womens care and look at the status in the most recent created date. So this line should say Full Circle Women's Care Status=Meeting Scheduled

Paul Newcome

@mragans23 There are a number of examples out there in the Community that detail the exact steps to get the metrics and whatnot that you are looking for (trying not to get this thread too far off track).

For the first one you will need

=COUNT(DISTINCT(COLLECT(.......)))

For the second you will need a checkbox column with an IF statement looking a the [Created Date] and comparing it to the MAX/COLLECT of the [Created Date] (collected by [Practice Name] column) that will check the box on the most recent line for that practice which can then drive a report.

If I can find some examples, I will post links to them here, but hopefully you are able to understand the above and get your solution before I can track down the links.