There may be multiple instances of any county in that field for different contacts.
Multiple instances in what format? I'm guessing you have multiple values in one field, but separated by what character?
Do you have a table of county acronyms? If not, do you have a value list of county acronyms? Either could be used with a calculation field defined in County to return a 1 if one of the listed acronyms matches one in your list of 67 counties, but the details depend on the format of your his_exchange field.
I should give a little more background on the files:
The background is that it is an Amateur Radio contest, where people try to contact as many people as possible during the contest. The individual counties serve as multipliers. The participants send in a textfile log in a mostly consistent format. I import that into Excel (open office Calc actually) then into Filemaker, and occasionally mess with the import order to get everything consistent.
Each record has a field for the Callsign of the person, the callsign of the person he contacted, then a numerical exchange, a county exchange, time, and frequency. Typical are hundreds of contacts.
The field "his_exchange" has any one of 67 possible counties in it. That particular field will have the same acronym in several records, because some counties are rare, and some very common.
As for a table or list, that would be simple to make up if needed. I had originally tried multiple finds, and that just didn't work too well.
So his_exchange only has one value in it?
With a table where you have 67 records, with a different county acronym in each record, you can create this relationship:
ContestData::His_Exchange = CountyAcronyms::Acronym
Then Your other field, defined in ContestData can use this expression to return one if the acronym in His_Exchange matches an acronym in CountyAcronyms:
Not IsEmpty ( CountyAcronyms::Acronym )
I suspect that might not be sufficient here as you keep referring to multiple instances so it may be that you have multiple records for the same contestant and you want a maximum of 1 returned for all of the records for that contestant. If that's the case let me know and also tell me more about the data being entered.
I suspect that might not be sufficient here as you keep referring to multiple instances so it may be that you have multiple records for the same contestant and you want a maximum of 1 returned for all of the records for that contestant
That's it. He might have say 100 contacts total, and perhaps 10 would be from one county. Maybe only one from another. The value total is the same for either, just 1.
I'll get one of the files and let you know how they are arranged - The computer I do the soring on is at home. Thanks much!
If he has 10 from one county AND 1 from another ,wouldn't you want a result of 2? (This would count the number of counties contacted and I thought that was the point of the contest.)