Register and calendar integration

sahilhq
sahilhq ✭✭✭✭✭✭

Hi,

I need assistance in creating a calendar which will show the number of locations where material deliveries will happen (5 locations) over 24 hours - 365 days a year. The idea is to then create a dashboard with the form and calendar and share it with the senior members of my team so that they can book delivery slots by filling in the form based on the availabilities they see on the calendar. I have setup a draft dashboard. The form in the dashboard is looking good but the calendar not so good (see attached screenshot). The calendar needs to somehow show all the slots available (12am - 11pm) on a daily basis at all the 5 different locations. Is there a way we could achieve this?

I also have set up a sheet that's driving the calendar (see attached screenshot). I think we need to think of a clever formula in the sheet to achieve this. Please help!!


.

Best Answer

«1

Answers

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Paul Newcome Hi! You have assisted me with a similar kind of query a few months ago. Can you please help again?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    For starters... To display multiple slots on the same day in a calendar view, each slot would need it's own row. So if you have 50 slots open on Dec. 12 and you want to display all 50 of them, you would need to have 50 rows with Dec. 12 in your date column. That doesn't seem very feasible if you are wanting to scale this out with that many options over the course of an entire year. You may want to consider breaking it down by quarter or even month.


    Basically my suggestion would be to build it out for the month of January. Copy that 11 times and update for each month. Then create a "Directory" type of dashboard that contains links to each of the monthly dashboards. Does this sound like an option, or do you need it all on one sheet?

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Paul Newcome Thanks for the response. Ideally speaking it would be nice to have it all under one sheet but if that's not possible then I could try building it for the month of January and then copying it 11 times and update for each month. Do you know any other possible workaround to have it all on one sheet? If not could you suggest with what the formula would be like for the 5 different locations for 24 hours everyday in the month of January? See attached screenshot of calendar sheet of a much simpler dashboard that you helped me creating a few months back. If I do it this way for the new dashboard I believe we would over a thousand rows . Please assist with a "smarter" solution. Thanks!


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If I remember correctly, it would end up being a very similar setup.


    Could you provide a link to the post where we sorted through the other solution so I can refresh my memory?


    This one is obviously going to be more complex, so I want to try to replicate the other solution as much as possible so that we have more familiarity with the build.

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Paul Newcome Many thanks for this. Yes, I was looking to display all slots for all locations for each day on the calendar. In fact when making the booking I would like the person making the booking to be able to see all available slots in each and every hour of the day. So for example if there are 5 slots available from 2-3pm or 10 slots from 2-3am I would want the requested to be able to see that in the calendar. That’s the level of detail I would like to achieve. Do you think it will be possible to show all the 24 hour slots for all the 5 locations in one calendar?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Oy! You are wanting some serious detail! Haha. It is possible, but it will become a rather tedious build, and the calendar will become rather cluttered.


    To have every slot for every location displayed, each slot for each location would need it's own row. 24 Hours at 5 Locations means 120 rows per day. To be able to show that level of detail, you would need to decide on exactly how you wanted to break it down even further than just by row.


    120 rows per day means only 16 full days per sheet...

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Paul Newcome Hi again! I have tried to work on this but I am not really getting anywhere.... So for the time being I have only been asked to create the calendar for the month of Feb to see what it would look like. I gave it a shot for one day in Feb and the results are attached below. I had to insert 120 rows for a day since deliveries are happening over 24 hours and in 5 different locations. Unfortunately, the calendar was only able to show information for two locations (48 rows).

    Do you think there is any other way we could break it down so that the calendar can show the available slots for all 5 locations and not just two? Ideally speaking it would be nice to have a calendar but I don't mind showing the number of slots available at all locations in any different way as long as it could be shown in a dashboard. So something like a card view will also do if we can show the information nicely. Please help! Thanks a lot in advance.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Card view could work.

    Just throwing some ideas out right here...


    Card view would need to be broken down with the Location as the lanes.

    Sheets would be broken down by date.

    Dashboard would have hyperlinks to each sheet that users can access depending on which date range they want.

    All fields would be locked to the users with the exception of a checkbox which they would check to "book" that time slot.

    All sheets would be duplicated with data removed (so we have all columns in the same order).

    Move row automation so that when user checks box and saves, the row for that time slot is removed from that sheet and placed on the dup sheet (archive).

    Metrics pointing at archive sheets show broad view number of available slots for each location on each day.

    These broad view numbers displayed as calendar view on same dashboard.


    Does it sound like something like that might work for you?

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Paul Newcome Honestly, it's all currently sounding a bit complex to do but I think it could work. Where shall I get started from? Can you send me the steps to achieve this please?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Let's start with the main sheet for a 2 week block. Let's just set it up for now for 1 - 14 March 2020.


    Create a row for each time slot for each day for each location (1,680 rows) (Date type column).

    Add in a Single Select Dropdown that lists out the 5 locations. Go ahead and enter the location for each of the rows.

    Add in a checkbox column. Leave these unchecked for now.

    Add in a text/number column that has the time for each row.

    In the Primary column, enter something along the lines of:

    =[Date Column] + ": " + [Time Slot Column]


    Once this is done, you should be able to switch to Card view and set the lanes as the Location Dropdown.

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Paul Newcome All done mate. Please see attached images of how the sheet currently looks like in Grid and Card view. What should I do next? Thank you once again.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Thinking more on it, you may want to add in a field or two for identifying information such as name and email or something to that effect. This card view is going to be used in place of the form, so we need a way to track that as well.


    Keep in mind... This is the main view the users will be seeing. Is that ok for you/your users?


    If so... The next step would be to duplicate this sheet. Because you don't have a whole lot of formulas or columns, you can just create a new sheet and replicate the columns (that may be easier than using "Save as new..." and trying to delete all of those rows). I also suggest adding in a system generated Created Date type column for future tracking purposes. We can call this duplicated sheet "Booked" for now. Then you would set up a Move Row automation on the Delivery sheet that will move a row once the checkbox is checked to the Booked sheet.


    The thought process behind this so far is that users will access the card view and select a card/time slot that they want to book. Once they book it, the row/card will be moved to the other sheet so that it is no longer even available for other users.


    Make sense so far?

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    I am not sure if I would like to replace the form with the card. The form that we have in place has all sorts of questions being asked such as model, registration numbers, vehicle type, etc. The intention to put the calendar/card at the first place was just for the visibility of available slots in the calendar year. So it would be nice to have the form and calendar/card adjacent to each other. One of the questions being asked in the form is to select delivery slot. It would be great if we could somehow link this question to the calendar/card view if you know what I mean?


    Link to the form - https://app.smartsheet.com/b/form/a7d3d1ff27b346339eaa5f2b22d0dd81

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!