Smartsheet Basics
Smartsheet Basics
Ask questions about the core Smartsheet application: Sheets, Forms, Reports, Dashboards, and more.
Populating Assigned to Column Based on a User's Acceptance
I have task-based sheet with 3 relevant columns:
(1) "Available Users" which is a contact list column that can accept multiple contacts. This column lists the user who are available or eligible to take on a task. (The identification of the available users is done manually at the time the record is created.)
(2) "Assigned User", the user to whom the task will be assigned later.
(3) "Task Assigned" - a select column with a single value "Assigned"
When a new record is created, an email automatically is sent to each Available User. That email includes a link to a Dynamic View. In that Dynamic View, each available user can see a list of the tasks where they are listed as an Available User and where Task Assigned is blank.
From the Dynamic View, I want to allow an Available User to accept a task not yet assigned. The Assigned User would simply set the value of Task Assigned to "Assigned" and the record would be updated to list that user as the "Assigned User" in the sheet.
However, I cannot figure out how to change the value of Assigned User to the Available user who accepted. Although that user will be listed as the Modified By at the time they accept the task, I cannot use Modified By instead of Assigned user because other people may update the task later.
I tried automation, to set the value of Assigned User at the time Task Assigned is changed to "Assigned" using workflow, but there is no way to dynamically set the value of a Contact List column to the [Modified By] field (or any other field). You can only hard code in the new value.
This seems like it should be very simple and I am overlooking something obvious.
Best Answer
-
Hmm... Try inserting another contact column and use
=[Modified (by)]@row
When the copy row automation happens, in theory it should strip the formula and capture the static data. Does that work? If so, you can now reference this one in the INDEX/MATCH above.
Answers
-
Why not just have them select their name within the contact column and have the "Assigned" populated through formula?
-
I'm not sure I understand. there are two Contact List columns: Available Users and Assigned To.
When you say "select their name within the contact column" do you mean select their name from the Available Users column? How would that work? There could be 10 users listed there, so the user is supposed to remove the other 9 users listed? With no guarantee that when they submit the form that they have removed the other users or selected themselves?
Then I'm not sure what formula could be used to populate the Assigned To column from the Available Users. If you simply mean =[Available Users]@row, that will contain whatever info is in Available Columns.
An alternative would simply be to expose the Assigned To column in the form and have them select their own name from the contact list, although there is no guarantee that they would correctly select their own name.
Given that the user is already logged in and the system knows who they are when they access the form, it would be easier if they could simlpy change the Accept column value to Accepted and save the form and the form will automatically assign it tothem.
The other approaches are much kludgier from a user experience perspective and will often result in errors.
-
The person who is marking the row as "Assigned". Have them fill in the "Assigned To" column and automate the other column.
-
@Paul Newcome Thanks. That would achieve the result, but as noted, it's less than an ideal experience, forcing the user to select their name from a list when the system already knows who they are and running the risk that the user (likely accidentally) selects the wrong user.
Ideally, the approach will utilize the fact that the system has already identified the user and the user only needs to accept the task.
-
There is a way that would require some additional setup to make it work the way you want it to.
Basically you would create another sheet, set up a Copy Row automation to copy the row when a user enters "Assigned", then use a formula with cross sheet references to look at the second sheet and pull the (now static) email address from the Modified column. We would add one additional column which can be hidden on the working sheet after setting everything up.
Is that something you'd like to explore?
-
@Paul Newcome I think I understand that. I have never done that type of thing before. How do you set up a link between the two sheets so that the Modified By value from new row the second sheet populates the correct row in the original sheet?
-
First we would need to establish a unique ID for each row. That is where the added column comes into play. You would want to insert an auto-number column. You don't need any special formatting or anything.
Then you would use a formula along the lines of...
=IFERROR(INDEX({Other Sheet Modified By Column}, MATCH([Auto-Number Column]@row, {Other Sheet Auto-Number Column}, 0)), "")
Of course this will not be an immediate thing as soon as the person enters "Assigned". They will have to first save the sheet and then wait for the automation to copy the row over. Refreshing your browser after saving should help speed that up.
-
@Paul Newcome I tried this out. Unfortunately, when automation is used to copy the row from the original sheet to the cloned sheet, the Modified By value of the copied row in the cloned sheet lists "automation@smartsheet.com", not the smartsheet user who last modified the copied row in the original sheet. Therefore, I cannot get the data that way, unless I am missing something.
-
Hmm... Try inserting another contact column and use
=[Modified (by)]@row
When the copy row automation happens, in theory it should strip the formula and capture the static data. Does that work? If so, you can now reference this one in the INDEX/MATCH above.
-
@Paul Newcome Yes. that works! Thanks so much.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Trending in Smartsheet Basics
- Hello Team, May be this is intentional, but want to double check. In a sheet, when i open the conversations window, I am able to see tabs for "Row", "Sheet" "All" and "Unread". On the contrary, in a r…
- Hi everyone, I have experience using Smartsheet Core Product and I'm Core Product Certified , but I am new to Smartsheet Control Center (SCC). I am currently preparing for the Smartsheet Aligned Solut…
- Can I and how do I set conditional formatting for a Finish Date font to be red if the date is in the past and % Complete is not 100. Is there a way to set multiple conditions for Conditional formattin…
