Workflow Automation - Assign People Function.

JPFORDTE · 04/21/22

I have created a Shift Rota for my team. The left axis displays the day and date, the top axis displays each person broken down by team. In the table each person on each day gets assigned a letter depending on the shift they are assigned to (e.g D = Day Shift, A = Afternoon Shift).

I have then set up an automation to collect each person for each shift type per day.

The automations are as follows.

Depending on what letter they have assigned that day, this feeds into another table where it collects the different people on each shift.

The automation works when you reassign someone's shift. E.g you assign me from D (day shift) to N (Night shift) on the shift rota, the automation then puts my contact info into Night Shift Column on table on the right. However, my name still stays in the Day shift column.

Is there a way to remove a name from the previous cell it was in?

I understand using a and index(collect( or Join(collect is probably better but I have not found a way to do this given the many different variables it would have to look up.

Many thanks in advance!

Jeff Reisman · 04/26/22

When I create a similar formula, and none of the IF's is true (none of the columns has a "D" in it,) a "0" is placed in the cell. That's unexpected!

But, unlike what I see in yours, the "0" goes away when at least one of the IFs turns to true.

When I add a negative condition of "" (a blank value) to one of the IFs, I no longer get a "0" when none of the conditions are true. So add that at the end of your first IF (as shown below) and see if that works to get rid of the 0.

=IF([email protected]= "D", $Employee$1, "")

Jeff Reisman · 04/21/22

@JPFORDTE

In place of the automation, you can do this using a bunch of IF statements added together in each shift's column. However, you'll need toconvert the Shift columns from Contact-type columns to regular Multi-Select columns, and you'll need tocreate a new regular multi-select column (let's call it "Employee",) listing your employees:

Let's start with the Day Shift column:

=IF([email protected]= "D", Employee1) + IF([email protected]= "D", Employee2) + IF([email protected]= "D", Employee3) + IF([email protected]= "D", Employee4) + IF([email protected]= "D", Employee5) + IF([email protected]= "D", Employee6) + IF([email protected]= "D", Employee7) + IF([email protected]= "D", Employee8) + IF([email protected]= "D", Employee9)

The result of this, in a multi-select column, is each name listed as a distinct value in the multi-select cell.

For your Small Day Shift column, just change the "D"s in the above formula to "SD", and so on for the other shift columns.

This way, when someone's shift changes, the formulas update and the employee's name will move to the correct shift column.

JPFORDTE · 04/25/22

嗨,杰夫,

thank you very much! this works.

one little issue:

Would you happen to know why for some rows it returns a zero? Couldn't figure out why.

Jeff Reisman · 04/25/22

Hi Jeff,<\/p>

thank you very much! this works. <\/p>

one little issue:<\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/89KTL3S08TS0\/image.png<\/a><\/p>

Would you happen to know why for some rows it returns a zero? Couldn't figure out why.<\/p>","bodyRaw":"[{\"insert\":\"Hi Jeff,\\nthank you very much! this works. \\none little issue:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/89KTL3S08TS0\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/89KTL3S08TS0\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"Would you happen to know why for some rows it returns a zero? Couldn't figure out why.\\n\\n\\n\"}]","format":"rich","dateInserted":"2022-04-25T09:36:09+00:00","insertUser":{"userID":140218,"name":"JPFORDTE","url":"https:\/\/community.smartsheet.com\/profile\/JPFORDTE","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7Zlpu6ODVaM!XIEqH7eWCvs!Ehtq2n17psd","dateLastActive":"2022-04-28T08:05:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/327201#Comment_327201","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/327201#Comment_327201

Without seeing your entire formula and the lookup list of employee names, I couldn't say.

JPFORDTE · 04/26/22

Employee List:

Formula (this one looks up the correct people but there is also a zero that is returned in this cell):

=IF([email protected]= "D", $Employee$1) + IF([email protected]= "D", $Employee$2) + IF([email protected]= "D", $Employee$3) + IF([email protected]= "D", $Employee$4) + IF([email protected]= "D", $Employee$5) + IF([email protected]= "D", $Employee$6) + IF([email protected]= "D", $Employee$7) + IF([email protected]= "D", $Employee$8) + IF([email protected]= "D", $Employee$9) + IF([email protected]= "D", $Employee$10) + IF([email protected]= "D", $Employee$11) + IF([email protected]= "D", $Employee$12) + IF([email protected]= "D", $Employee$13) + IF([email protected]= "D", $Employee$14) + IF([email protected]= "D", $Employee$15) + IF([email protected]= "D", $Employee$16) + IF([email protected]= "D", $Employee$17) + IF([email protected]= "D", $Employee$18)

Jeff Reisman · 04/26/22

When I create a similar formula, and none of the IF's is true (none of the columns has a "D" in it,) a "0" is placed in the cell. That's unexpected!

But, unlike what I see in yours, the "0" goes away when at least one of the IFs turns to true.

When I add a negative condition of "" (a blank value) to one of the IFs, I no longer get a "0" when none of the conditions are true. So add that at the end of your first IF (as shown below) and see if that works to get rid of the 0.

=IF([email protected]= "D", $Employee$1, "")

JPFORDTE · 04/27/22

When I create a similar formula, and none of the IF's is true (none of the columns has a "D" in it,) a "0" is placed in the cell. That's unexpected!<\/p>

But, unlike what I see in yours, the "0" goes away when at least one of the IFs turns to true.<\/p>

When I add a negative condition of "" (a blank value) to one of the IFs, I no longer get a "0" when none of the conditions are true. So add that at the end of your first IF (as shown below) and see if that works to get rid of the 0.<\/p>

=IF(Joakim@row = "D", $Employee$1, ""<\/strong>)<\/p>","bodyRaw":"[{\"insert\":\"When I create a similar formula, and none of the IF's is true (none of the columns has a \\\"D\\\" in it,) a \\\"0\\\" is placed in the cell. That's unexpected!\\nBut, unlike what I see in yours, the \\\"0\\\" goes away when at least one of the IFs turns to true.\\nWhen I add a negative condition of \\\"\\\" (a blank value) to one of the IFs, I no longer get a \\\"0\\\" when none of the conditions are true. So add that at the end of your first IF (as shown below) and see if that works to get rid of the 0.\\n=IF(Joakim@row = \\\"D\\\", $Employee$1\"},{\"attributes\":{\"bold\":true},\"insert\":\", \\\"\\\"//www.santa-greenland.com/community/discussion/comment/\"},{\"insert\":\")\\n\"}]","format":"rich","dateInserted":"2022-04-26T21:02:23+00:00","insertUser":{"userID":86866,"name":"Jeff Reisman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Reisman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3V8W2AM105QE\/nIDXI4I7VDS7W.JPG","dateLastActive":"2023-06-23T18:31:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/327461#Comment_327461","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/327461#Comment_327461

Legend! Thank you Jeff, this worked!

Jeff Reisman · 04/27/22

https:\/\/community.smartsheet.com\/discussion\/comment\/327461#Comment_327461<\/a><\/p>

Legend! Thank you Jeff, this worked!<\/p>","bodyRaw":"[{\"insert\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/comment\\\/327461#Comment_327461\",\"attributes\":{\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/comment\\\/327461#Comment_327461\"}},{\"insert\":\"\\n\"},{\"insert\":\"Legend! Thank you Jeff, this worked!\\n\"}]","format":"rich","dateInserted":"2022-04-27T08:18:07+00:00","insertUser":{"userID":140218,"name":"JPFORDTE","url":"https:\/\/community.smartsheet.com\/profile\/JPFORDTE","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7Zlpu6ODVaM!XIEqH7eWCvs!Ehtq2n17psd","dateLastActive":"2022-04-28T08:05:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/327494#Comment_327494","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/327494#Comment_327494

太棒了!

Now that I'm thinking about it - here's why the 0 gets placed in the cell, and why adding the "" fixes it:

Because we didn't give the IFs a negative condition (a value for if the logical expression is false,) and we are ADDING formulas together, Smartsheet assumes the negative condition is the default negative numeric value, which is 0. (For example, in a checkbox field, 1 is positive - checked, and 0 is negative, unchecked.) If you add a string of IFs that have conditions that are numbers (not in quotes,) Smartsheet will mathematically add the values together. By adding the "" (blank text) negative condition, that means instead of treating the formula like it's 0 + 0 + 0 etc, it treats it as blank text + 0 + 0, effectively turning the cell into a text cell, and therefore not "adding" all the zeroes together.

Workflow Automation - Assign People Function.

Best Answer

Answers

Categories