Tough relationship?

[HendersonD]

I am having a hard time making a relationship between files that gives me just what I want in a portal window.

This is a simplified example of my solution but one that gets to the heart of the matter. I have three tables, Computers, Software, and SoftwareInstalled with the following fields in each:

Computers - compID, computer name

Software - softID, software name

SoftwareInstalled - compID, softID

The Computers table contains 800 records, one for each computer on campus, compID being a unique serial number for each machine.

The Software table contains 200 records, one for each piece of software we own, softID is a unique serial number for each piece of software

I have two portals in Computers, one that shows all the software that could be installed on the computer (Available Software) and one that shows the titles that are installed (Installed Software). I have a button made in the Available Software portal that when clicked assigns that piece of software to that computer. What it does via a script is make a new record in SoftwareInstalled and puts in this record the proper compID and softID. In essence the SoftwareInstalled table is a link between Computers and Software to break up a many-to-many relationship. Many computers can have the same piece of software installed and many pieces of software can be installed on a computer.

So far this works great, when I click this button the software title I clicked on appears in the portal labeled Installed Software. Here is the problem, as soon as the software title appears in the Installed Software portal, it should no longer be seen in the Available Software portal.

An example might help. One particular computer has no software installed on it so the

[Ender]

Hi Dave,

We worked this out a few months ago by using the ValueListItems function to list the values in the assigned list. Then using this in a <> relationship to exclude the used items.

You can see more discussion about this here:

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=128500

And I've also attached a sample that shows how it works.

It's important to note that this technique works best when a script is used to add the item, because the Refresh Window [ Flush cached join results ] step is used to update the lists.

UserGroups-2.fp7.zip