Discussions

 View Only
Expand all | Collapse all

Random "number assignment"

  • 1.  Random "number assignment"

    Posted 08-10-2022 21:51

    Hi! 

    I built a junior rodeo app and I'm wondering if there's a way I can do a "random draw" for scorecards.

    We have to generate a random sequence of numbers depending on how many players are in that event. Right now we're using a random number generator, and just taking whatever number order they generate and assigning it manually for over 1,000 scorecards. We have a report, and it's okay to type the number down the list, but it would be great if QB could generate a random draw for us! 

    Here's how it works: The "draw" determines when they come out of the gate - when it's their turn.

    Event A has 14 players, they need to be assigned random numbers.

    Event B has 12 players and need a random number assigned. etc and so on.

    Does anyone have any ideas?



    ------------------------------
    Renee Hansen
    ------------------------------


  • 2.  RE: Random "number assignment"

    Posted 08-11-2022 07:48
    Edited by Prashant Maheshwari 08-11-2022 07:48
    If you are ok with a slow pipeline/jinja solution (although it would be still faster than you manually copy pasting) ,It can generate random ID

    I tried updating a record with following formula and it works great 

    {{ range(1, 10000) | random }}


    ------------------------------
    Prashant Maheshwari
    ------------------------------



  • 3.  RE: Random "number assignment"

    Posted 08-11-2022 10:39
    That's a workable option! How would this work?

    ------------------------------
    Renee Hansen
    ------------------------------



  • 4.  RE: Random "number assignment"

    Posted 08-11-2022 10:39
    Actually I mean, how would I set it up?

    ------------------------------
    Renee Hansen
    ------------------------------



  • 5.  RE: Random "number assignment"

    Posted 08-11-2022 11:12
    Create two fields on your records you wish to generate random number
    • checkbox - GenerateRandom
    • text - RandomScore
    Create a pipeline
    • Trigger- When records are updated , when GenerateRandom=True
    • Update field RandomScore 
    • Value {{ range(1, 10000) | random }}


    ------------------------------
    Prashant Maheshwari
    ------------------------------



  • 6.  RE: Random "number assignment"

    Posted 08-11-2022 11:22
    Thanks! I'll give that a try!

    ------------------------------
    Renee Hansen
    ------------------------------



  • 7.  RE: Random "number assignment"

    Posted 08-11-2022 11:55
    It seems to be working, however, I am getting repeated numbers. Is there a way to not have that happen?



    ------------------------------
    Renee Hansen
    ------------------------------



  • 8.  RE: Random "number assignment"

    Posted 08-11-2022 13:28
    I think this is the proper result . Each record is random , 

    What you are looking for is a list of unique random numbers between 1-100 

    I think excel might be an easier feature here.Export a CSV in excel with 

    Record ID# | DrawTest

    In Drawtest you can put the formula =SORTBY(SEQUENCE(100),RANDARRAY(100))

    Then import it back into QuickBase 

    PS : This is in excel 365 




    ------------------------------
    Prashant Maheshwari
    ------------------------------



  • 9.  RE: Random "number assignment"

    Posted 08-11-2022 14:44
    I'm trying really hard to stay out of excel.

    ------------------------------
    Renee Hansen
    ------------------------------



  • 10.  RE: Random "number assignment"

    Posted 08-14-2022 23:34
    Yeah , I understand , I would also love to have QuickBase only solution . If you do manage to figure this out , let me know I would also love to know

    ------------------------------
    Prashant Maheshwari
    ------------------------------



  • 11.  RE: Random "number assignment"

    Posted 03-22-2023 10:28

    Renee,

    Did you ever solve this with pure QB?  I have new business case that needs this as well.



    ------------------------------
    Don Larson
    ------------------------------



  • 12.  RE: Random "number assignment"

    Posted 03-23-2023 00:58
    Edited by Prashant Maheshwari 03-23-2023 00:58

    Hi Don , 

    i did a couple of tries and the most relevant answer was using the shuffle filter in Jinja but it's not supported via quickbase. I've opened a ticket to ask them if there is a way to emulate the same. 



    ------------------------------
    Prashant Maheshwari
    ------------------------------



  • 13.  RE: Random "number assignment"

    Posted 03-23-2023 10:37

    I have made some minor progress on this.   I took the Jinja expression you used

    {{range(1,20000)|random}}

    to populate a field on the records.   That works great as you described.

    Next I am trying to pull that field and the RID's into a JSON file and order them by the random number field.

    Then I want to extract the RID's from the first 10 records in order by the random numbers.

    That will give me my pool of randomly selected records for the business process.

    Then I will clear the random field in the original data set.

    The hard part is the JSON step but once I have a framework, this should be repeatable for other cases.



    ------------------------------
    Don Larson
    ------------------------------



  • 14.  RE: Random "number assignment"

    Posted 03-26-2023 23:53
    Edited by Prashant Maheshwari 03-26-2023 23:54

    I spent tonnes of time trying to figure this out during the weekend. The randomizing is a pain that I couldn't resolve , even with some logic in place

    1. Have a range from 1 to 100
    2. Start a loop , use random filter to generate one number , add this number to dictionary 
    3. also check if dictionary already has this number , if not , skip , restart loop

    Still I couldn't resolve this. 

    Easier way might be

    1. I think a better way might be , hire a developer from fiverr/or similar which makes a simple python / gsheet based script to randomize a group of number (also sent via webhook), this script should accept (record id#) from webhook , tableid# , range of number to randomize (1-200 or 200-400 etc) 
    2. Start pipeline to send this request as json/webhook (1 API consumed)
    3. This script starts, using logic , assigns a number each randomly to each record id# , send the webhook back.
    4. Using free incoming pipeline action , update records with matching recordid#



    ------------------------------
    Prashant Maheshwari
    ------------------------------



  • 15.  RE: Random "number assignment"

    Posted 03-27-2023 11:13

    I've been watching this thread for a bit, but haven't chimed in, since it sounded like folks were solving the issue.

    But now it is sounding far more complicated. 

    I had a similar need - Assign a random place in line for people making appointments coming to pick up free produce (food pantry). A random place was needed so that the same individuals wouldn't always be first in line.

    Original solution was pre-javascript ban, but I think it can be adapted for folks who know piplines etc (I haven't ventured there yet). We have moved to a different appointment method, so this solution was no longer needed.

    Parent table - Pantry dates - defines a date the pantry would be open, how many appointment could be made. Has a text field to hold a comma delimited list of random numbers, and a button to generate the numbers.

    The button calls a .js code page, with parameters of the record id, and max number of random numbers to generate.


    The code page generates the list of unique random numbers, and updates the Parent record with the list.

    As appointments are added, each appointment is assigned the next random number in the list

    Part([Line Numbers], [# of Produce appointments], ",")

    Appointments are not allowed to be deleted, to prevent a number from being reused.

    For this thread, if the child records already exist, then those versed in pipelines, could make a pipeline run through the list of records and assign the next random number in the parent list.

    I think this fits the original random number issue the original poster had. It might not fit the greater discussion later posters have. 

    Hope this helps if someone needs a simple random number solution.

    The pertinent javascript code for generating the random number line:

    qb_maxnum is the maximum number to generate. Initialization forced anything lower than 10 to be 10.

    The random number methodology I pulled from the web a number of years ago, and I believe the logic is solid.

    function PopulateNumbers() {
        /* First routine called.  This will handle generating the array of random numbers.
         Basic overview
           Populate the array
           Loop through the array to shuffle the numbers
      */   

    for (var widx = 0; widx < qb_maxnum; widx++) { // Load each number into the array
            numberline[widx] = widx + 1;
        } // end for loop load each number

        numberstring = ""; //Clear it out
        for (var widx = qb_maxnum - 1; widx > 0; widx--) { // Shuffle each number into the array

            //select a random number (index) between 0 and the loop idx

            randomnum = Math.floor(Math.random() * (widx));
            tempnum = numberline[widx]; //save the value in the current spot    
            numberline[widx] = numberline[randomnum];  //swap it with the random value 
            numberline[randomnum] = tempnum; //replace value so its available
            numberstring += numberline[widx]  + ", "; // Tack the used number on the end of the number string
        } // end for loop randomize each number
      //Add the last number to the string
     numberstring += numberline[0];

    } //end populate number line



    ------------------------------
    Andrea Best
    ------------------------------



  • 16.  RE: Random "number assignment"

    Posted 03-27-2023 14:12

    Andrea,

    I have had no luck manipulating the data in a Pipeline to give me the random order.   Calling a code page is not likely to be much easier but there are more options.

    I will keep beating on this one.



    ------------------------------
    Don Larson
    ------------------------------