Finding Correct Record From Relationship

[stefwef]

I have two tables A and B with the following fields in each:

TABLE A

Record ID A (auto enter serial number)

type key 1 (a global text value)

type key 2 (a global text value)

TABLE B:

ID tag (created from relationship, =Record ID A)

type (created from relationship, so always =type key 1 or type key 2)

date (entered manually)

And the following two relationships:

RELATIONSHIP 1 between A and B:

Record ID A=ID tag

type=type key 1

RELATIONSHIP 2 between A and B:

Record ID A=ID tag

type=type key 2

I placed 2 portals on table A layout: one for relationship 1 holding date from relationship 1 and one for relationship 2 holding date from relationship 2. Both relationships allow creation of record in TABLE B, so ID tag and type are automatically set to match TABLE A's record ID and the type 1 or 2 depending on portal 1 or 2 whenever a date is entered from the table A layout.

THE PROBLEM: when in TABLE A, I do a search for a specific date in portal of relationship 1, and I get results that match both relationship 1 and relationship 2.

To give an example:

IF TABLE B has the following two records:

ID tag = 234

type = "1"

date = 4/5/2008

AND

ID tag = 235

type = "2"

date = 4/9/2008

If I'm in TABLE A in FIND mode and search for year 2008 in date/portal for relationship 1, I get both #234 and #235 when in fact I should only get #234.

If Table B also holds the following record:

ID tag = 235

type = "2"

date = 4/9/2007

Then that means I get a "result" that in effect shows 4/9/07 as a result to my 2008 search - you see the problem.

Is this just the way relationships work or am I missing something?

If I do a calc in TABLE A that = date from relationship 1, then a FIND of that calc returns correct record (just 234). Why is the relationship not acting the same way?

I would love to avoid creating calcs and scripts that catch find mode to go to the calc fields because I am using these relationships all over the place. Any direction most welcome/appreciated! Thanks!

[bcooney]

I realize that I'm avoiding your question about why a find in a portal doesn't filter--however, I would find in Table B, then go to related matching found set back to Table A. I wouldn't find directly in the portal.

[stefwef]

Thanks, but unfortunately that's not an option. Users need to enter the dates in table A and want to be able to find through the portal in Table A...

The only thing they do in table B is view all sorted dates (of a subset or of all table A records.)

Because entry happens in table A layout, I can't think of another way to have the relationship (meaning date really needs to be in table B and brought in to A layout). I've actually got more like 40 types of dates to sort through - not just the 2 relationships I used to simplify my posting.

For those that are just a one line portal I can create an autoenter calc and place it behind the field for find mode entry. For those that are more than one line I guess there's no real solution...

It could be fmp does not allow for this type of functionality. Seems like a crazy big thing for them to not implement - it significantly defeats the purpose of relationships I've created.

Edited February 1, 2009 by Guest

[bruceR]

Is this just the way relationships work or am I missing something?

Yes, that is the way relations work. Follow BCooney's advice.

[bcooney]

When you find yourself building convoluted interfaces, you should step back and think it through again. There's usually a better way.