Thank you LN, thank you for the time taken to write the explanation. I tested it and it was working until I selected a certain combination of option and then it gives me an error on save 'This species has been selected' (the constraint message).
I think this is because I am using numerical codes for the names, so I have a species of '7' and a species of '73' which is causing the issue, is this possible?
I would prefer not to change my ID codes to get around this, is it possible to modify the constraint to accommodate this?
Yes, absolutely, that form does assume that each value is unique and not contained in other values. This is a reasonable assumption for selects but you're right that it doesn't make sense for arbitrary strings or integers.
This form shows an alternative using an XPath filter expression (the stuff in the square brackets):
That says to join all of the id values for repeats which are not the current one. I tend to try to avoid XPath in examples but in this case it may actually be a clearer solution all around and less risky because it doesn't assume anything about values entered. What do you think?
I didn't look at @bdra2778's example above in great detail but I think the concept works as well. It uses a parallel repeat to compare every prior value. Do note that it would be slower with a bigger repeat. @bdra2778 there also is one limitation I can think of: because your repeat count is position(..)-1, you won't immediately detect duplicates if someone comes back to edit an answer. For example, if they enter in 2, 6, 7 and then go back to change the 2 to a 7, I believe that will be accepted. However, on validation, the second 7 will show a constraint failure. This is not a big deal but I did want to point it out.
Hi LN, thanks again for your time on assisting me with this, I am an end user rather than a designer but I am learning slowly! I have implemented the Xpath you suggested and it seems to be working well, I'll be doing some more testing today to check on the issue you highlight, going back to change options is generally not an issue on the form I am working with though, but will check.
The alternative I was working on yesterday with your previous example was to create unique ID (a-z; 0-9) which is limited to 36 options, and then using a calculate field to change back to my database values. It would have worked for the form I am bust with as there are <36 select_one options, but going forward this may increase.