JOIN(COLLECT
Hi,
I'm trying to use the join collect formula (example of my formula currently)
=JOIN(COLLECT({Meeting Minutes Topic & AI}, {Meeting Minutes Pursuit #}, [Pursuit #]@row, {Meeting Minutes Updated Date}, Objective:I want the formula to search the meeting minutes sheet (pic 2) to match the pursuit # column and return the Topic column & the Action item column into the highlighted cell below (pic one) only if the pursuit number matches AND the Updated date (on pic 2) is within the last 7 days. Thank you so much for any help on this one!
Help Article Resources
Categories
Hmm. Is the date column setup as a date type column vs text\/number? <\/p>
You can also just try retyping the formula vs copying\/paste which has been known to cause issues in some cases.<\/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":["discussion","question"]},"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":[]},{"discussionID":112449,"type":"question","name":"I need help! I need to calculate a date based on hire type and start date","excerpt":"If Hire Type is New Hire, and OD Start Date is a valid date, Then NEW HIRE Request for Contract Date needs to be 2 weeks (14 days ahead of start date) Can anyone please help me? The formula i have typed, I screenshotted. and I am getting a 'circular' error.","snippet":"If Hire Type is New Hire, and OD Start Date is a valid date, Then NEW HIRE Request for Contract Date needs to be 2 weeks (14 days ahead of start date) Can anyone please help me?…","categoryID":322,"dateInserted":"2023-11-01T17:32:37+00:00","dateUpdated":null,"dateLastComment":"2023-11-02T17:39:39+00:00","insertUserID":169395,"insertUser":{"userID":169395,"name":"Danielle Baker","title":"Provider Enrollment Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Danielle%20Baker","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bDDWmy99SOY!!lNP-l834szZ","dateLastActive":"2023-11-01T18:54:59+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":169308,"lastUser":{"userID":169308,"name":"SmartSheetUser01","url":"https:\/\/community.smartsheet.com\/profile\/SmartSheetUser01","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-11-02T17:39:17+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":47,"score":null,"hot":3397810336,"url":"https:\/\/community.smartsheet.com\/discussion\/112449\/i-need-help-i-need-to-calculate-a-date-based-on-hire-type-and-start-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112449\/i-need-help-i-need-to-calculate-a-date-based-on-hire-type-and-start-date","format":"Rich","lastPost":{"discussionID":112449,"commentID":402859,"name":"Re: I need help! I need to calculate a date based on hire type and start date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402859#Comment_402859","dateInserted":"2023-11-02T17:39:39+00:00","insertUserID":169308,"insertUser":{"userID":169308,"name":"SmartSheetUser01","url":"https:\/\/community.smartsheet.com\/profile\/SmartSheetUser01","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-11-02T17:39:17+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/0AAN864AYQ9M\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/0AAN864AYQ9M\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/0AAN864AYQ9M\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/0AAN864AYQ9M\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/0AAN864AYQ9M\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/0AAN864AYQ9M\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-11-01T22:04:59+00:00","dateAnswered":"2023-11-01T18:35:23+00:00","acceptedAnswers":[{"commentID":402620,"body":"
You're getting a circular error because you're trying to use the value from your \"Hire Type\" column to calculate a value for your \"Hire Type\" column. That formula will always be chasing it's tail.<\/p>
Your second IF criteria states that IF [OD Start Date]@row = \"True\". The problem with this is that [OD Start Date] column is not a True\/False column such as a checkbox or flag. It's a date column, so it will never be True or False.<\/p>
Your final IF criteria states \"[NEW Hire Request for Contract Date]@row = Date\" would also cause an error. First it's surrounded by \"\" which means everything in-between should be treated as a string of text. It's not a criteria statement that will be evaluated. Second there's no \"= Date\" functionality that will tell you the value is a valid date. If you have that column set as a Date column that is restricted to dates only, then any entry will be a valid Date otherwise the data wouldn't have been accepted into the cell.<\/p>
Try this in your [NEW Hire Request for Contract Date] column: =IF(AND(NOT(ISBLANK([OD Start Date]@row)), [Hire Type]@row = \"New Hire\"), [OD Start Date]@row - 14, \"\")<\/p>
The [NEW Hire Request for Contract Date] column must be a Date type column for this to work.<\/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":["discussion","question"]},"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":[]},{"discussionID":112445,"type":"question","name":"How to format specific\/named date in If\/Then formula","excerpt":"I have a column for the record Create Date. I have a column within the grid that needs to evaluate the CREATE DATE compared to a specific date - 10\/27\/2023. I've tried several variations of formatting the date within the IF statement with no success: =IF(Created@row < 10 \/ 27 \/ 23, \"blank\", \"not blank\") .... Smartsheet…","snippet":"I have a column for the record Create Date. I have a column within the grid that needs to evaluate the CREATE DATE compared to a specific date - 10\/27\/2023. I've tried several…","categoryID":322,"dateInserted":"2023-11-01T16:44:21+00:00","dateUpdated":null,"dateLastComment":"2023-11-02T16:11:20+00:00","insertUserID":123201,"insertUser":{"userID":123201,"name":"Brian Becnel","url":"https:\/\/community.smartsheet.com\/profile\/Brian%20Becnel","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!05DRPwyhQ_Y!wvFet7KSNnw!WnKjKtL5skl","dateLastActive":"2023-11-02T14:23:54+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-11-02T16:18:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":54,"score":null,"hot":3397801541,"url":"https:\/\/community.smartsheet.com\/discussion\/112445\/how-to-format-specific-named-date-in-if-then-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112445\/how-to-format-specific-named-date-in-if-then-formula","format":"Rich","lastPost":{"discussionID":112445,"commentID":402823,"name":"Re: How to format specific\/named date in If\/Then formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402823#Comment_402823","dateInserted":"2023-11-02T16:11:20+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-11-02T16:18:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-11-02T14:24:06+00:00","dateAnswered":"2023-11-02T13:02:16+00:00","acceptedAnswers":[{"commentID":402761,"body":"
You would put the DATE function inside of the IF function.<\/p>
=IF(Created@row< DATE(2023, 10, 27), .....<\/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":["discussion","question"]},"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":[]}">
