Generating & displaying a monthly shift roster

Ditto this :point_up: :pray:

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

Table template:

And CSS template:

3 Likes

Amazing @Darren_Murphy :star_struck:

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

1 Like

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?

1 Like

Unfortunately, yes :weary:
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 :grin:

1 Like

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

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.

9 Likes

Beautiful!

2 Likes

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

2 Likes

Nice!
That looks like it may have potential

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?

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 :white_check_mark: 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.

1 Like

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

And yeah, that is a concern.

1 Like

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!

6 Likes

This Is amazing. Beautiful work Jeff !!!

2 Likes

YES! Glide take note…we need a monthly layout like This!!!

4 Likes

wow, thanks Jeff.
Going to have some fun with this, I think :grin:

2 Likes

I managed to fix that by adding an IFE to deal with an initial empty state… don’t think that broke anything else, still working my way through the logic.

2 Likes

@Jeff_Hager this doesn’t look right?

Haven’t yet figured out what the impact of that is.

Edit: Looks like changing that to use WeekDay adds one day to the result.

Edit 2: hmm… another one that looks a bit dodgy…

Edit 3: Unfortunately my app is in a team folder, so wasn’t able to re-use your Glide table. Had to rebuild it from scratch. Managed to do that, and have it working in my app…

Now to figure out how to link it to my roster table…

3 Likes

So @Jeff_Hager, I’ve been studying this for most of today, fiddling and trying different things - and I keep bumping into the problem that my existing data isn’t very well structured.

Having the days of the month in columns is a real problem. After banging my head on my desk for a couple of hours, I think I’ve hit upon an idea. Would appreciate your thoughts…

As I mentioned in my OP, I do have control over the data. My existing “Roster” sheet is actually imported from another source. I wrote the import code for this, so I can do whatever I like with it. This is my current thinking:

  • One row per worker, per month
  • Each row has the following columns:
    • Worker name
    • Year
    • Month
    • Calendar

The final column (Calendar) will actually be the equivalent of the jl-DIVDays column in your example. It would include all the appropriate div/class definitions for that worker, for every day of that month. I would write code to generate that when I do the roster import.

With this, integrating into your Calendar layout becomes quite simple, I think…

  • Add a USC to the MonthLayout table to hold a worker name from a Choice component
  • Create an identical template column in each table that concatenates the worker name, year and month (eg, Joe Blogs:2021:January)
  • Use those template columns to create a single relation between the two tables
  • Drag the workers calendar through that relation into the MonthLayout table via a Lookup
  • Use that calendar in your tmp-Calendar template column, replacing the existing {ListDays} variable

A couple of potential gotchas that I can think of:

  • I’ll need to take care when generating the individual calendars to ensure I get the first day of the month in the correct position. But I think I can handle that :slight_smile:
  • I’m not sure how responsive it will be as a user switches months in the calendar view - as the relation will be torn down and rebuilt with each tap. I think that should be okay, but it might be a case of suck it and see…

What do you think?

1 Like
  • Nice work on the IFE. I don’t know why I didn’t think of that. During the whole process of setting up the paging, I had discovered that for some reason, I could not make a math column work as the source of the increment. I was trying to add or subtract the calculated number of days to get to the first day of the previous or next month, but for some reason, neither increment, or set column would work for me when the source is a math column. Bugged the hell out of me and is most likely a bug. So instead, I changed the increments to increment by 1 or -1 and had to do some extra math with the “15th” method to get to the previous or next month. Because of that, there is a small bug, due to date drift, after paging through 30 some months where it will get stuck on 1 month for two taps of the next month and will skip a month when going backwards. Mostly unnoticeable, but still annoying. Being able to add months or years would make it much easier. I’ll have to see if I can rethink that one.

  • Looking at mth-WeekDay formula, I think it was honestly due to a lot of trial and error and probably changing the replacements to try different things. Changing the WeekDay replacement to use the weekday column does shift everything 1 day for January, but it also ends up putting the 1st day of the month on the same week day for all months, so the days don’t flow how they should. I changed my copy to use Date as the replacement parameter so it uses sv-FirstDay. I think it was working correctly, but just poor coding on my part.

  • Looking at mth-Day, it’s just extracting the day number so I can use it later in the DIV tags. Again, probably a lot of trial and error trying different things and an old replacement name stuck. I’ve fixed it and renamed DayPosition to Day. I also renamed mth-WeekDay to mth-Dates.

  • So to give a little context about what I was thinking to merge your data into the Month Layout…If you notice, there is the if-Highlight column. All it does right now is check if the date is today. If it is then it returns a value that fills the {highlight} replacement in the tmp-DIVDays columns. My thought is that you could add additional if statements to check the shift for that day and fill it with your shift names, such as (D, N, 1st, 2nd, etc.). That would automatically plug into the DIV tags and as long as you have the CSS set up, it would highlight with the correct color. The CSS has color coding for --current, --day, and --night, but you could change that as needed.

  • Your idea might work. I think the hardest part would be lining up the days, unless you move some the Month Layout logic over to your other tables to figure out the number of blank DIV tags at the beginning of the month. Otherwise, you could maybe do some other magic to get a joined list of only the prior month’s days that have a blank if-ThisMonthDay. The add that joined list to your {ListDays} joined list. Would you happen to have a screenshot of some sample data so I can get a better visual of you monthly tables? Then I can think on it for a while. I’m was hoping we could do some sort of simple plug and play into the Month Layout table with relations and lookups to fill the highlight column, but that might be difficult if everything is laid out in columns, when would mean that your way may be best.

2 Likes

yah, I eventually figured that one out. Have made a similar adjustment in my copy.

Got it :+1: Have made similar adjustments in my copy.

Yeah, I picked up on that. Problem is I have over 30 distinct codes to deal with, and now an additional requirement where my customer has decided that for some codes he wants the actual code displayed in place of the day number. Ugh! :crazy_face:

Yup, I’ve already built out the CSS I’ll need for all codes.

I reckon it will, and I’ve already made a start on the code. As I’ll be generating the {ListDays} array programatically, I’ll just insert the appropriate number of blank divs as I do that. The initial thought of generating this programatically was a bit daunting, but now that I’ve made a start, I can see that it’ll actually be pretty straightforward. For example, I started by defining a couple of mapping tables…

function get_class_names() {
  var class_names = {
    day: 'calendar__number--day',
    night: 'calendar__number--night',
    first: 'calendar__number--first',
    second: 'calendar__number--second',
    third: 'calendar__number--third',
    twoa: 'calendar__number--twoa',
    twob: 'calendar__number--twob',
    ns: 'calendar__number--ns',
    off: 'calendar__number--off',
    training: 'calendar__number--training',
    standby: 'calendar__number--standby',
    leave: 'calendar__number--leave',
    missing: 'calendar__number--missing'
  }
  return class_names;
}

function get_class_map() {
  var class_map = {
    '1ST': { class: 'day', display: 'day' },
    '2A': { class: 'twoa', display: 'day' },
    '2ND': { class: 'second', display: 'day' },
    'ATI D': { class: 'day', display: 'code' },
    'D': { class: 'day', display: 'day' },
    'ITE': { class: 'day', display: 'code' },
    'OJT': { class: 'training', display: 'code' },
    'OJT D': { class: 'training', display: 'code' },
    'SRT': { class: 'training', display: 'code' },
    '2B': { class: 'twob', display: 'day' },
    '3RD': { class: 'third', display: 'day' },
    'ATI N': { class: 'night', display: 'code' },
    'N': { class: 'night', display: 'day' },
    'OJT N': { class: 'night', display: 'code' },
    'ABS': { class: 'missing', display: 'code' },
    'AL': { class: 'leave', display: 'code' },
    'CO': { class: 'training', display: 'code' },
    'MC': { class: 'leave', display: 'code' },
    'MIA': { class: 'missing', display: 'code' },
    'NS': { class: 'ns', display: 'day' },
    'OFF': { class: 'off', display: 'day' },
    'RS': { class: 'missing', display: 'code' },
    'SB': { class: 'standby', display: 'day' },
    'SUS': { class: 'missing', display: 'code' },
    'UL': { class: 'leave', display: 'code' },
    'DAFM': { class: 'day', display: 'code' },
    'NAFM': { class: 'night', display: 'code' },
    'DPM': { class: 'day', display: 'code' },
    'NPM': { class: 'night', display: 'code' },
    'DFM': { class: 'day', display: 'code' },
    'NFM': { class: 'night', display: 'code' },
  }
  return class_map;
}

With those, all I need to do is iterate through the roster, and create an array of arrays, plugging in the appropriate values as I go :grin:

Here is a snapshot of what the source data looks like (this lives in a separate Google Worksheet, and is managed on a day to day basis by the customer):

And here is what my current export looks like when it arrives in my app Google Sheet:

But, assuming that my idea works out (I should know in a few hours), then that will change and all those day of month columns will be gone.

I’ll report back a bit later tonight :slightly_smiling_face:

4 Likes