two match fields

[rocktap]

Is it possible to create a relationship that requires two fields to match (in my case 'amount' and 'date', so that a third field's data can be looked up. I tried a calculation (if, test, result 1, result2), where the test was to match amount and date in each respective DB but no result would come up.

[CobaltSky]

Hi rocktap,

It sure is. The way to do it is to create a calculation within each file which concatenates the respective fields. Ie with a formula along the lines of:

AmountField & "-" & DateField

...then create a new relationship which matches the calculating fields to each other.

Then the match - via the new relationship - will only occur between records where both the amount and date are the same.

[danjacoby]

Except it ain't quite that simple.

If you are creating related records in a portal, you need a calc field in the main file, and a key field in the related file that will enter the result of the calculation (that field should be a text or number field, or whatever, depending on what the calc field in the main file returns -- in your case, I recommend a text field).

If you want to split the data into two separate fields in the related file, you'll need two calc fields, each returning the related field in the main file (which means you have to set up an identical relationship from the related file to the main file).

BTW, I recommend that your original calc field read:

NumToText(AmountField) & " - " & DateToText(DateField)

[CobaltSky]

Sorry Dan, but if you read the original post, it *is* that simple.

Rocktap specifically says what the relationship is for: "...so that a third field's data can be looked up...".

The solution you're suggesting is three times as versatile - and three times as complex - as what Rocktap has indicated is required.

[danjacoby]

Yes, well, that's what happens when the board automatically jumps past stuff I've already read -- even if I've forgotten just exactly what it was I did read (like, maybe a couple of days ago).

I've complained about this "feature", but seemed to be overruled by several others who like it. I now consider myself vindicated on that point at least.