Calculation on multiple related records

[Mark Turner]

I'm trying to summarize the data from a multi-record per relationship file into a single field in a destination file. I'm using a calculation field in the destination file, but it only finds data in the first related record.

WTUCountyWorking.fp5 has these fields (plus others not used in the calculation):

SymbolKey (used to define relationship)

State

County

Data in the State field is either "Washington" or "Oregon" and the County field contains the name of one of the 75 counties in the two states. There are anywhere from one to 40 records which have the same value in SymbolKey and more than 16,000 records in the file, which came from an outside source.

OR_WA_County_WTU.fp5 has these fields:

SymbolKey (used to define relationship)

75 individual County fields, of the form WA_Adams, WA_Benton, OR_Baker, etc.

The county fields are all calculated, using the following formula:

If( (WTUCountyWorking::State="Washington" and WTUCountyWorking::County="Adams") , 1 , 0 )

The goal here is to end up with a "1" in the county field if at least one record in the related database has the county name in the "County" field.

Is there a way to have the calculation look at all related records, or is there another technique I should be using?

[ESpringer]

It sounds like you've put lots of unnecessary work in. That set of 75 separate county fields will do you no good unless you get 75 separate relationships to go with them. Meanwhile, it's not clear *what* relationship your "symbol key" is facilitating? What's being matched to what? My guess is that your relationship is irrelevant to the task you're asking it to perform...

I'm reluctant to give further advice until I know the structure of your data. If you've got 75 different county FIELDS in the second database, what is each separate record for? My sense is you probably want a RECORD for each of the counties, entered in as something like "WA_Adams" *in* the field for state&county, and then matching that to a field in the other database for State & County combined (a calc field that puts the two together with a "_" between). At any rate, such a file (with separate records for each county) is the best place to run a report about what the scoop is on each county...

It's a good idea, by the way, to name your databases after a specific kind of thing: Counties, work orders, employees, whatever -- each record will be one... [what?].

Feel free to attach a file, or describe your task more specifically...

[Mark Turner]

Espringer:

Thanks for your response.

The "SymbolKey" field is a shorthand for the botanical name for a native plant. It's the common link across several databases holding the source information for a book, and comes from the USDA PLANTS database at <http://plants.usda.gov>.

I know my file names look a little wierd, but I'm the only one who will ever use them and they're quite meaningful to me. Once I get this working with the WTU data, I need to replicate the process with at least three other source databases with similar, but not necessarily identical, structures.

The goal of this process is to have one record per plant in OR_WA_County_WTU that shows in which counties in WA or OR it can be found. The data will get exported, one record at a time, and run through a GIS system to generate one map per plant. The GIS program expects one field per county with a numerical value indicating whether to shade the area or leave it blank.

The WTUCountyWorking database came from an outside source and is structured with one record per instance per plant. If AQFO (Aquilegia formosa) is found in 33 counties, then there are 33 records for that plant. There may be more than one record with the same value in the "county" field because the records actually represent dried plant specimens in an herbarium collection. In the original file, there are a large number of additional fields that show things like who collected the plant, where it was found, habitat where found, date collected, etc. I've added the "SymbolKey" field to my working copy of this database so it will link with the other files I'm using. The original database is accessible on-line at <http://herbarium.botany.washington.edu/FMPro?-db=VP%20Inventory&-lay=web&-format=vpsearch.htm&-view>. My working copy is about 8 MB, too big to attach.

Hopefully this has clarified the nature of the task.

-- Mark Turner

OR_WA_County_WTU.zip

[ESpringer]

Gotcha. You have what I'd call a "species" database, and a "specimens" database. Of course I don't care what you call them, but your explanation makes the task much clearer.

OK, so as it's set up, your relational calc will only "peek" at the first encountered related record, and you'll get a 1 in only ONE county. Where what you want is a bunch of 1s to indicate which counties should be "lit up" on your map, right?

First, I'd suggest building the relation on a concatenated field, for simplicity. The "specimens database" should have a calc field which concatenates county and state, as in: State&"_"&County.

There may be a more elegant solution, but this is what occurs to me:

Set up a SELF-JOIN relation called "SameSpecies" or whatever among your specimen records, so that records on the same plant-symbol field share in this relation.

Then, you can make a VALUE LIST, based on everything shown in the "State_Count" concat field for this relation.

Then this value list can be harnessed in a calc field (I imagine calling it SameSpeciesAllCounties)

ValueListItems(Status(CurrentFileName),"SameSpecies")

Now, each of the specimen records will have a field showing ALL counties in which this specimen appears, return-delimited.

That makes it possible, based on just ONE species-symbol based relation, to peek at ANY related record for the plant, and see whether your county-name in question appears there. A PatternCount function should be able to do that... for good form, include

[Fenton]

I was going to go for a Looping script using Status(CurrentFieldName), but ESpringer's solution is better, more dynamic. I redid your file(s). I added a couple of dummy files for the "specimens" and "species" files (taking their names from the error dialog). I also renamed all the files, for safety.

I used Position instead of PatternCount, it's supposed to be faster. I didn't bother with the extra

zWTU.zip