Generating random values for rows using a button

1

Hi Community! The new Randomitem function is just in time to help me, but I can’t quite get it to do what I need.

What I have a is hefty table where each row represents a school district’s application. Each row also includes a point of contact (POC). Many, but not all, applications will expire June 30 and they will need to be assigned a new POC. Since there are over 300 applications and we have a team of 5, I’m trying to find a way to assign the team equitably across the expired applications.

I cannot use a column formula because some applications will NOT expire until next year and those POCs need to remain in tact. I have already worked out a formula to correctly label those that have expired and a button to make the POC blank for any expired applications.

So, enter the new button with a filter to only affect rows where the POC is blank.

Right now, I have a small, separate table with the list of potential POCs - Application Reviewers.Reviewer - from which to pull the values. My button formula right now is simply RandomItem(Application reviewers.Reviewer).

The randomitem function is working, however, it picks one random item from the list and then assigns that selection to EVERY row. What I need it to do is a choose a NEW random item for each row. Can anyone help me out?

2

Hey Kat!

First, use FormulaMap() to update one row at a time (instead of updating all rows with the same value). Second, wrap ModifyRows() in RunActions() so that the doc has a chance to recalculate between invoking Random()s:

Reviewers.FormulaMap(RunActions(
  CurrentValue.ModifyRows(Reviewers.Reviewer, RandomItem(...))
))
3

Thanks, Paul!

For anyone else’s reference, my final formula was
FormulaMap(Filter([Application Statuses], AND(POC.IsBlank(),OR([Exp. Date]=“EXPIRED”,[Exp. Date]=“UNAPPROVED”))) ,RunActions(ModifyRows(CurrentValue,[Application Statuses].POC,RandomItem([Application reviewers].Reviewer))))

In running with checks, it did provide a relatively even distribution among team members.

4

Great to hear!

Give the “fluid” way of writing formulas a try (i.e. X.f().g() instead of g(f(X))). It’s a bit unfamiliar after Excel/Sheets but once you get the grip of it you’ll love it:

[Application Statuses].Filter(
  POC.IsBlank() AND [Exp. Date].In("EXPIRED", "UNAPPROVED")
).FormulaMap(RunActions(
  CurrentValue.ModifyRows(
    [Application Statuses].POC, [Application reviewers].Reviewer.RandomItem()
  )
))
2 Likes