Friday, February 20, 2009

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.


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>
* 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>
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.


No comments: