So I have a table called VENUES that has a field "VENUES::venueLocation" and "VENUES::venueName".
The Venue Location value, for example, could be "New York" or another state. The Venue Name value could be anything.
For this example, let's say there are 4 records
1 - New York - Warehouse 1
2 - New York - Warehouse 2
3 - Vermont - Warehouse 1
4 - Vermont - Warehouse 2
I would like to make it so that when I enter another record "5 - Vermont - 'VENUES::venueName'" that the venueName field is unique but only to the records that are in the same location. I tried setting the field to be only unique values but that causes an issue with the records from another location that uses the same venueName. I have already created another instance of the VENUES table and did a self-join where the only records that match would be if they had the same venueLocation and created a value list from this context to get just a list of venueName's to compare against. But I can't figure out how to write the calculation to validate by calculation in the validation settings of the field.
I hope this has explained my issue well enough and I would be happy to elaborate if needed. So can anyone let me know how to make this work?