Best practices/overbooking for Smartsheet calendar app?

Justin Ramos
Justin Ramos ✭
edited 06/14/22 inAdd Ons and Integrations

My company (Bath remodeling company) has our install calendar currently on excel and we do about 22 jobs a day. We are planning to migrate our calendar to Smartsheet calendar app and we are having issues finding a way to provide block outs for some Installers when they request off or how to prevent overbooking. I created a report to show availability based off the number of entries we have but when we go negative there isn't anything preventing someone from filling out a form fill and adding to an already overbooked day. The best I could do would be to show the form and the report on the same dashboard and when inputting in a new job, you would have to confirm that the day isn't negative (because of overbooking) before inputting another entry on the calendar. If there are any best practices when it comes to Smartsheet calendar app in general please let me know since this is a new feature for us!

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this for the COUNTIFS:

    =COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <=[email protected])


    Now that we have the count, we can wrap that in a COUNTIFS to say that if that number exceeds the maximum, flag the row.

    =IF(COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <=[email protected]) > 20, 1)

    thinkspi.com

    · Share on Facebook Share on Twitter

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Having the report and form on the same dashboard is going to be the best solution I can think of in this particular case.


    You could also add in some extra automations to send out an update request to whoever filled out the form alerting them that they have overbooked and need to adjust their booking.

    thinkspi.com

    · Share on Facebook Share on Twitter
  • Justin Ramos
    Justin Ramos ✭

    @Paul NewcomeThank you for the feedback! How would I set up automations to send an update request to who submitted the most recent form? I have the schedule availability on another sheet that has cross references to the main install calendar sheet based off the entries submitted so would I need to move all of that info onto the one main sheet and hide it in order to have the ability to notify someone that we are overbooked?

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Which sheet captures the form entries?

    thinkspi.com

    · Share on Facebook Share on Twitter
  • Justin Ramos
    Justin Ramos ✭

    The main sheet is the one with the form since the form fills are essentially the install/customer info. I then created a separate sheet with cross references with a countif formula to count the number of jobs we have on a given date. It gets complicated because we have 3 different levels of installers and all 3 levels are counted to give the total number of openings and then I have another column next to those numbers to deduct as new entries are submitted to make sure we don't over book

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could insert a column on the main sheet with a formula containing cross sheet references to flag a row if it creates an entry that causes an overbooking. You would write it so that it incorporates a COUNTIFS to count how many previous entries there are for that particular date then compare that to how many bookings are available in total. Any time the COUNTIFS is greater than the total amount available for that date, flag that row. Then you can set your update request to trigger based on that flag.

    thinkspi.com

    · Share on Facebook Share on Twitter
  • Justin Ramos
    Justin Ramos ✭
    Smartsheet pic.PNG

    @Paul NewcomeI have been trying to make this work and I am still having issues. I've tried =COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row) and that works but gives me the same value for all rows so if someone submitted a row that was overbooked, it would notify everyone who submitted for that date because all the numbers in the hidden column would change. I tried an index match formula to the availability sheet and it's the same issue. I wonder if I used the Distinct formula I could add one row at a time until it hits the max number to be overbooked. I tried =COUNT(DISTINCT(COLLECT([Main Installer Level]:[Main Installer Level], [Main Installer Level]@row, [Install Date]:[Install Date], [Install Date]@row))) but that only give me a 1 back. Basically, I need it return a distinct value back from counting the Install level needed based off the install date with other entries in the same sheet so then if say 20 entries are submitted with the same install date and the same required installer level it should return 1, 2, 3... until it hit 20 and then I could built the automation to update rows based off when it hits the cap and is marked as overbooked. What do you think?

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this for the COUNTIFS:

    =COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <=[email protected])


    Now that we have the count, we can wrap that in a COUNTIFS to say that if that number exceeds the maximum, flag the row.

    =IF(COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <=[email protected]) > 20, 1)

    thinkspi.com

    · Share on Facebook Share on Twitter
  • Justin Ramos
    Justin Ramos ✭

    @Paul NewcomeI used your formula and it seems to work! Thank you for your help!

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help.️

    thinkspi.com

    · Share on Facebook Share on Twitter
  • Justin Ramos
    Justin Ramos ✭


    Capture.PNG

    Hey@Paul Newcome,

    So far your formula has been working great however, I am experiencing a weird issue. I have a job marked for a level 3 Installer but the level 1 check box is being checked. For background, the job below it is an overbooked level 1 job but it's weird that it would check it even though "Main Installer Level" column is a 3. Below is my formula I am using, I needed to add a + COUNTIFS to the original because I have a column for if there is a 2nd installer assigned to a job. Any advice? Thanks!


    =IF(COUNTIFS([Main Installer Level]:[Main Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <=[email protected]) + COUNTIFS([2nd Installer Level]:[2nd Installer Level], "1", [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <=[email protected]) > [Level 1 Availability]#, 1)

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It is because your formula is specifying Level 1. Try using a cell reference instead.

    =IF(COUNTIFS([Main Installer Level]:[Main Installer Level],[Main Installer Level]@row, [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <=[email protected]) + COUNTIFS([2nd Installer Level]:[2nd Installer Level],[2nd Installer Level]@row, [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <=[email protected]) > [Level 1 Availability]#, 1)


    You are also going to have to adjust the [Level 1 Availability]# portion to look at the level 3 availability.

    thinkspi.com

    · Share on Facebook Share on Twitter
  • Justin Ramos
    Justin Ramos ✭
    Capture2.PNG

    @Paul Newcomethe [Level 1 Availability]# comes from the sheet summary which is linked from a master sheet that we assign how many we can schedule per day. Saturday's are the only days that are different so I made it separate. How do you recommend I adjust it to look at the other levels? The other check boxes are the same formula but reference "2" or "3" for the levels and the availability that matches it.

    下面是我们使用的“可用性”表make sure we aren't over booked. These are manually entered by our operations department based off how many guys we have available by level. The Blue row is what is being linked, I couldn't figure out a way to match the date to what is entered for the original formula. (right now it's just one set number even if you look at 9/25, we only have 9 available level 1s)

    Capture3.PNG


    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 08/27/21

    You would change the bold portion below, but it looks like you have separate columns for each level and that you have already taken care of that.


    =IF(COUNTIFS([Main Installer Level]:[Main Installer Level], [Main Installer Level]@row, [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <=[email protected]) + COUNTIFS([2nd Installer Level]:[2nd Installer Level], [2nd Installer Level]@row, [Install Date]:[Install Date], [Install Date]@row, Created:Created, @cell <=[email protected]) >(一级可用性)#, 1)

    thinkspi.com

    · Share on Facebook Share on Twitter

Categories

You would need to use a Move Row or Copy Row automation.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":474,"urlcode":"data-shuttle","name":"Data Shuttle"}]},{"discussionID":106742,"type":"question","name":"Conditional Formatting","excerpt":"Hi, I need to apply conditional formatting based on another row in the sheet. Higher it should be highlighted in red, equal to or below highlighted in green. It would be easy to just use the target figures in the conditional formatting rules but I need to be able to change it frequently without changing all the formatting…","categoryID":321,"dateInserted":"2023-06-21T22:41:30+00:00","dateUpdated":null,"dateLastComment":"2023-06-22T15:15:27+00:00","insertUserID":160222,"insertUser":{"userID":160222,"name":"WillH","url":"https:\/\/community.smartsheet.com\/profile\/WillH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T15:13:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":160222,"lastUser":{"userID":160222,"name":"WillH","url":"https:\/\/community.smartsheet.com\/profile\/WillH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T15:13:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":31,"score":null,"hot":3374836617,"url":"https:\/\/community.smartsheet.com\/discussion\/106742\/conditional-formatting","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106742\/conditional-formatting","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":106742,"commentID":381752,"name":"Re: Conditional Formatting","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381752#Comment_381752","dateInserted":"2023-06-22T15:15:27+00:00","insertUserID":160222,"insertUser":{"userID":160222,"name":"WillH","url":"https:\/\/community.smartsheet.com\/profile\/WillH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T15:13:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/ARIIND3W8G3C\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T15:15:35+00:00","dateAnswered":"2023-06-22T03:52:17+00:00","acceptedAnswers":[{"commentID":381675,"body":"

@isabonita<\/a> is on the right track. This is kind of an involved answer, but once you figure out the logic, it isn't that bad.<\/p>

You cannot reference specific cells in the conditional format in a manner that would make this easy. You cannot, for instance, say IF this specific cell is greater than that cell, apply this format. You can do it for the same row, but not always referencing the same cell. We get around this by inserting a \"helper\" column for each condition we want to check. The checkbox format works great. In the conditional format rule, you can simply say IF THIS cell is checked, apply the format to THAT cell. <\/p>

To set the checkbox to checked\/unchecked, we use this column formula: =IF(PDP@row > PDP#, 1, 0)<\/p>

But what is PDP#? This is another issue we have to work around. You cannot reference the first cell of a column in a column formula, you can only reference cells in the same row. To work around this, we add fields into the sheet summary. [PDP]# references the PDP field in the sheet summary. In order to make updating these values easier, we set the value of this field equal to the first cell in the PDP column using =PDP1<\/p>

After creating all of the helper columns, setting up the column formulas for each to determine which ones are checked, creating all of the necessary fields in the sheet summary, and setting those equal to the first cells in each of the respective columns within the sheet, we can set up the conditional formatting.<\/p>

Check out the image below to get an idea of how to setup the formats. Unfortunately, we have one more workaround to setup at this stage. Since the formatting will apply to every row in the sheet, it will apply to the first row as well and all of those cells will be green. To get around this, add the first format pictured below as the first one in your list. For the background color, you must select \"white\" in order to avoid the highlight. If you set it to \"no background\", you will get an error because Smartsheet does not see \"no background\" as a format.<\/p>

After everything is setup and running, you should be able to update the cells in your target row and things will work themselves out automatically.<\/p>


<\/p>


<\/p>

\n
\n \n \"Condition.PNG\"<\/img><\/a>\n <\/div>\n<\/div>\n

I hope this helps, and I really hope it makes sense.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}]},{"discussionID":106711,"type":"question","name":"Is there a glitch in the alert automation when trying to add the double curly brackets?","excerpt":"When creating an alert based on a row being added or changed, I can set the alert to go out hourly, daily, weekly. However, when I add the fields with the double curly brackets, it will not allow me to use the hourly, daily, weekly feature. Is this a glitch in Smartsheet? Has anyone else noticed this?","categoryID":321,"dateInserted":"2023-06-21T17:08:36+00:00","dateUpdated":null,"dateLastComment":"2023-06-22T16:21:48+00:00","insertUserID":158955,"insertUser":{"userID":158955,"name":"Andrea_Thompson","url":"https:\/\/community.smartsheet.com\/profile\/Andrea_Thompson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AmEthyIFMno!3R8u_8p4Dvo!yYe8n_1IW84","dateLastActive":"2023-06-22T16:22:37+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-22T19:34:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":48,"score":null,"hot":3374821224,"url":"https:\/\/community.smartsheet.com\/discussion\/106711\/is-there-a-glitch-in-the-alert-automation-when-trying-to-add-the-double-curly-brackets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106711\/is-there-a-glitch-in-the-alert-automation-when-trying-to-add-the-double-curly-brackets","format":"Rich","lastPost":{"discussionID":106711,"commentID":381771,"name":"Re: Is there a glitch in the alert automation when trying to add the double curly brackets?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381771#Comment_381771","dateInserted":"2023-06-22T16:21:48+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-22T19:34:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T17:28:51+00:00","dateAnswered":"2023-06-21T19:34:19+00:00","acceptedAnswers":[{"commentID":381597,"body":"

Sorry. I misspoke. You are correct that it is not a time based trigger, but it is a time based sending of the alert. If you have 10 rows that changed within that 1 hour period, then it will send all 10 of those rows within the same alert. Since it is pulling 10 rows, it does not know which row to pull the {{Placeholder}} from.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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=341&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":5398,"limit":3},"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Using Smartsheet

  • Data Shuttle Update Rows Workflow: Preventing Duplicate Updates

    Hi all, I have a data shuttle update workflow that runs on a schedule updating rows in the target Smartsheet as they change in an excel source file stored in Box. I wanted to see if there was a way to somehow configure the data shuttle workflow so once a row has been updated, it can't be changed again. I tried doing this…

  • Conditional Formatting

    Hi, I need to apply conditional formatting based on another row in the sheet. Higher it should be highlighted in red, equal to or below highlighted in green. It would be easy to just use the target figures in the conditional formatting rules but I need to be able to change it frequently without changing all the formatting…

  • Is there a glitch in the alert automation when trying to add the double curly brackets?

    When creating an alert based on a row being added or changed, I can set the alert to go out hourly, daily, weekly. However, when I add the fields with the double curly brackets, it will not allow me to use the hourly, daily, weekly feature. Is this a glitch in Smartsheet? Has anyone else noticed this?