Generating & displaying a monthly shift roster

[Darren_Murphy]

Seeking a little bit of inspiration here…

I need to create something the same (or similar) to the following in Glide:

• The above represents a shift roster for a single worker for an entire month.
• The colour coding represents different types of shifts (D = Day Shift, N = Night Shift, etc)
• I need to do this for about 300 workers, so I’m probably going to present all workers as a searchable/filterable inline list, and then use a View Details action to present the roster for each worker
• My data is currently structured like so:
  • There is one sheet for each month (Jan, Feb, Mar, etc)
  • Each sheet has a single row for each worker
  • There are 2 or 3 columns of identifying information, followed by one column for each day of the month
  • Each of the day of the month columns contains a “Shift Code” identifying that workers shift for that day
• I do have control over the data, so restructuring it to make it easier to work with is an option

My initial thought was that I could do it as an HTML table, creating a separate template for each month of the year, and then use CSS classes to generate the colour coded circles based on the shift codes. And I’ve actually already started down this path. Observe, an HTML table calendar with a wonky blue circle

However, I can see that this is going to get really messy very quickly. And so before I head too far down the rabbit hole, I thought it might be wise to ask:

• Has anyone tackled anything like this before, and have any advice to give?
• Does anyone have any bright ideas for a good (better) way to approach this challenge?

[ehdubya]

This hurt my head just to read haha

I don’t think I’d be any help but just a thought:

Would Cloudinary be able to help with the colour-coordination / conditional visibility of dates/times?

*I’m no Cloudinary wizard in the slightest, but I assume from seeing some of @Robert_Petitto’s vids that it’s as easy as defining which params(?) where and when and then just formatting and calling the link to display the data in-app…

[Darren_Murphy]

Thanks.
Yeah, I do have Cloudinary in the back of my mind, although I’m not sure it would be any less complex than my HTML/CSS option. Thinking if I took the Cloudinary route, I’d probably need a template image for each month, and then find some way to calculate where to put the image overlays for each day of the month… could be quite a challenge…

[Jacktools_Net]

I would create 31 Images for each type. With the day of month as text in the image.

Than i would create a sheet to manage the images.

At least I would use a simple tiles view filtert by user and date greater equal now and group by month.

[ThinhDinh]

This is indeed quite a challenge. You would need at most 31 overlays of the circles, and that’s not taking into account you have to get the Calendar weekdays right for each month as well.

[Robert_Petitto]

I know the alignment is a bit off, but I’m impressed with the HTML calendar table.

This is one of those situations where we’re trying to fit a square calendar peg into a round Glideapp hole. It may be easier to sync your sheet with a third party calendaring service that offers this type of layout and then display it in webview.

[Darren_Murphy]

Progress!!

Actually, I think this is going to work. I can use the shift codes as the class names.
It’s going to be a monster template, but I think it will work…

[Robert_Petitto]

Would LOVE to see this when you’re done (or even halfway done).

[ehdubya]

Ditto this

[Darren_Murphy]

Needs some tidy up, but that’s the back of it broken

Table template:

And CSS template:

[Lucas_Pires]

Amazing @Darren_Murphy

It would be great if we had some Calendar component, or something like that.

Something similar to date picker/calendar screen style but in flat format and not as pop-up

[Robert_Petitto]

Wow. If the table is hard coded, though, does that mean you have to do that every month every year so you know where the dates fall per weekday?

[Darren_Murphy]

Unfortunately, yes
I’ll probably write some code to auto-generate the HTML, but I’ll still need to manually map all the replacements for each month.
Unless… @Jeff_Hager is pretty handy with dates, maybe he’ll have a better idea

[Jeff_Hager]

hehe, you got me playing with this too. I found some stuff here

39 CSS Calendars

and I’m playing with this particular one:
https://codepen.io/corvus-007/pen/MzEKyJ

What I like is that it doesn’t use tables, so it seems like it might be a little cleaner. There’s still the question of how to dynamically build it for each month, so I still need to figure that out. Right now I’m playing with the CSS. Changed a couple of things and had to fix some CSS that wasn’t rendering with glide.

Got this so far.

This is my slightly altered version of the CSS so far.

<pre>
<span>
<style>
body {
  position: relative;
  display: flex;
  justify-content: center;
  align-items: center;
  height: 100vh;
  font-family: 'Poppins', sans-serif;
  background: url("https://images.unsplash.com/photo-1516912481808-3406841bd33c?ixlib=rb-0.3.5&q=85&fm=jpg&crop=entropy&cs=srgb&ixid=eyJhcHBfaWQiOjE0NTg5fQ&s=183f2924ba5a8429441804609b2d4f61") no-repeat center / cover;
  
  &::before {
    content: "";
    position: absolute;
    top: 0;
    left: 0;
    bottom: 0;
    right: 0;
    background: inherit;
    filter: blur(10px);
  }
}

.calendar {
  position: relative;
  width: 100%;
  background-color: #fff;
  box-sizing: border-box;
  box-shadow: 0 5px 50px rgba(#000, 0.5);
  border-radius: 8px;
  overflow: hidden;
}

.calendar__picture {
  position: relative;
  height: 100px;
  padding: 20px;
  color: #fff;
  background: #262626 url("https://images.unsplash.com/photo-1516912481808-3406841bd33c?ixlib=rb-0.3.5&q=85&fm=jpg&crop=entropy&cs=srgb&ixid=eyJhcHBfaWQiOjE0NTg5fQ&s=183f2924ba5a8429441804609b2d4f61") no-repeat center / cover;
  text-shadow: 0 2px 2px rgba(#000, 0.2);
  box-sizing: border-box;
  
  &::before {
    content: "";
    left: 0;
    right: 0;
    top: 0;
    bottom: 0;
    position: absolute;
    background: linear-gradient(to top, rgba(#000, 0.25), rgba(#000, 0.1));
  } 

  h2 {
    margin: 0;
  }
  
  h3 {
    margin: 0;
    font-weight: 500;
  }
}

.calendar__date {
  padding: 20px;
  display: grid;
  grid-template-columns: repeat(7, minmax(10%, 1fr));
  grid-gap: 10px;
  box-sizing: border-box;
}

.calendar__day {
  display: flex;
  align-items: center;
  justify-content: center;
  height: 25px;
  font-weight: 600;
  color: #262626; 
}
  
.calendar__day:nth-child(1) {
   color: #ff685d !important;
}

.calendar__number {
  display: flex;
  align-items: center;
  justify-content: center;
  height: 25px;
  color: #262626;
}

.calendar__number:nth-child(8),
.calendar__number:nth-child(15),
.calendar__number:nth-child(22),
.calendar__number:nth-child(29),
.calendar__number:nth-child(36) {
    color: #ff685d;
    font-weight: 700;
}
  
.calendar__number--current,
.calendar__number:hover {
    border-radius:20px;
    background-color: #009688;
    color: #fff !important;
    font-weight: 700;
    cursor: pointer;
}

.calendar__number--day {
    border-radius:20px;
    background-color: #aaaaaa;
    color: #fff !important;
    font-weight: 700;
    cursor: pointer;
}

.calendar__number--night {
    border-radius:20px;
    background-color: #000000;
    color: #fff !important;
    font-weight: 700;
    cursor: pointer;
}

</style>
</span>
</pre>

In hindsight, the ‘body’ class in the CSS could probably be done away with.

[Robert_Petitto]

Beautiful!

[ehdubya]

Looks like Glide-alchemy to me! W😃W!

[Darren_Murphy]

Nice!
That looks like it may have potential

@Jeff_Hager

What I like is that it doesn’t use tables, so it seems like it might be a little cleaner. There’s still the question of how to dynamically build it for each month, so I still need to figure that out.

I’ve actually been toying with the idea of restructuring my data, and set it up so that there is one row per worker per day (instead of per month). I’m thinking that could make it a bit easier to work with, but what’s giving me pause is that it’ll be very costly in terms of row count - will increase that by a factor of ~30 to about 10,000 rows per month. Any thoughts on that?

[Wiz.Wazeer]

Awesome but don’t you think HTML is risky business.

I was going to share my latest sheet with you but not finished yet.

Company X sources business for the self-employed B in return for a %. X requires B to sign up and add a whole months availability and then every other month. Getting all the Bs to add their availability was easy enough. Getting X to assign jobs by available date also easy enough.

Next I wanted B to be able to see all allocated jobs and available dates for that month exactly how you
Have been going about it.

Explanation of how I’m going about it.

1. If then for different colours.
2. Created a dynamic calendar using google sheet formula. On the sheet I have 3 columns.
   (A) a single row month
   (B) a single row year
   © 31 rows and a long formula.

When type say 3 in the month cell column C generates March, when 9, September and in that order.

Then I created several relations. The outcome ?

When all the Bs sign into their account, the first thing they see is a tiles layout of 31 (if feb 28) days.
They can for the month in Q see all their available dates and allocated jobs in different colours. To see the next month all I get them to do is increment until it reaches 12, when it reverts back to 1.

I am nearly done Im struggling a little with one aspect of it but I can see myself on the other side of the bridge.

I’d love to share a copy with you but still in staging lol. But let me know.

[Darren_Murphy]

Sounds intriguing.
Yes, I’d love to get a sneak preview when you’re ready to provide that

@Wiz.Wazeer

Awesome but don’t you think HTML is risky business.

And yeah, that is a concern.

[Jeff_Hager]

Check out what I have so far. All of it is in the ‘Month Layout’ glide table and tab. The other tab and sheets are from another thing I was working on awhile back.

The entire month layout sheet builds the calendar. It only uses 37 rows and allows paging through all months, so it adapts dynamically. It’s a little quirky at the moment until you click a button first to initially set the increment value. Then it works fine after that. I have it set to highlight the current day and there is CSS for day and night colors that I’m not using, but it could be expanded to have options for all of your shift colors. I’m thinking with the right relations/lookups added, you could probably lookup an employee/date in another sheet to see what the shift is and set the current color for each day.

Enjoy!

Calendar Concept