Select from a filtered list. Need help

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

@11162

Here’s a demo of the same concept @BenLee posted.

2 Likes

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.