Randomly assign a user to a record.

[Spitfire]

Hello, (sorry if this is covered elsewhere, but I cannot seem to find the answer).

I am building purchasing request app that has multiple levels, from the request to the approval to the purchase.  Each request belongs to a department, and each department has at least one purchaser, however, some have multiple purchasers.  I would like the app to assign the purchaser on its own, and I can achieve that with Lookup for those departments with only one purchaser.  My issue is that I would like the system to randomly choose a purchaser from a list of those who have multiple purchasers.  

Example:

Budget has two purchasers where Admin has one.

1. A request is entered for Admin, it is approved, and the purchaser is assigned automatically via my lookup formula.

2. A request is entered for Budget, it is approved, and the purchaser is assigned automatically (randomly) from the pool of two people (different each time).

My current formula is: IF(COUNT(FILTER(Purchasers,[Dept/Bureau] = [_THISROW].[Dept/Bureau])) =1,LOOKUP([_THISROW].[Dept/Bureau],"Purchasers","Dept/Bureau","Email"),"PENDING")  This assigned the purchaser for those with 1 purchaser, and assigned "PENDING" to any others. 

I have also tried:  Any(Select but it always picks the top of the list.  I need random.

Thanks

[Marc_Dillon]

ANY() is equivalent to INDEX( ... , 1 ). Just replace the "1" with a RANDBETWEEN().

[Spitfire]

I guess I am not understanding.  I used: INDEX(FILTER("Purchasers",([Dept/Bureau]=[_THISROW].[Dept/Bureau])),RANDBETWEEN(1,2)) but only get the first key value.  I don't need the key value, I need to call out the email related to the key value.  

[Marc_Dillon]

FILTER is a special case of SELECT, that always returns key values. Don't use it if you don't want the key value.

https://help.appsheet.com/en/articles/2357314-select

[Spitfire]

Okay, I am using Index(SELECT(Purchasers[Email],([Dept/Bureau]=[_THISROW].[Dept/Bureau])),RANDBETWEEN(0,2)) but only getting the second value now, when I do RANDBETWEEN(), the system errors.  When I do RANDBETWEEN(1,2) it only gives me the first value.  What am I doing wrong?

[Marc_Dillon]

Appsheet is 1-indexed, not 0.

[Spitfire]

Okay, that was frustrating, to say the least.  Your solution works flawlessly once I understood it, and once I tested the formula more than once.  The first time it randomly assigned only the first value, then the second time the second value.  I have tested it now five times, and it is randomizing the assignments.  The final formula is:  Index(SELECT(Purchasers[Email],([Dept/Bureau]=[_THISROW].[Dept/Bureau])),(RANDBETWEEN(1,2)))

You ROCK!

[Spitfire]

@Marc_Dillon  We have been using the app in a deployed state for about two weeks now.  Unfortunately, the random statement is not working.  The app assigns the same person in 9 out of 10 cases.  I have tried re-writing the formula, just reorganizing it, and both ways it assigns one person over the other.  Does not really seem random.   I could look at building a formula to count their assignments and assign to the lower person, but I would really like for the random function to work.  I am sure it is just me!

Here is the current formula:  IF(COUNT(SELECT(Buyers[Email],([Dept/Bureau])=[_THISROW].[Dept/Bureau])) >=2,Index(SELECT(Buyers[Email],([Dept/Bureau]=[_THISROW].[Dept/Bureau])),(RANDBETWEEN(1,2))),LOOKUP([_THISROW].[Dept/Bureau],"Buyers","Dept/Bureau","Email"))

Here is the past formula:  IF(COUNT(FILTER(Purchasers,[Dept/Bureau] = [_THISROW].[Dept/Bureau])) <=1,LOOKUP([_THISROW].[Dept/Bureau],"Purchasers","Dept/Bureau","Email"),Index(SELECT(Purchasers[Email],([Dept/Bureau]=[_THISROW].[Dept/Bureau])),(RANDBETWEEN(1,2)))

[Steve · Accepted solution]

I've seen cases where RANDBETWEEN() will give the same "random" result several times in a row when used repeatedly in quick succession. The work-around is to introduce some variation in the numbers of your range. So instead of this:

RANDBETWEEN(1, 2)

Try this:

(RANDBETWEEN([_THISROW], ([_THISROW] + 1)) - [_THISROW] + 1)

Or:

(MOD(RANDBETWEEN([_THISROW], ([_THISROW] + 9999)), 2) + 1)

[Spitfire]

Thank you,

---

@Steve wrote:

I've seen cases where RANDBETWEEN() will give the same "random" result several times in a row when used repeatedly in quick succession.

---

So these assignments in our system were days apart, not in quick succession.  I don't know if that matters.  Either way, I will try your solution.  Thank you for the help.

[Spitfire]

So when I replace the RandBetween it tells me "Arithmetic expression '([_THISROW]+9999)' has inputs of an invalid type 'Unknown'"  It does this for either.

Would it be better to come up with a formula that compares assignments and assigns the next to the next person in line, though I cannot think of what that would look like right now.

Here is the current formula: IF(COUNT(SELECT(Active Buyers[Email],([Dept/Bureau])=[_THISROW].[Dept/Bureau])) >=2,Index(SELECT(Active Buyers[Email],([Dept/Bureau]=[_THISROW].[Dept/Bureau])),(RANDBETWEEN(1,2))),LOOKUP([_THISROW].[Dept/Bureau],"Active Buyers","Dept/Bureau","Email"))

[Steve]

Whoops! In my suggestions, replace _THISROW with _ROWNUMBER.