Jump to content
Salesforce and other SMB Solutions are coming soon. ×

In many-to-many relationship, show records in portal NOT included in join table


TonyFMCafe
 Share

Recommended Posts

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.)

 

Any ideas? (I know this must be simple.)

Link to comment
Share on other sites

You just need a new table occurrence of Products, call it "AllProducts". Create a constant field "One", defined as 1, in the Products table and again in the Presentations table, and make a relationship to AllProducts using One::One.

 

Then you can have a portal to every product in your db alongside of your portal to only the products that are already in the presentation.

 

NOTE: If all you need is a value list of all products, it's easier to just create a value list. A value list can display an additional field to provide more info than the field selected for the value list; if you need more than two fields' worth of info, you can always create a calc field in Products that joins multiple fields together (Product Name & " " & Product Category & " "&Product Description)

Link to comment
Share on other sites

Thank you for the reply, but I don't think I was clear.

 

I would like a portal that shows me ONLY the products that have NOT been included in the presentation, not all products.

 

It's easy to create a portal that shows me what IS included in the presentation, or all products, but I can't figure out how to create a portal that shows me only those products that are NOT included in the presentation.

 

Any ideas?

Link to comment
Share on other sites

Easy enough as a verb (Go to Related, show; Show Omitted Only), but how to do it as a noun?

 

Hmm, let me think on that for a minute.

 

 

EDIT: Sometimes once you can think of an awful, klunky, inelegant way of doing something, it helps you figure out a method suitable for deployment. OK, we can snag the list of all products and define them into a calc field, ValueListItems (Get(filename), "AllProducts"), and we can similarly snag the list of all RELATED products. So one awful, klunky, inelegant approach would be a huge nest of Substitute functions:

 

substituting "" for every occurrence of Middle("¶"&RelatedProductsVL&"¶", Position("¶"&RelatedProductsVL&"¶", "¶", 1, 1)+1, Position("¶"&RelatedProductsVL&"¶", "¶", 1, 2)-Position("¶"&RelatedProductsVL&"¶", "¶", 1, 1)-1);

 

substituting "" for every occurrence of Middle("¶"&RelatedProductsVL&"¶", Position("¶"&RelatedProductsVL&"¶", "¶", 1, 2)+1, Position("¶"&RelatedProductsVL&"¶", "¶", 1, 3)-Position("¶"&RelatedProductsVL&"¶", "¶", 1, 2)-1);

 

...and so forth. Question is, how to get that kind of recursion in a noun (field def) without hardwiring (writing out) every permutation until covered enough potential lines' worth to deal with any quantity of products you'd ever expect to have connected to any presentation?

 

I am, for the moment, stumped. (You can't just run Substitute and substitute "' for the entirety of RelatedProductsVL within AllProductsVL -- for the same reason a literal Find for "==Oranges Peaches Cherries" won't bring up a record containing "Oranges Cherries"; the substitution won't occur unless the entire literal string of the related value list occurs without interruption within the larger body of the entire AllProductsVL. And of course it wouldn't, the related ones would be interspersed with those that aren't selected yet).

 

Now, if you could run a script and go obtain the results on command and then dump them (into a global field on the layout, le'ts say), that's easy enough, as I said above. Go to Related Records [Products, Show Related only], Show Omitted Only, Go to Record [First], Loop, Set Variable $UnChosenProducts [$UnChosenProducts&Left("¶", Length($UnChosenProducts))&Products::Product Name], Go to Record [Exit after Last, Next], Go to Layout [original layout], Set Field [AnyTable::G.GlobalTextField, $UnChosenProducts]

 

Problem with that is, as soon as another product is chosen for the presentation, the global field becomes wrong, you have to run the script again to make its contents "catch up" and remove the product you just added. AND it's just there as a visual guide, you can't readily use it as a value list....well yes you can, instead of a global you can dump $UnChosenProducts into a LOCAL text field and then have a selfjoin relationship of the record to itself (RecID to RecID where RecID is calc numerical Get(RecordID) works quite well for this trick) and a ValueList of related values of that local text field, apply that to a field for selecting yet more products and you're all set. Except, as I said, for the problem that it doesn't update as you go, you have to keep re-running the bloody script after each selection.

Link to comment
Share on other sites

Hi Tony.

 

As Soren suggested in the other forum, this is a good place to use a ValueListItems trick. The technique uses the value list items as a not equal filter for the relationship showing the items NOT in your selected list.

 

Attached is a demo illustrating how it works. This is from this thread where we worked it out:

 

http://fmforums.com/forum/showtopic.php?tid/128500/

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use