Populating Assigned to Column Based on a User's Acceptance

Mark Salamon

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.

Paul Newcome

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

Why not just have them select their name within the contact column and have the "Assigned" populated through formula?

Mark Salamon

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.

Paul Newcome

The person who is marking the row as "Assigned". Have them fill in the "Assigned To" column and automate the other column.

Mark Salamon

@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.

Paul Newcome

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?

Mark Salamon

@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?

Paul Newcome

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.

Mark Salamon

@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.

Paul Newcome

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.

Mark Salamon

@Paul Newcome Yes. that works! Thanks so much.

Paul Newcome

Happy to help. 👍️