Plate number as in vehicle license plate number? That seems an odd choice for an ID.
Why would there be two entries with the same unique combination of time and plate number? If you were to set your system up to prevent such duplication, you wouldn't have this problem in the first place.
I tried to concatenate the time + plate number and create a value list
Can you describe those "indexing problems"? Did you try using a text field with an auto-enter calculation to concatenate the values?
Not only should that work as an indexed field for a value list, a validation rule specifying unique values could catch and prevent the creation of a duplicate value pair in the first place.
The ID which serves to match the records between the tables is the name of the participant. So for a given participant in the Participant table I have several participant appointment records in the Appointments table.
My database is set up in a way that for one participant there can be multiple appointments with the same exact times. These records with the same time values however differ based on other fields (experiment type, experimental lab). I would like to list out for a given day all the non repeating time + license plate numbers.
What I tried for the concatenation:
1. I set up a calculation to join the time + license plate numbers for every record in the Appointments table. Like: appointments::time&"____"&participants::license_plate_number.
2. Then I tried to make a value list out of my calculation field results. But I got a warning that my value list wont work because the field i want to use cannot be indexed.
3. I tried to change the setting for the calculation field in the Storage options to automatically create indexes. But I got the error that my calculation cannot be indexed because it uses a "related field, a summary field, an unstored calculation field, or a field with global storage."
I guess there is something going on with how the relationships are set up in my tables. How can I try to figure out the problem?Where should I start?
Thanks for your help!