Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Shuffling records by five members monthly

465 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Bolufmi
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a database with ages, I have grouped the ages into views and I want to shuffle each members a particular age into a group of five. For example: Ages 18-24, I want the members to be grouped by five members each and at the end of each month, the automation shuffle the members so the members are different for each month.

5 Replies 5
Alexey_Gusev
13 - Mars
13 - Mars

Hi,

There is a lot of ways to get random numbers by formula, but none can guarantee you get groups 5 people each. I don't know how python can help with your case, but JS version can be like this.
Note: 'Group', or you can name it as you wish, should be a Text field. If you want to make it Number.
remove .toString() in line 7

 

 

const [ FLD, QTY ] = [ 'Group' , 5 ]
const table=base.getTable(cursor.activeTableId||base.tables[0].id);
const view=table.getView(cursor.activeViewId||table.views[0].id)
const randomsort=(a,b)=>Math.random()-0.5
const query=await view.selectRecordsAsync({fields:[]})
const shuffled=[...query.records].sort(randomsort)
const update=(rec,ix)=>({id:rec.id,fields:{[FLD]:(1+Math.floor(ix/QTY)).toString()}})
const upd=shuffled.map(update)
while (upd.length) await table.updateRecordsAsync(upd.splice(0,50))

 

 

 

 

Bolufmi
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you for the response.

I tried the formula, but it is giving me an error, I changed the filed to Age since I already have the Age field which is a number field, that means I also removed the .String().

Please, what can I do?

 
Bolufmi
5 - Automation Enthusiast
5 - Automation Enthusiast

I am not importing data from SQLite, my data is synced from Memberstack using Airtable, the issue is to shuffle the members.

Better use that script.
I've added Input settings and improved shuffle logic

 

const config = input.config({title: 'Shuffler',
 items: [input.config.table('table', {label: 'Select table'}),
 input.config.field('group', {label: 'Choose field to set group', parentTable: 'table'}),
 input.config.view('view', {label: 'Select view', parentTable: 'table'}), 
 input.config.number('qty', {label:'Persons in group'})]
})
const {table,group,view,qty}=config
const shuffle=arr=>arr.map(x => [Math.random(), x]).sort().map(([_, x]) => x)
const query=await view.selectRecordsAsync({fields:[]})
const update=(rec,ix)=>({id:rec.id,fields:{[group.name]:(1+Math.floor(ix/qty)).toString()}})
const upd=shuffle(query.records).map(update)
while (upd.length) await table.updateRecordsAsync(upd.splice(0,50))

 


It's not formula, it's a script for scripting extension
Alexey_Gusev_0-1738590433149.png
 
Alexey_Gusev_1-1738590449896.pngAlexey_Gusev_2-1738590503098.png



Alexey_Gusev_3-1738590562116.png

Alexey_Gusev_4-1738590631290.png

 



Alexey_Gusev_6-1738590731694.png

Note: I don't think you should set 'Age' as field to set group, as it will overwrite all values which are currently present in a field.



georgemartin72
4 - Data Explorer
4 - Data Explorer

You could use a randomization function to shuffle the members within each age group and then assign them to smaller groups of five. For the monthly shuffle Pacchetto per aumentare recensione, a scheduled task or trigger can run at the end of each month to update the groupings. If you're working in SQL, something like ORDER BY NEWID() (for SQL Server) might help randomize the order.