How to get random drawings from a list of people that have percentage weighted on them

[Tiger1]

I have a list of employees I want to randomly select. I also have them weighted - meaning some will be at a higher rate of getting chosen.

I need an expression for this?

Or maybe this has multiple expressions in different places?

[WillowMobileSys]

I believe we only have the RANDBETWEEN() function within AppSheet to randomly choose numbers.

I think what I would do is assign a list of numbers to each person based on their weighting (maybe randomly). E.g. weight of 1 is 1 number, 2 is 2 numbers, etc.

Count the total numbers assigned and then perform a RANDBETWEEN() 1 and that count. The chosen person is the one who has the generated number in their list.

[Tiger1]

I agree. I have a risk factor table. It basically sets up what you just said:

Risk Factor 0 = employee will not be in random drawing
Risk Factor 1 = Employee will have one entry
Risk Factor 2 = Employee will have 2 entries
etc…

However - how do i write the expressions?

[WillowMobileSys]

I am understanding that you wish to perform a drawing to chose a “winning” employee at some point in time. Because things can change that affect the drawing over time - employee count and/or assignment of Risk Factor, you really can’t assign the numbers until you actually perform the drawing.

The total numbers you assign are based on SUM(<<All employee Risk Factors>>

I would add a column to hold the list of assigned numbers. Then create a set of actions to run at drawing time. First action(s), to make this easy, would just go down the list of employees and assign a sequential list of numbers.

Employee 1 Risk Factor=1 - assign the number list of {1}
Employee 2 Risk Factor=3 - assign the list of {2, 3, 4}
Employee 3 Risk Factor=2 - assign list of {5, 6}
Employee 4 Risk Factor=1 - assign list of {7}
…and so on

Once you have assigned the numbers from 1 to SUM(<<All employee Risk Factors>>

Then you can perform your drawing by randomly selecting one of the numbers with this expression:

RANDBETWEEN(1, SUM(<<All employee Risk Factors>>)

RANDBETWEEN() is an inclusive random function which means it can return the lower bound (in this case 1) and it can return the upper-bound (SUM(<<All employee Risk Factors>>)

The “winning” employee is the one with the drawn number in their list.

[Tiger1]

You gave a great answer, I am afraid i am just lost a little. So I already have a list of employees - would this be same as your "SUM(<>)

COUNT(
FILTER(
Nestle Log Results,
([Last Name] <> “Last Name”,([Last Name]))))

Next,
“Add a column to hold the list of assigned numbers”? - is that to be virtual?

Next,
First action - “go down the list of employees and assign a sequential list of numbers” - what would that expression look like?

Last,
How do i perform the drawing?

To maybe make this make more sense to me:

I have 396 total employees right now. How do i assign those 396 per each employee with their risk factor ( i have a column already giving me this number per employee)

That row is [Number of entries]

I am not understanding how to take that and give that employee the right amount of numbers for the drawing - Meaning

Employee 1 has 2 entries so he gets - {1,2}

[WillowMobileSys]

Not knowing your data I’m trying to give you just the general approach which is why I am lax on details. If someone else has n easier way, please chime in!!

Tiger:

would this be same as your "SUM(<>)

It doesn’t seem so. The SUM() is a sum of the “Number of Entries” across all employees that would participate in the Drawing so something like this:

SUM(SELECT(Employees[Number of Entries], true)) – add criteris if needed to filter out any employee rows

Tiger:

“Add a column to hold the list of assigned numbers”? - is that to be virtual?

No, I believe this should be a normal column. You don’t want these values recomputed every Sync between drawings.

Tiger:

First action - “go down the list of employees and assign a sequential list of numbers” - what would that expression look like?

Again, i don’t know your data so this might be tricky. I would create, with a set of actions, a looping ability to cycle through the employees list assigning the the proper list of numbers. You can refer to @Steve 's article below on how to do this. Of course, you will need to modify it somewhat to fit your use case. Instead of adding rows, you will be updating a list of numbers on an already existing row and column. But the same looping concept still applies.

Add any number of rows from any row within a table Tips & Tricks 💡

Definitely a frequently-asked question: how do I add some number of rows to a table automatically? It’s possible, but it’s not easy. This article details an approach that can be accomplished entirely within AppSheet (no API use), within the app itself (no workflow use), and accommodates an arbitrary number of rows (no built-in count maximum). While I’ve tried to make the guide accessible for less-experienced app creators, make no mistake: this is an advanced technique. I hope you find this us…

Tiger:

How do i perform the drawing?

This is the easy part. Once all Employee numbers have been assigned, you simply need to execute the RANDBETWEEN() expression from before.

How you do this is up to you. You could just run it as part of the group of actions, identify the winning Employee, mark that row as the winner (somehow) and use a Format Rule to highlight the row as the winner.

You could display an action in the app for someone to tap, that then selects the winner as described above. Where and how is up to you.

[Tiger1]

So you are saying in order to get the sequential numbers - i need to create action buttons?

Reading through what Steve created was a bit confusing. But it looks like he is allowing the user to quickly add rows to your spreadsheet inside the app. How, or better yet, what would i change to NOT make this add rows, but to add the proper amount of numbers to the row PER employee?

So something that would take:

Sum of total entries = SUM(employees X risk factors)
Then take that SUM and distribute numbers per employee according to how many each one is suppose to get based on their (Risk Factor)

Quick example of my current data:

I have 396 employees
I have 988 entries (i got this by multiplying 396 X risk factor per employee)

So now i simply need the app to count down starting at 988 or count up to 988 - distributing numbers to each employee. If Employee A has a risk factor of 3 - he needs to get numbers {1,2,3}. This is where i am completely lost. Steve has a great article on adding rows - but i need to add numbers to a row and then move on to the next row without duplicating any of the numbers.

[WillowMobileSys]

I’ll think on it to see if there an easier way to simulate the physical assigning of numbers.

My thought is that maybe we can programmatically determine which user WOULD HAVE been assigned the random number without actually assigning numbers? I am not sure and it certainly is not coming to me at the moment.

[Tiger1]

Thank you. I am just trying to understand it as well. I know the numbers but i cannot figure out how to take (988) and distribute that number into the list without duplicating a number AND giving multiple numbers to those who have multipliers.

[Tiger1]

Tried this - no deal
IF(
[Number of Entries] = “4”,
RANDBETWEEN(1, COUNT(Nestle Log Results[Total Employees entering Random drawing]) *4),

IF(
[Number of Entries] = “3”,
RANDBETWEEN(1, [Total Employees entering Random drawing]),

IF(
[Number of Entries] = “2”,
RANDBETWEEN(1, [Total Employees entering Random drawing]),

IF(
[Number of Entries] = “1”,
RANDBETWEEN(1, [Total Employees entering Random drawing]),

“0”))))

It assigns numbers but not eh right amount of numbers:

Example - if employee 1 is Risk factor 2 - he should get 2 numbers.

[Tiger1]

Any more luck with this? No one else has responded and I am still lost on this.

[Steve]

Here’s my suggestion:

Add a column to the employees table with an App formula expression of:

TOP(
  SPLIT(
    SUBSTITUTE(
      "x,x,...,x",
      "x",
      [_THISROW]
    ),
    ","
  ),
  [Number of entries]
)

This column should be same type of column (virtual or non-virtual) as the Number of entries column.

For each employee, the column will contain a list of references to the employee, where the number of list entries will be equal to the [Number of entries] value.

Then, for the “drawing”, use this expression:

ANY(
  ORDERBY(
    SPLIT(
      (
        FILTER("Nestle Log Results", FALSE)
        + Nestle Log Results[entries-list-column]
      ),
      ","
    ),
    RANDBETWEEN(1, 999999999)
  )
)

replacing entries-list-column with the name of the column added above.

[Tiger1]

Thanks guys. Steve - for the last expression - for the drawing - is that placed in another virtual column - or used as a behavior action?

[Marc_Dillon]

Use Steve’s expression instead

Steve:

ANY(
  ORDERBY(
    SPLIT(
      (
        FILTER("Nestle Log Results", FALSE)
        + Nestle Log Results[entries-list-column]
      ),
      ","
    ),
    RANDBETWEEN(1, 999999999)
  )
)

[Tiger1]

Ok. Thanks

[Tiger1]

???

[Tiger1]

[Steve]

Whoops! Try this instead:

ANY(
  ORDERBY(
    (
      FILTER("Nestle Log Results", FALSE)
      + SPLIT(Nestle Log Results[entries-list-column], ",")
    ),
    RANDBETWEEN(1, 999999999)
  )
)

[Tiger1]

Thanks - the error went away.

Is there a way to take out of the random drawing all employees with [number of entries] = “0”?

Also,
I am noticing it only shows the employees [First Name], Can i get the whole name?

[Steve]

Tiger:

Is there a way to take out of the random drawing all employees with [number of entries] = “0”?

That should already be happening.

Tiger:

I am noticing it only shows the employees [First Name], Can i get the whole name?

It should be showing whatever the row’s label is.

[Tiger1]

This happens sometimes (i am assuming its because of the [number of entries] = “0”, but maybe not?

The label of that table is [Employee Name] not [First Name]?

I want to add - i was unable to make the first expression with the new VC - same type as [number of entries]. I had to make it a list:

And,
The second expression is in my second table (Random Test Table), is that ok?

[Steve]

Tiger:

This happens sometimes (i am assuming its because of the [number of entries] = “0”, but maybe not?

Ah, yeah. Try this:

ANY(
  ORDERBY(
    (
      FILTER("Nestle Log Results", FALSE)
      + SPLIT(Nestle Log Results[entries-list-column], ",")
      - LIST("")
    ),
    RANDBETWEEN(1, 999999999)
  )
)

[Tiger1]

That seems to be working PERFECTLY.

Only issue is it gives this:

I just want to see:
[Employee Name]

Wait, Steve - i see my issue…

Hahaha.

I had a computedkey - doing exactly that:

[Employee ID] “-” [First Name]

Hahahaha

Sorry

[Steve · Accepted solution]

In the configuration for the column with this ANY(ORDERBY(...)) expression we just got working, make sure the List has a base type of Ref with a source table of Nestle Log Results.

[Tiger1]

Thanks for this help. This is a BIG deal for this app…

[Marc_Dillon]

Good general suggestions from @WillowMobileSystems

I’m going to take a slightly different approach for assigning “entries” to each employee, one that won’t depend on ensuring an unbroken list of numbers from 1 to a max.

Let’s create a new VC in your employee’s table. Give it an expression of:

SWITCH( [number of entries] ,
1 , LIST( [employee id] ) ,
2 , LIST( [employee id] , [employee id] ),
3 , LIST( [employee id] , [employee id] , [employee id] ),
…
LIST("")
)

Now let’s go to wherever you’re wanting to perform the drawing. Let’s run the expression:

INDEX(
Employees[new VC] ,
RANDBETWEEN( 1 , COUNT( Employees[new VC] ) )
)

And that should return the employee id value of the winner.

[Tiger1]

Is the second expression in a button (Action)?

[Marc_Dillon]

That depends on how or when you intend to perform the drawing. I don’t believe you have yet described these things. Where should the drawing result appear?

[Tiger1]

Yeah. Im sorry. I want to be able to click a button to run the random drawing — put that in a view.

[Marc_Dillon]

So then you’ll probably want it in an expression in an action to set the value of some column.

[Tiger1]

Can the column - where the results goes- be a virtual column?

[Marc_Dillon]

No. You can’t set the value of a VC with an action, as VCs are not editable. You could just put the expression into a VC, but I highly doubt that would be a good choice because it would run the drawing everytime you sync the app.

[Tiger1]

Yeah - i did that already - and yes it runs everytime

[Tiger1]

So - i need to add a column to the raw data table, then run an action with that second expression?

[Marc_Dillon]

Possibly. I would probably have an entirely new “Drawings” table.

[Tiger1]

Ok. So have an employee key from the drawing table to get all the employees in it - then do the drawing?

[Marc_Dillon]

Tiger:

So have an employee key from the drawing table to get all the employees in it

I’m not sure what you’re thinking here, but no, nothing like that should be necessary. Drawings table, 3 columns: id,date(time?),drawing result (possibly ref to employee table).

At that point you could just throw the expression into the initial value of Drawing Result if you want.

[Tiger1]

Ok. I built the table:

I gave the expression as the initial Formula to Drawing Result:

But now, how do i make it actually randomly draw? A button?

Update. I made a form and it seems to draw rndom numbers - which is great. But two things:

I cannot attach the employee to the number drown?

It shows the number drown as many times as the employee had entries. So if the emplkoyee was entered twice it shows:

[WillowMobileSys]

@Tiger @Marc_Dillon @Steve

Hey guys! Thanks for stepping in and giving better suggestions. For the record, I did play with both approaches I provided. I leaned more towards the summing approach I explained because it was easier. I ran into small issues and set it aside then got busy - gotta make a buck when I can!

But both of your suggestions are way better!