Skip to main content

In Excel I often create formulas that reference a random item from another table. I can’t figure out how to do this in Airtable.


The general way I do this in Excel is:





  • create an autofill column (called “Index_lookup”) that has the formula =RANDBETWEEN(1,ROWS(<table_name>))



  • create another column that has the formula =INDEX(<table_name>, &@tIndex_lookup]],COLUMN(<table_name>mIndex_lookup]) - COLUMN(table_name) +1)





I got some suggestions of how to generate semi random numbers but I can’t figure out how to:





  • query the number of rows in another table


  • generate a random number within that range, although I could do a “normalize” that casts the semi-random number into the max of table length


  • index to retrieve the value at a particular cell of another table




I realize that this is NOT the model that Airtable uses so I am open to using a different model but these do have to be separate tables in my schema.



Thanks for any and all help.



p.s. the context is that over the years I’ve built up a fairly complex Excel workbook to generate sample data for use in various UI design projects. I’ve looked into https://mockaroo.com/ but I find it overly complex and I don’t like its workflow. I’d like to port this project from Excel to Airtable.



I don’t have a lot of time now, but basically you’d have to link to all the rows in the ‘other table’ to do the count. You can also use rollup or lookup at that point to create a long string or array of ‘cells’ which you can combine with your count & random number and a bunch of functions (e.g. ARRAYJOIN, MAX, SUBSTITUTE, MID, SEARCH, etc) to select a ‘particular cell’.



I don’t have a lot of time now, but basically you’d have to link to all the rows in the ‘other table’ to do the count. You can also use rollup or lookup at that point to create a long string or array of ‘cells’ which you can combine with your count & random number and a bunch of functions (e.g. ARRAYJOIN, MAX, SUBSTITUTE, MID, SEARCH, etc) to select a ‘particular cell’.


Ugh. that’s a really gross “solution” and doesn’t automatically update as the “other table” changes over time. This is so easy in Excel.


Thanks, though, for the suggestion.

Yup.



Airtable has no way to access other tables without links by default. And no procedural functions at all. I’m pretty sure the general concept above is the only way to handle this.



Maybe you can do something with Zapier or custom scripting…

Reply


Terms & Conditions