Display complement of matched records in portal?

[MuonDude]

I have Cases and Products (many-to-many); so I

have a join table:

CaseID ----< [CaseID|ProductID] >----- ProductID

In the Case Table I have a field: zg_CaseID (global filter), set to a current Case ID. On a layout

tied to the CASE TO I can display a portal with

the related Products (from the Case_CPJoin_Product TO).

What I'm struggle with is that I want to show those Products NOT related to the current Case (thus the user can select from this

list without seeing the products already assigned to the current case, avoiding duplicate assignments).

See attached picture (yes I know the TO name is messed up!).

TIA

[John Mark Osborne]

I put together a demonstration of what you want using an example I have of an invoicing solution. There is a many-to-many relationship from invoices to products. Look at the popup menu in the portal on the invoice layout to see that it shows only products not included on the current invoice. You could easily use the relationship for a portal as well.

The basic idea is to gather all the keys from the join table for the current parent using a calculation. In this example, all the products from the current invoice. These values are placed in a return-separated list using the List function and employed in a relationship as a multi-key. The operator on the multi-key is the NOT equals sign.

I used the new 8.5 List function to make the process as easy as possible. If you don't have FileMaker 8.5 you could use a recursive custom function containing GetNthRecord. However, this complicates matters when you don't need it and is far slower than the List function. So, I provided another example in the download demonstrating how to do this with the ValueListItems function, making it compatible with FileMaker Pro 7, 8 and 8.5. It's also very fast! It's drawback is value lists eliminate duplicates. With a many-to-many relationship, there won't be duplicates in the join table but even if there were, it still wouldn't fail. If you did need duplicates in your list (for another solution), you would need use a recursive custom function or the List function. For more information on the recursive custom function (if you don't have FileMaker 8.5 and need duplicates), search in the Resources area at the databasepros.com web site for "GetNthRecord".

NotRelated.zip

[MuonDude]

I put together a demonstration of what you want using an example I have of an invoicing solution. There is a many-to-many relationship from invoices to products. Look at the popup menu in the portal on the invoice layout to see that it shows only products not included on the current invoice. You could easily use the relationship for a portal as well.

...

Thanks very much!

I've printed your reply and downloaded the files so that I can study and understand what you did.

regards,

-- Sam

[MuonDude]

I put together a demonstration of what you want using an example I have of an invoicing solution. There is a many-to-many relationship from invoices to products. Look at the popup menu in the portal on the invoice layout to see that it shows only products not included on the current invoice. You could easily use the relationship for a portal as well.

...

I looked at your files, but it doesn't seem to work. On the Invoice layout if I go to a new portal row and add another product, the popup menu doesn't update. I still see product id 1 (widget) in the menu after it is added to the current invoice.

I attached a screen shot.

I am using FM 8.5 Advanced.

I understand the general approach of gathering the product id's from the join table for the current invoice and using those with a not equal to relationship. It seems like the NotRelated Field isn't updating when a new portal row is added.

Did I miss something?

[MuonDude]

I think I figured this out. JMO's solution was helpful in getting my thinking straight. For some reason his solution (and mine) don't work when entering new join records "by hand" or via the portal itself.

The example I attached below shows two portals on a Case record, one for the assigned Products and one for the unassigned Products. The unassigned Products portal has a "+" button which creates the Join table records and seems to correctly trigger the filtering (notice that the script has a commit records step).

feel free to use/modify, but don't blame me.

If some else has deeper insight why JMO's approach doesn't refresh I'd love to hear it.

Anyway, thanks to JMO I got pointed in the right direction! I really appreciate it.

regards,

Case-Product-Test.zip

[John Mark Osborne]

There is a relationship refresh issue that is easily corrected with the Refresh Window script step using the option to flush cached join results. Attach a button to the field with the popup menu using the steps in the attached screen shot.