Indexing of linked records
J
Joseph
I would like to be able to select an attached record by its index. The use case would be to select a record at random from a grouping of records that meet some criteria - i.e picking a winner of a giveaway.
Currently, my random number is generated using 3rd party automation (it would be great if a random # generator could be added in some capacity, but that is a different thing). Assuming you select the number or generate it randomly, there is currently no good way to use that number as an index to select a record.
I imagine a formula option called "INDEX" that takes 2 arguments. The first being the list of records, and the second being the index. This formula will search all the attached records and return the record matching the index count .
Ex: List = [entry1, entry2, entry3]
INDEX ( List, 0) = "entry1"
INDEX ( List, 2) = "entry3"
A
Adam Johnston
Hey Joseph, you can get a pseudo random number generator with MOD(DATEDIFF(DATE(" 12:00 01 01 1980"),NOW(), "minutes")*479001599, [max value]), where [max value] is the max integer you want it to return (479001599 is just a big-ish prime number, feel free to choose a different one), and you can get something like the index function you are looking for by filtering linker records based on their autonumber: FIRST(GET_LIST([Related Record].[Auto Number] == [Random Number], [Related Record].[title]))
J
Joseph
Hey Adam Johnston, that's pretty clever, I wouldn't have thought of that method. I'll look into this some more!
J
Joseph
Adam Johnston This was very clever, but I don't think it will work for my application. The random number in this method will never be able to select the ends of the list - it seems to be a fixed result based on the divisor. In my application, I will need one of any of the available list to be chosen, including the limits. (the length of the list serves as the divisor, which changes each iteration, but this issue seems to remain)
The indexing idea is very cool but the only issue is that the autonumber won't reflect the actual index in the list. For example, the 4th item may have autonumber 39.
working through the logic of your suggestions was extremely educational, so thank you for the suggestions! Let me know if I am missing something, which is certainly possible, and I'll keep looking into it!
For context - I currently feed the list length as a limit and am using chat GPT to generate the number through Make. The list length is first generated through internal SS automation when a toggle is triggered via Make record update. Then the rest of the automation can continue through a second webhook to Make (second scenario).
A
Adam Johnston
Joseph I would caution against using chatgpt to generate a random number because it can make mistakes that might break your workflow.
You might want to stick to using Make to do the job, but for completeness:
You should be able to dynamically limit the random number based on how many giveaway records you have linked with MOD(DATEDIFF(DATE(" 12:00 01 01 1980"),NOW(), "minutes")
479001599, COUNT([Link to Giveaway Records])) + 1. If you want the number to remain static use MOD(DATEDIFF(DATE(" 12:00 01 01 1980"),[First Created], "minutes")
479001599, COUNT([Link to Giveaway Records])) +1.Note the "+1"s. They are important because that formula will return values in a range starting from 0.
You should then be able to implement an actual indexing formula based on the linked records using something like
NUMBER(RIGHT(ARRAYJOIN(TOP(RELATED_RECORDS_SORT([Link to Giveaway Records].[Auto Number], [Link to Giveaway Records].[Auto Number]), [Random number generator])), 7)).
That will return a random auto number belonging to one of the giveaway records you have linked. Make sure and remove the labels on the auto number in the giveaway records and set the leading zeros to "000000" (to match the "7" above). You can then use that chosen index to get the relevant record:
FIRST(GET_LIST([Chosen index] == [Link to Giveaway Records].[Auto Number], [Link to Giveaway Records].[title]))
A
Adam Johnston
Joseph see an example solution here: https://app.smartsuite.com/shared/s5ch1upc/shared-solution/fuSyIKRfod/. You should be able to copy it
Jon Darbyshire
Great to hear your perspective, Joseph! I have a few more questions for you:
- Can you provide more details on how you envision the 'INDEX' formula working with larger lists of records?
- Are there any specific criteria you would like to use for selecting records?
- Would you find it useful if the system could automatically generate a random number for the index?
J
Joseph
Jon Darbyshire Thanks for asking!
- I mainly see it as a means to pull a specific record from a grouping of a larger list of records. However, I could see external uses potentially around finding a record through other identifiers, and then returning the index of that record. This seems like it could have some benefit if you are sorting the list by some specific method and would need to iterate through a list to find an error.
I envision that the list could be a direct pointer to a linked record field, or a separate formula list (think something generated by GET_LIST)
- Assuming you are supplying the INDEX yourself and the formula returns the record, it would be nice to have an optional 3rd argument(s) that filters the return value(s). For example:
BestSoftwareList = [SmartSuite, Microsoft Office, Google Workspace]
INDEX (BestSoftwareList, 0) = SmartSuite, 10-20 people, software company, really cool team, superior no-code platform... (all the information associated with the record).
OR
INDEX (BestSoftwareList, 0, BestSoftwareList.Name, BestSoftwareList.Size) = SmartSuite, 10-20 people
- I think a random number generator tool would be useful in other scenarios too, but specifically in this one. Having an internal way to generate a random number (with upper and lower limits specified) would be amazing.
NOTE: smart docs can do this if you have AI on a paid plan but you can't automate with it.
Hope that helps!