Date Loops for a ROTA

As part of the ROTA application I had to build a very simple way for a user to say, "Starting on this date, assign something weekly for X weeks." This meant I had to get the start date, the quantity of weeks, write the dates and then loop them in as rows in a table. Later I'd use the row IDs for the outside of a nested loop to assign people to these date-events.

Not much time to explain laboriously here but am posting my code just in case it ever helps someone searching.

I get from a form the qty of weeks and the start date.

<cfset rotaweeks = "#form.rotaweeks#">
<cfset rotadt = "#dateformat(form.rotadt,'mm/dd/yyyy')#">

I actually go back a week so the script can then regularly go forward a week in the loop.

<cfset newday = #DateAdd("d", -7, "#variables.rotadt#")#>
<cfset newdaylist = "">
<cfoutput>
<cfloop from="1" to="#variables.rotaweeks#" step="1" index="i">
<cfset newdayA = #DateAdd("d", 7, "#variables.newday#")#>
<cfset newday = "#DateFormat(variables.newdayA,'mm/dd/yyyy')#">
<cfset newdaylist = "#ListAppend(variables.newdaylist, '#variables.newday#')#">
</cfloop>
</cfoutput>

That leaves me with a list of dates. I set and format some vars for the edges of that date range.

<cfset firstdt = "#ListFirst(variables.newdaylist)#">
<cfset afterA = '#DateAdd("d", -1, "#variables.firstdt#")#'>
<cfset after = '#DateFormat(variables.afterA,"mm/dd/yyyy")#'>
<cfset lastdt = "#ListLast(variables.newdaylist)#">
<cfset beforeA = '#DateAdd("d", 1, "#variables.lastdt#")#'>
<cfset before = '#DateFormat(variables.beforeA,"mm/dd/yyyy")#'>

<cfset listqty = '#ListLen(variables.newday)#'>

I give users the option to remove all previous entries from that date range.

<!--- if checked, remove previous entries for this team between those dates (if they are replacing/modifying the rota) --->
delete from myrotatable WHERE
teamid = #variables.teamid#
and rotaid = #variables.rotaid#
and rotadt > '#variables.after#'
and rotadt < '#variables.before#'

Then the dates get written into rows in a table

<!--- then insert the dates in table --->
<cfloop list="#variables.newdaylist#" index="ii">
insert into myrotatable
(rotadt, rotaid, rotateamid, ...)
values
('#ii#',#variables.rotaid#,#variables.teamid#, ...)
</cfloop>

I can grab the record IDs of those dates and loop through them, sub-looping my list of userIDs, to create the rota. See the post Rota, Dynamic Variables and Nested Links for the rest of it.

ROTA, Dynamic Variables and Nested Loops

Fresh from the land of nobody-can-help-me-I'm-doomed, I discovered (as usual) that I was trying to make something a lot harder than it needed to be. Hate it when I do that. Then the relief of solving it is tempered by knowing I was an idiot and it should have been obvious in the first place. In any case, onward:

I never knew why I might 'need' something like 'dynamic variables' but here is where I did. I've been working on a ROTA. This is a list of user assigned slots/dates. A baseball team lineup is a good example of a rota. A list of three kids and the chores they take turns doing, or a few employees and the tasks they take turns doing, would be a rota.

In my application, there are "teams" (groups) of people who change off weekly setting up a fun assignment for the rest of the team. This means in my case, there are generally far more dates (weekly assignments) than people (team members), so there is the big loop through dates and then the smaller sub-loop through the members, which repeats until all the dates are filled.

Example:

John (userID 93), Jane (userID 156), and Alex (userID 418) are the team;
The dates are weekly, every Monday for 7 weeks.

So when we end this we should have something like:

week 1: john (93)
week 2: jane (156)
week 3: alex (418)
week 4: john (93)
week 5: jane (156)
week 6: alex (418)
week 7: john (93)

This was easy to code except I screwed up the dynamic variables part. Just overcomplicated it.

In general the building process worked like this:

* build rota info including settings, rota name and rota ID, start date and qty of weeks
* get team info including list of user IDs, Aliases and team ID
* show team members for a drag&drop sequence (see Drag & Drop Sortable Lists)
* that list is going to post as a list of the userIDs: form.userid = 93,156,418

Then on the next page the assignments happen.

These team members are being looped in sequentially, and this is going to be controlled with a simple counter variable. So up top we set a list of our user IDs in dynamic variables using the counter. That way, LATER when we are doing the loop through dates, we can use the counter to rebuild what the variable should be named and then evaluate the name to get the user ID.

<!--- set up a numbering list for the user IDs. eg 'theuser2' = 156. --->
<cfset counter = 1>
<cfset qtyusers = "#ListLen(variables.userlist)#">
<cfloop list="#variables.userlist#" index="i">
<cfset "theuser#variables.counter#" = "#i#">
<cfset counter = #variables.counter# + 1>
</cfloop>

* query for the record IDs of the list of dates, now we have a list to big-loop through

To see how I created the weekly dates, see Date Loops for Rota

* our list of user IDs is our small list to sub-loop through

<cfset counter = 1>
<cfloop query="myquery">
<!--- we have fewer users than events. user numbering starts over. --->
<cfif variables.counter GT variables.qtyusers><cfset counter = 1></cfif>
<cfset thisuserA = 'theuser' & '#variables.counter#'>

{another query here gets user info}
then the query that updates the record says:

update table SET
rota_userid = #evaluate(variables.thisuserA)#

<cfset counter = #variables.counter# + 1>
</cfloop>

And that's it. I really can't believe that I've started and stopped on this several times over 3 years -- previously it was insanely complicated. A couple months ago the solution fell into my head and I thought "wow, so simple!" and I wrote the code superquick, and it worked perfectly right up till the end -- when it crashed and burned on the dynamic variable.

It was telling me the dynamic variable I had set was not found. Even though I could display both its name and its value right above the line where it insisted it didn't exist. It turned out to be something funky about the way I'd been coding it that the evaluate function was crashing against. The above code works fine.

PJ