Skip to main content

So let's say I have a table that is going to have different numbers in rows listed in a single column, like below.

23

31

6

18

And in another table, I have some ranges filled into two columns, with a min column and max column, and a third column with a name, like so below.

0-10 Sally 

11-20 Joe

21-30 Bob

31-40 Jane

Now how do I have the first table use the second table to evaluate a number to determine which range it fits into so that it can show me the name in the first table?

23 Bob

31 Jane

6 Sally

18 Joe

I'm OK with formulas prior to this need, but now I am totally lost. I don't know if I am supposed to use rollups, lookups, linked tables or what. I've never linked tables before and I need a guru to translate my need into something that gives me a clue on what to try. 1st time here on this forum.

It's like I want to say "see which range my number fits into and display the name for me".

My actual real-life problem is of a larger scale where I have close to a hundred ranges, not four like I have in my example.

Thanks, -Andy

You could try doing this with an automation:

Link to base

Thanks Adam. I have not used an automation before. But does it end up showing the name in table 1 of your example? What I was trying to do is like if your "Names" table would be in the background, and "table 1" would be where all the user experience would be". It's like I am trying to have my users and myself enter in a number (representing a size of a physical item), and depending on the size it is going to be different things show up to indicate what size range it fits into. Maybe I just don't understand how an automation works, but ultimately, I want the same table where I type the size, to also show what size range it fits into. But there are about a hundred size-ranges so I didn't know how to make that work using a formula at that scale. If I only had a handful of size ranges, I could have made that work with a "nested if", which I have done before. Is there a way to get the names Bob, Jane, Sally & Joe to show up in your Table 1?

Thanks Adam. I have not used an automation before. But does it end up showing the name in table 1 of your example? What I was trying to do is like if your "Names" table would be in the background, and "table 1" would be where all the user experience would be". It's like I am trying to have my users and myself enter in a number (representing a size of a physical item), and depending on the size it is going to be different things show up to indicate what size range it fits into. Maybe I just don't understand how an automation works, but ultimately, I want the same table where I type the size, to also show what size range it fits into. But there are about a hundred size-ranges so I didn't know how to make that work using a formula at that scale. If I only had a handful of size ranges, I could have made that work with a "nested if", which I have done before. Is there a way to get the names Bob, Jane, Sally & Joe to show up in your Table 1?


Yeap, that's what the automation would do.  When the automation triggers on the record in "Table 1" it looks in the "Names" table for the record where its number value falls within the range, and then links the two records together

Yeap, that's what the automation would do.  When the automation triggers on the record in "Table 1" it looks in the "Names" table for the record where its number value falls within the range, and then links the two records together


Thanks again Adam. I have spent some time trying to apply your example to my specific situation and I have learned from it, since this is my first time learning about automations. However, I am still having some trouble with the "update record" action. My real-life example that I am trying to make work is different than the simplified problem I originally described. The names table in your example I would have more columns, and "sally, joe, bob, jane" are actually not the first column. I don't know if that is why my attempt is still not working, but when I try to use any other column besides the first column, I get an error in the testing. If my first column were simply a list of numbers, like 1, 2, 3, 4. And, my second column was sally, joe, bob, jane", how would you example be different, given that I still want either sally, joe, bob, or jane to show up in the 1st table? I think that might be what I would need to see, to get me all the way to success. Any hints are appreciated because pretty much every single selection I make in these linking and automation dialog boxes are new to me.

Thanks again Adam. I have spent some time trying to apply your example to my specific situation and I have learned from it, since this is my first time learning about automations. However, I am still having some trouble with the "update record" action. My real-life example that I am trying to make work is different than the simplified problem I originally described. The names table in your example I would have more columns, and "sally, joe, bob, jane" are actually not the first column. I don't know if that is why my attempt is still not working, but when I try to use any other column besides the first column, I get an error in the testing. If my first column were simply a list of numbers, like 1, 2, 3, 4. And, my second column was sally, joe, bob, jane", how would you example be different, given that I still want either sally, joe, bob, or jane to show up in the 1st table? I think that might be what I would need to see, to get me all the way to success. Any hints are appreciated because pretty much every single selection I make in these linking and automation dialog boxes are new to me.


Never mind, I think I figured out to add a lookup field to give me something I can use.

Reply


Terms & Conditions