problem Join Table Multi-Criteria Relationship

[kmerritt]

I am having a problem with a multi-criteria relationship I am trying to create from Table A to Table AB (Join Table to :. I'll try to explain it clearly.

Table A contains the list of Products

Table B contains the list of Reps.

One rep can represent many products, thus Table AB joins the Reps (: to the Companies (A)....a standard join. The only two 'input' fields are 1) a field with the Table A Primary ID and 2) a field with Table B primary ID. Every other field is a calculation field that grabs info from Table B through the Table AB - Table B relationship.

OK. Here is my problem. I want to create a multi-criteria relationship between Table A and Table AB where one of the match fields in Table AB is a calculation field (grabbed from Table :. Therefore, the relationship would look like this:

WHERE product_key (from Table A) = product_link (from Table AB)

AND product_type (from Table A) = product_type (calculated field in Table AB that references Table

I can't seem to get this to work. For some reason, because the product type in Table AB is a calculated field it won't match to the Table A product_type. If I try to do the multi-criteria match with a non-calculation field from the Join Table (Table AB), everything works fine.

Am I missing something. If this just won't work, can someone explain why so that I can understand why I shouldn't try this in the future.

Thanks for your help!

-Kevin

[smorr]

Because TableAB::Product_Type is a calculated field that references a value from another relationship, it cannot be indexed. Consequently it cannot be used as a predicate in the relationship (or more precisely the relationship will be in one direction (and not the direction you want).

You can do three things here.

1) make AB::Product_Type a lookup field

2) reconsider your relational structure

3) reconsider what you are trying to do.

I am trying to understand better what your intent is here and I hang on the following line:

WHERE product_key (from Table A) = product_link (from Table AB)

AND product_type (from Table A) = product_type (calculated field in Table AB that references Table :

I don't see how adding the predicate of product_type on this relationship adds anything to this relationship. If you are matching on product_key isn't a match on product_type is implicit. Wouldn't trying match on product_type be either redundant (because it is implicit) or contradictory (you provide a type that won't match)?

Perhaps one way to look at this is more than one relationship in accordance to what you want to do.

so have one TO where TableA::Product_Key = TableAB::Product_Link

have another where TableA::Product_Type = Table B::Product_Type that you use for different reasons.

Again-- it is important to understand what you are trying to do -- ie what type of information are you trying to get here?

[RalphL]

Why don't you make this relationship directly with A?

In 7 you shoundn't need all those calculated fields in the join table.

[Fenton]

I agree with smorr. Once you have the ProductID, the Type is implicit. Now, if it was an Or match, that would be different; but wouldn't necessarily make a lot of sense, saying, "I want this product (no matter what Type) or any product of this Type."

What, in plain English, are you trying to find?

[kmerritt]

Your right this doesn't make sense! I think I made a mistake in my original explanation. Let me try to be more clear. Basically, a rep can be linked to numerous products. Therefore, it should have been switched (Table A = Reps & Table B = Products)

I should have written it like this to be more clear:

WHERE rep_key (from Table A) = rep_key_from_product_to_rep_join (from Table AB)

AND global_product_type ('pens' in this case from Table A) = product_type (calculated field in Join Table AB that references Table :

Does this make more sense?

The reason I came up with this product type multi-criteria match is as a way to "filter" a portal. In the portal I only want to see products that match a certain type. For example, if I relate a rep to certain stationary products, I then want to create a portal that can filter out only the 'pens' that the rep carries and thus filter out all the products that don't match the type 'pen'.

I would love to find a way to do the solution without a join file, but I am not sure how to do a many-to-many without a join file. Maybe I m still thinking in an FM6 way, but I can't see how to to not use the calculations.

The lookup would work, but the problem is with the products specifics changing so much, is there an easy way to make sure the lookup gets re-looked-up?

Thanks for the help.

-Kevin

[smorr]

I think I am a little clearer on what you are trying so let me give it a whirl

First there are two key ideas:

1. you will need to have a global Field for specifyign the product type to view in the Join table -- but if you use it in a relationship, the relationship is unidirectional so it is important to consider the direction that the request is going. In this case the direction will be from reps to products -- if you want the other way you need to set up a second relational path.

2. [Very Important] A portal on one Layout(TableOccurance) can be to a TO anywhere on the relational path (ie the portal doesn't have to be to an adjacent TO.)

See If I get this right:

Step 1: create relationship:

TableB(Reps) <-> TableAB (Join) on RepID= RepID

Step 2:

Create a global in TableAB: gSelectedProductType

Step 3:

Create relationship

TableAB:: gSelectedProductType = TableA::ProductType (single direction)

AND TableAB:: ProductID = TableA::ProductID

(this is a single direction relationship)

Step 4: On a layout attached to TableB, create a portal that pulls records from TableA ( 2 TOs away on the relational path) and add the field TableAB::gSelectedProductType so that you can specify which products

Logically what is happening here is that the first relationship will select all the records in the join table that match the reps (ie all the products for a rep) then the second relationship will select all products that match the selected join records and further match the specified product type.

I've included my file so you can see.

relationalproblem.zip

[kmerritt]

Thanks!!!

That worked perfectly. Thanks especially for the attachment. Once I looked at that I understood the process completely.

-Kevin