See the image below.
I need to make unique numbering. If the number 999.01.A is already occupied, there must be only B,C and D options in select list. Is it possible to make something like that?
This is a tricky one!
This solutions uses a Lookup column type and then adds a filter for the values pulled in. The formula for the custom filter is:
CurrentValue.Contains(thisTable.ConstrVar).Not()
1 Like
One semi-unrelated question @BenLee: why did you write CurrentValue.Contains(thisTable.ConstrVar) and not thisTable.ConstrVar.Contains(CurrentValue)?
I know they work either way: A.Contains(B) is true if there’s any overlap of A and B regardless if those are elements or lists, or either is a list and the other one is an element. But still, wondering about the thinking process here.
I always write either Collection.Contains(Element) or Element.In(Collection). Or maybe there’s some perf benefit?
No real reason I chose one over the other here, and I haven’t checked to see if there is a performance benefit to either one.
I’ve found that I gravitate to writing formulas the way you mentioned when I’m “outside” of a given table and the reverse when I’m “inside” a given table. When “outside” a given table, you’re referencing the table more directly, just from a visual formula standpoint. I guess it’s just something about how my head sees the problem.
You’re also right in that Contains() works a “Contains Any”, so either way it’s written shows the overlap.
The tricky situation here is it’s somewhat of a circular reference, you’re referencing the same column you’re acting on in the formula, so trying this strategy with a Select List column type will give you a warning. It’ll still work, but will show an error in the formula builder.