Relation Select based on Formula

[Shannon_Bradley]

I’m sure it in in here somewhere, but I perhaps can’t figure out how to search it properly.

I have a Helper Table with a bunch of Milestones

which is connected to my main Members table. I can currently manually select the milestones for each person of course, but I thought it would be cool if it happened on its own.

The formula would be long (and I think achievable) but I’ve tried a bunch of things and my brain frizzled. It would go something like:

If Troop Total >10 then show 10 Troops
If Troop Total >25 then show 25 Troops
If Troop Total >50 then show 50 Troops
If Troop Total >100 then show 100 Troops (etc)
If Today - Approval Date > 5 years show 5 Years
If Today - Approval Date > 10 years show 10 Years
If Today - Approval Date > 20 years show 20 Years (etc)

So is this something that should be done another way? Am I making it too complicated from the get-go? The reason I went the helper table route is so I have a reverse list of who achieved each Milestone…

If someone knows an existing topic that explains this exact thing that would be great!

Thanks!

[Christiaan_Huizer]

hi @Shannon_Bradley , you need a filter with logical operators instead of an if or a switchIf logic.

You may want to share a sample doc and I am sure that one of the many great community members is willing to have a look, define the filter and show you how to move forward, cheers, christiaan

[Shannon_Bradley]

Looking up info on that!

Hopefully I did the share on this correctly, this is the sort of thing I’m looking at https://coda.io/@shanbra-film/relation-select-based-on-formula

https://coda.io/d/_d6EWXXiXKsY/Relation-Select-Based-on-Formula_suxix9Tz

[Christiaan_Huizer]

hi @Shannon_Bradley ,

can you adapt the sharing sessions so people can edit in the doc.

this is my contribution based on having a column with numbers in the second table to reference in my filter that looks into the first table:

[DB Members].Filter([Total Troops] >= thisRow.nbr)

it could be very well you need something more refined since you look for years and troops, but because I don’t have a clue of the logic you are working with, this is it for now.

It is weekend here in Europe, time for the champions league final

Enjoy, Cheers, Christiaan

[Shannon_Bradley]

So I WILL need additional fields. I can start by just separating the types of milestones, not a problem with that lol.

I didn’t think about filtering from the Helper Table end, that makes perfect sense!

Edit to add
The advice to look at things from the other direction is awesome, and I think makes all the difference. I’m so close! Now to go read up on date formulas to make this second bit work. This community is amazing