It depends on which method you want to use. Since you're already storing the day of the week in the SQL table, it may be easier to go with option 2, but option 1's offset amount for the first day would also be easy. It also depends on what you do with the data in Forms and Workflow.
For example, if you need to email someone a specific day's value, option 1 would likely be better. However, if you want to email a week's total, there would be fewer date calculations with option 2. Personally, since you're storing the day of the week as well, I'd go with option 1; it has more flexibility and will be easier for you to do.
Let me break down what I did for option 1. If you would rather 2, let me know and I can write that up too. FYI both of them are using tables, not collections.
Option 1 - One Row Per Day
For the table's settings, I have a single column with no header text (variable is Hours) at 50% width, and the row label as {n} so it shows the day number, also at 50% width.
You'll then want to have a lookup rule that finds all of the Workday values from the SQL table (in order, including blanks) and fill the Hours column with them. The day number will populate automatically.
You will also need the a separate field for the 1st of the month's day of the week, aka the offset. If you can, I'd recommend a quick stored procedure that simply grabs the day of the week from the "Day" column in your SQL table and substitutes it for a numerical value, Sunday = 1, Monday = 2, etc.
For the formatting, apply the "month-table" CSS class to the tables
and use the following CSS:
.month-table td.col0 {
padding-right: 4px;
}
.month-table td {
display: inline-block;
width: 50%;
text-align: right;
}
.month-table tr {
float: left;
width: 14%;
}
.month-table .cf-xlarge {
width: 100%;
}
The following JavaScript will apply the first day's offset:
var monthTableIDs = [
[?,?], //Jan
[?,?], //Feb
[?,?], //Mar
[?,?], //Apr
[?,?], //May
[?,?], //Jun
[?,?], //Jul
[?,?], //Aug
[?,?], //Sep
[?,?], //Oct
[?,?], //Nov
[?,?] //Dec
];
$(document).ready(function() {
for (var i of monthTableIDs) {
if (i[0] !== null && i[1] !== null)
changeMonthPadding(i[0], i[1]);
}
});
$(document).on("lookupcomplete", function() {
for (var i of monthTableIDs) {
if (i[0] !== null && i[1] !== null)
changeMonthPadding(i[0], i[1]);
}
});
function changeMonthPadding(sourceID, tableID) { //source and table are numbers --> 4 means q4
var firstday = $("#q" + sourceID + " input").val();
var tr = $("#q" + tableID + " tbody tr:first-child()");
$(tr).css("width", (14*firstday) + "%");
$(tr).find("td").css("width", Math.floor((100/firstday/2)*10)/10 + "%");
$(tr).find("td.col0").css("width", Math.ceil((1-(1/firstday/2))*100*10)/10 + "%");
};
You will need to add the IDs of your tables and offset fields into the monthTableIDs variable. Just replace the first question mark of a row with the table ID (if it's q15, that's the number 15), and the second question mark with the corresponding field with the offset number.
So "[?,?], //Jan" becomes "[15,22], //Jan" if your Jan table is q15 and the Jan offset field is q22.
