In many-to-many relationship, how to view items in a portal that are NOT included

[TonyInSeattle]

Let's say I have two tables: PRODUCTS and PRESENTATIONS, each with a unique ID key. Every product can be part of more than one presentation, and every presentation can include more than one product. So I create a join table titled PRODPRESJOIN. Each record in the join table contains 1 ProdID and 1 PresID, which are matched to their prospective tables. (See the attached example.)

So, if I'm on a layout based on the PRESENTATIONS page, it's easy to create a portal that shows me all the products that are included in the presentation. But how do I create a portal that shows me all of the products that are NOT included in this presentation?

In the attached example, the portal labeled "excluded products" does not work. Any ideas? (I know this must be simple.)

Example.zip

[Søren Dyhr]

I usually make a valuelist over all the included, and use:

"~¶" & ValueListItems(Get(FileName);"theList")

As multilinekey for the non-equijoin... just remember that there are some freshing problems eventhough it should reside in an unstored field. I usually put the calc in a Set Field[ scriptstep!

Another way of doing it is:

http://www.nightwing.com.au/FileMaker/demos7/demo703.html

--sd

[TonyInSeattle]

I don't think a value list will work in my case because there will be too many products. I'm actually planning on filter the unincluded list of products even further in the portal (based on other product characteristics) so the user won't have to sort through too many products.

So, is there a way to do this in the portal? Seems so simple, yet the solution eludes me! :/-)

[Søren Dyhr]

I don't think a value list will work in my case because there will be too many products.

Isn't the limit in the 2G area?? I's not like the recursions limit of 40K. I'm not suggesting to make it visible via a popup or such.

At least will the multiline key hold 2G, so setting the key could perhaps be sequenzed set field steps, but I wouldn't expect it beeing a requirement at all.

--sd

[TonyInSeattle]

(I've already added a post similar to this in a different forum and think it would be more appropriate here.)

Let's say I have two tables: PRODUCTS and PRESENTATIONS, each with a unique ID key. Every product can be part of more than one presentation, and every presentation can include more than one product. So I create a join table titled PRODPRESJOIN. Each record in the join table contains 1 ProdID and 1 PresID, which are matched to their respective tables. (See the attached example.)

So, if I'm on a layout based on the PRESENTATIONS page, it's easy to create a portal that shows me all the products that are included in the presentation. But how do I create a portal that shows me all of the products that are NOT included in this presentation?

(I want to use such a portal to show the products that have not yet been added to the presentation so the user can click which ones he/she wants to add.)

In the attached example, the portal labeled "excluded products" does not work. Any ideas? (I know this must be simple.)

Example.zip

[TonyInSeattle]

Thank you for your continued help, but I think your solution is way over my head! (I think I need to change my skill level to beginner!) -)

Is there some way you can show me more clearly what you mean? If it's easy enough (and you have the time), could you modify my example file so that the "excluded" portal works?

[Lee Smith]

I merged your two topics. Please do not [color:red]double post.

I sent you an Private Topic about this.

Lee

[Ender]

Asked and answered here:

http://www.filemakertoday.com/com/showthread.php?p=40581