Can someone please tell me what is wrong with my rule?
I have a table of members and want to put them randomly into groups of 5 members each. My intended approach is to have two other tables 'PGroup' and 'GroupMember' then dynamically create a PGroup record, assign 5 random members to it, then create another PGroup and assign another 5 members etc until all members are grouped (I will deal with any excess members seperately, i.e. if clean division by 5 is not possible).
Below is my rule:
If COUNT Member WHERE (Member.Grouped='No')>=5 Then
CREATE PGroup WITH PGroup.Active='Yes'
EXEC_SP 'Random5' RETURN Member
Member.Grouped='Yes'
CREATE GroupMember FOR EACH Member WITH GroupMember.Member=Member,GroupMember.PGroup=PGroup,GroupMember.Active='Yes'
Notes:
My problem is that when this rule runs, all 15 members in my database get assigned to a single group instead of three different groups. I'm not sure what I need to do in order to get a new group created after each set of 5 groupmember records is created. It is as if the 5 returned members from my stored procedure are not placed in context, instead the actions immediately following it are performed on all members in the db. That's what the log indicates anyway.
Please help - thanks.
