defining relationships using multiple criteria

[_Pete]

I'm trying to define a lookup using two criteria, but "...when the same value exists in the match field in more than one record of the related file, FMP copies the value from only the first related record [ie based on the first criterion only?]." Our oh-so-useful manual goes on to say that "The first related record that's accessed depends on whether the related records are sorted.." Surely there's a way around this limitation? Or am I missing some obvious means of doing this?

Here's a more detailed description of what I'm trying to achieve and what (I think) is my problem:

I have two databases. The first consists of multiple records, each containing data on the abundance of a particular species of fish caught during a unique capture event (purse-seine set, if you're curious). Thus, each record from this database includes a field with a number assigned to that particular event (e.g. 487429), a field with a species code, and additional fields containing the abundance information--here, each species has its own record, but a unique capture event (and the number assigned to it) may appear in multiple records because these events often include several species (e.g. 487429 yielded yellowfin, mahi, and wahoo). The second database includes a single record for each unique capture event as well as fields containing abundance for all possible species (e.g. yellowfin, mahi, wahoo as well as skipjack and tripletail).

The problem: my abundance fields in the second database are ideally supposed to get their data from the first database. I've attempted to define these fields as calculations, first setting up a relationship between the two databases, matching the unique identifing number for these capture events, and then writing an equation something like

If(match setIDs::species=110,match setIDs::no_total,"0")

where 'setIDs' are the unique identifying numbers, '110' is a species code used in the first database and 'no_total' is the field containing the total number caught--theoretically of species 110. This works for the first species (110), but fails to find subsequent species (111, 121, 130, etc), as near as I can guess because FM goes only to the first record with the matching setID. If I re-sort these fields, I get the correct data for the species that now appear first (e.g. 130 if sorted from largest to smallest), but not for any of the other species. The first record (because of the sort order) provides the appropriate data for species 110, but no data for other species so FM returns '0' rather than searching all records with the matching setID for the correct species code.

I hope that this makes sense. I can easily send two small sample files that ought to illustrate the problem if anyone is so inclined.

Thanks in advance, Pete

[ESpringer]

It sounds like what you need is a portal rather than one calculated field.

Given that your capture event is already linked to the species-data file, you can put a portal layout in your capture-event database, and on each row place fields from the related species-info records. Do you know how to do this?

If you really want the information to be included as a *field* there are ways to do this, by defining a VALUE LIST with information from the other file, but I'm guessing you wouldn't need to do this... Please say so if you want to pursue that.

[Fenton]

I've seen this question about the fish somewhere else, and began writing an answer for it. You only mentioned 3 species there, so you'll have to add fields for additional ones.

The data is correct for what you've specified, though it is not what you wanted. You're basically getting just the data in the 1st matching records; ie. you're not summing it.

To sum it you need to "concatenate" (put together) the SetID with the Species. These are "hard-coded" values in the 2nd file, so you can get each value as a field (which is not a strictly relational structure, it seems to me).

So, in the 2nd file, I'd create a calculation field for each, unstored, text. I'll label them according to their text names.

"SetID_YFT" = SetID & " 110"

"SetID_SKJ" = SetID & " 111"

"SetID_BLM" = SetID & " 124"

In the first file it's just "SetID_Species":

SetID & " " & Species

Create a relationship between each unstored field in the 2nd file and the SetID_Species field in the 1st, named by their label.

Then use the Aggregate function Sum(field) to do the arithmetic. These are the three total fields you want.

"_cYFT" = Sum("SetID_YFT::no_of_fish")

"_cSKJ" = Sum("SetID_SKJ::no_of_fish")

"_cBLM" = Sum("SetID_BLM::no_of_fish")