Randomly assign a Group value to a each record (with selected variables) - batch update app doesn't suffice

[Adriana_Rose]

I have a list of names of people enrolled in my class. Is there an easy way to place them into randomized groups of 6? Ideally, I could do this to have gender-balanced groups. Any suggestions would be welcome.

But then, I also would like it to have prevention such as, if they answered yes in not being in Madrid, please put on the same group.

Groups [ Group 1, Group 2, Group 3, Group 4, Group 5, Group 6, Group 7, Group 8]

I.e., If value for In Madrid? Yes, automatically add to Group 8, otherwise, randomly add to another group, but don’t allow more than 6 records to be assigned to each group.

Ideally, I want to assign a record (name) to a different group every week, and would eventually want to create a new row of groups, where the record (name) is assigned to different groups, but make sure it’s not the same as the previous group assignment.

I hope this makes sense! Adriana

[Kamille_Parks]

Do you want to override the group assignment each week, or do you want to keep a record of “So and so was in Group 4 on October 29, Group 2 November 5, etc.”?

[Kamille_Parks]

And does it matter if “Joe” happens to be in “Group 4” two weeks in a row if the rest of Group 4 is different on the second week?

[Adriana_Rose]

I’m thinking of creating different rows, as you’ve said "“So and so was in Group 4 on October 29, Group 2 November 5, etc.”

And, I guess it doesn’t matter if Joe was in group 4 two weeks ago, if no one else is in the same group from the second week.

[Kamille_Parks]

You could use this script to add a new set of “assignment” records. The script assumes you have a “People” table which links to your table which holds the assignments. The script will place all the people with a checkmark in one random group, ignoring the cap of 6, then will place everyone else in random groups. If the group which has the checkbox folk isn’t full, people may be randomly assigned to that group until it hits the cap.

You’ll need to change the relevant field/table names to ones that match your base, look for anything in quotes (i.e. you’ll change “Exception” to whatever the name of the checkbox field is).

const peopleT = base.getTable("People")
const peopleQ = await peopleT.selectRecordsAsync()
const peopleR = peopleQ.records

const assignmentsT = base.getTable("Assignments")
const groupsField = assignmentsT.getField("Group")
const groups = groupsField.options.choices

const today = new Date()

const exceptions = peopleR.filter(person => person.getCellValue("Exception"))
let unassigned = peopleR

const assignments = groups.map(x=> ({group: x, people: []}))
const exceptionGroupIndex = Math.floor(Math.random() * assignments.length)

// Place all the people with "exceptions" in the same random group
exceptions.forEach(person => {
    assignments[exceptionGroupIndex].people.push({id: person.id})
    unassigned = unassigned.filter(x => x.id !== person.id)
})
// Place the rest of the people in groups at random
while (unassigned.length) {
    assignments.forEach((assignment, index) => {
        while (assignment.people.length < 6 && unassigned.length) {
            const person = unassigned[Math.floor(Math.random() * unassigned.length)]
            assignments[index].people.push({id: person.id})
            unassigned = unassigned.filter(x => x.id !== person.id)
        }
    })
}

let updates = []

assignments.forEach(x => {
    x.people.forEach(person => {
        updates.push({
            fields: {
                "Date": today,
                "Group": {name: x.group.name},
                "Person": [{id: person.id}]
            }
        })
    })
})

while (updates.length > 0) {
    await assignmentsT.createRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

[Adriana_Rose]

Thanks Kamille! I’ll give it a try now!