Skip to main content
Solved

Randomly select x records from table (random sort or filter) using NOW()?

D
Forum|alt.badge.img+5

I've been trying to get this to work using a formula but haven't figured it out...What i want: I have a table that contains 1000 records, 12 of which i'm displaying on the web frontend via softr.io. Now what i want is to randomize the 12 records that will be displayed. This does not necessarily need to be randomized each time the page is refreshed/the API call to retreive the records is made. It could also randomize every minute or so.

I guess randomisation on refresh/load would have to be made via the API call somehow. But if the API call always retreives records based on a static filter, I would have to randomize the results that the filter retreives on the Airtable side.

Any idea how to do this?

One simple solution I thought of which should semi-randomize every time the result of NOW() is updated in Airtable: Number records with autonumber. Take the seconds from NOW() and calculate based on the second (which is a value between 0 and 60) which block of 12 records should be taken. I.e. if NOW() is xx:00:43 take the 43rd block of 12 records...There would be 60 different results and quite a few records that would never show up, but that would be better than always have the same 12 records being displayed (which is the status quo).

Ideas?

Best answer by David_Weiss2

So I share the solution I think of using: 

I have a table with 1000 records, say blogposts. On the start page of my blog, I want to show 10 blog posts from that table, but I want those records to be displayed randomly so that if users visit my website multiple times I want them to not always see the same 10 blog posts, but be served 10 random posts...

...my solution works insofar as that visitors are very unlikely to see the same posts if they visit my page on different days, but if they reload the page within an hour or so, the same blog posts are likely to be displayed.

How: I added a column "autonumber" (assigning each record a number betwen 1 and 1000). I then have another field that takes the minutes part of NOW(), i.e. a number between 1 to 59, depending on when Airtable last updated the NOW() value for my base (using the DATETIME_FORMAT formula)

I then have that formula field calculate a range of about 20 records that moves from 1 to 1000 depending on NOQ(). When NOW() is 1, it will be the first 10 records and when NOW() is 59 it is the last 10 records.

The formula just divides the total number of records (1000) by the number of minutes (60) multiplied by the NOW() value. I use this value to filter out records if their Autonumber is not within -10 or +10 of that value and voila, whenever the the minute in NOW() changes there will be a different set of records displayed. It is true that at 14:35 hrs, it will always be the same set of records (the 35th set of records out of 60 sets of records) but unless visitors always visit my site at the same minute, they will not see the same records...

...this obviously depends on the rate at which NOW() is updated which we can't control as users.

View original
    Did this topic help you find an answer to your question?

    5 replies

    J
    Forum|alt.badge.img+11
    • J_W1
    • Known Participant
    • 21 replies
    • March 22, 2024

    Just thinking out loud here.

    1. Have a filtered view on condition : field X = checked.

    2. An Airtable automation
    Trigger = running every 15 minutes
    Action Step 1 = Find Records action, to find all current records having a field X checked
    Action Step 2 = Script action having 12 records picked from the table randomly
    Action Step 3 = Applying a check in field X on all records pick in step 2
    Action Step 3 = Removing the check in field X on all records found in step 1

    3. And link this filtered view to display in the Softr.io front-end?

    TheTimeSavingCo
    1 person likes this
    • TheTimeSavingCo
      TheTimeSavingCo
    D
    Forum|alt.badge.img+5
    • David_Weiss2
    • Author
    • Inspiring
    • 12 replies
    • March 22, 2024
    J_W1 wrote:

    Just thinking out loud here.

    1. Have a filtered view on condition : field X = checked.

    2. An Airtable automation
    Trigger = running every 15 minutes
    Action Step 1 = Find Records action, to find all current records having a field X checked
    Action Step 2 = Script action having 12 records picked from the table randomly
    Action Step 3 = Applying a check in field X on all records pick in step 2
    Action Step 3 = Removing the check in field X on all records found in step 1

    3. And link this filtered view to display in the Softr.io front-end?


    thanks for your suggestions. looks a bit like a "brute-force" approach and uses a few thousands of automation runs every month, but is a possibility...thanks.

    D
    Forum|alt.badge.img+5
    • David_Weiss2
    • Author
    • Inspiring
    • 12 replies
    • March 22, 2024
    David_Weiss2 wrote:

    thanks for your suggestions. looks a bit like a "brute-force" approach and uses a few thousands of automation runs every month, but is a possibility...thanks.


    Also, do we know now how often/consistently NOW() updates?

    TheTimeSavingCo
    Forum|alt.badge.img+28
    David_Weiss2 wrote:

    Also, do we know now how often/consistently NOW() updates?


    re: Also, do we know now how often/consistently NOW() updates?

    Hard to say I'm afraid, the only thing we know for sure is if you're using NOW() with an automation it's every hour or so.  I've seen people talk about it doing like 1-4 hours too, and so your best bet's probably @J_W1 's solution if you want to do it on Airtable's side

    Adam, Consultant @ The Time Saving Co
    D
    Forum|alt.badge.img+5
    • David_Weiss2
    • Author
    • Inspiring
    • 12 replies
    • Answer
    • March 25, 2024

    So I share the solution I think of using: 

    I have a table with 1000 records, say blogposts. On the start page of my blog, I want to show 10 blog posts from that table, but I want those records to be displayed randomly so that if users visit my website multiple times I want them to not always see the same 10 blog posts, but be served 10 random posts...

    ...my solution works insofar as that visitors are very unlikely to see the same posts if they visit my page on different days, but if they reload the page within an hour or so, the same blog posts are likely to be displayed.

    How: I added a column "autonumber" (assigning each record a number betwen 1 and 1000). I then have another field that takes the minutes part of NOW(), i.e. a number between 1 to 59, depending on when Airtable last updated the NOW() value for my base (using the DATETIME_FORMAT formula)

    I then have that formula field calculate a range of about 20 records that moves from 1 to 1000 depending on NOQ(). When NOW() is 1, it will be the first 10 records and when NOW() is 59 it is the last 10 records.

    The formula just divides the total number of records (1000) by the number of minutes (60) multiplied by the NOW() value. I use this value to filter out records if their Autonumber is not within -10 or +10 of that value and voila, whenever the the minute in NOW() changes there will be a different set of records displayed. It is true that at 14:35 hrs, it will always be the same set of records (the 35th set of records out of 60 sets of records) but unless visitors always visit my site at the same minute, they will not see the same records...

    ...this obviously depends on the rate at which NOW() is updated which we can't control as users.

    Reply


    Most helpful members this week

    Terms & Conditions