Multiple Questions approval process.
Guys, as you all know we are using Smartsheet to protect our facility from individuals coming in to the plant. I have downloaded the COVID-19 form from Smartsheet. However, I created my own, and I am having a hard time figuring this out.
If visitor answer "Yes" to any of the questions, then, access is automatically Denied.
=IF(OR([Traveled outside The US?]5 = "Yes", [Traveled on a Cruise Ship?]5 = "Yes", [Respiratory Illness?]5 = "Yes", [Contact with symptomatic Person?]5 = "Yes"), "Denied", "")
If visitor answers no to all questions, then, it goes for approval process. (automatic workflow).
The thing is, if it goes for approval the response is saved under the Access column "Approved" or "Denied". But, in that column I also have the following formula.
如你所见,my formula is overwrited. Thus, when next submission comes in the formula is not longer available.
How can I make it so both scenarios work?
Best Answer
-
Paul Newcome ✭✭✭✭✭✭Create a separate column and put your IF statement in there with a little bit of a tweak...
=IF(OR([Traveled outside The US?]5 = "Yes", [Traveled on a Cruise Ship?]5 = "Yes", [Respiratory Illness?]5 = "Yes", [Contact with symptomatic Person?]5 = "Yes"), "Denied", Access5)
Then have your Approval Requests continue to populate the Access column, and this new column will house the final overall result based on either being an automatic "Denied" due to having a "Yes" in all columns or the response from the Approval Request.
thinkspi.com
Answers
-
Paul Newcome ✭✭✭✭✭✭
Create a separate column and put your IF statement in there with a little bit of a tweak...
=IF(OR([Traveled outside The US?]5 = "Yes", [Traveled on a Cruise Ship?]5 = "Yes", [Respiratory Illness?]5 = "Yes", [Contact with symptomatic Person?]5 = "Yes"), "Denied", Access5)
Then have your Approval Requests continue to populate the Access column, and this new column will house the final overall result based on either being an automatic "Denied" due to having a "Yes" in all columns or the response from the Approval Request.
thinkspi.com
-
Paul,
Thank you for your response. I have another question. The function/formula works good if I only have a single "Yes" but, it goes for approval and status remains Submitted if I have 2 "Yes" or more. Again, I don't want approval to be sent if visitor answered yes to any of the questions.
If you see, I followed your advice and I created an additional column with the formula. But, as you can see in row1. I get denied in the comment formula. But, submitted in the Access column.
Thank you!
-
Paul Newcome ✭✭✭✭✭✭
You would need to set up your approval request to be triggered off of your Comments column not containing the word "Denied".
thinkspi.com
-
Yeah, I just figured it out. Thank you so much for your help!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
Hi @Stephanie D<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =COUNTIF(Status:Status, \"Green\")<\/p> =COUNTIF([% Complete]:[% Complete], \"Half\")<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106897,"type":"question","name":"Delinquency Report","excerpt":"Hi SmartSheet Community, How do you pull a delinquency report showing month to month stagnation, in the same column across sheets? I'd like to send a monthly update request form for the \"assign to\" with their lists and pull only those item's whose %complete hasn't been updated in the last 30days. Is there a column specific…","categoryID":322,"dateInserted":"2023-06-26T15:35:15+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T16:23:29+00:00","insertUserID":162550,"insertUser":{"userID":162550,"name":"Avani","title":"Senior Manager, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Avani","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T18:44:26+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T16:21:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":20,"score":null,"hot":3375590924,"url":"https:\/\/community.smartsheet.com\/discussion\/106897\/delinquency-report","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106897\/delinquency-report","format":"Rich","lastPost":{"discussionID":106897,"commentID":382339,"name":"Re: Delinquency Report","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382339#Comment_382339","dateInserted":"2023-06-26T16:23:29+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T16:21:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T16:27:10+00:00","dateAnswered":"2023-06-26T16:23:29+00:00","acceptedAnswers":[{"commentID":382339,"body":" You would need to use a Record A Date automation to grab the date the % Complete column was last updated.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
