Please help - portal problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David - 30 Mar 2004 00:39 GMT

I worked many hours on this seemingly easy problem with no success

I have a classic many to many relationship that I solved with a join
file. A Computer file has fields compID and compName. Software has
softID and softName. The same computer can have many software titles
installed and a piece of software can be installed on multiple
different computers.

I created a file called join that contains just two fields, compID and
softID. Each record is the ID of a particular computer and then the
softID of a piece of software installed on it.

From the Computer file I create a relationship to join and use a
portal to show all the pieces of software installed on this machine.
Likewise for the Software file in a portal I can display all the
computers that this piece is installed on. All of this is working
fine.

I want a portal in my computer file that displays all the software
titles EXCLUDING the ones that are already installed. In other words
the titles that are available for installation. Likewise, in Software
I want a portal that displays all the computers EXCLUDING the
computers that this piece is already installed on.

I can easily get a portal in Computers to display all the software
titles. I use a calculated match key that excludes software that I
have a site license for and software that is the wrong platform (cross
platform district). The realtionship is directly from Computers to
Software.

I am baffled on how to exclude software that is in the join file for
this computer. In other words, software that is already installed.

Thanks
Dave

Reply to this Message

42 - 30 Mar 2004 01:16 GMT

>I worked many hours on this seemingly easy problem with no success
>
[quoted text clipped - 31 lines]
>Thanks
>Dave

The problem is that you are trying to use a *relationship* to view
data that is by your definition... *unrelated*.

For you to view the data in a portal you would literally need a record
in your join file for each possible computer-software pair with a
'installed' or 'not installed' status field.

Then it becomes a trivial matter of defining a relationship from your
computer to 'not installed' software.

Naturally that is a pretty clumsy implementation requiring a ton of
records.

If you just need the found set and don't need it in a portal you can
simply perform a find in software for the related computer. And then
invert the found set ('show omitted').

If you absolutely must have it in a portal, you are going to have to
make some sort of relationship between computers and *not installed*
software.

Reply to this Message

Howard Schlossberg - 30 Mar 2004 01:28 GMT

And by the way, your problem becomes much easier in FMP7, where you can
define a value list to capture the related IDs from the join file, and
then use a 'not equal' relationship for the portal from the third file.
The mising piece of this in FMP6 is that 6 relationships may only be
equal.

>>I worked many hours on this seemingly easy problem with no success
>>
[quoted text clipped - 53 lines]
> make some sort of relationship between computers and *not installed*
> software.

Signature

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California
Associate Member, FileMaker Solutions Alliance

Reply to this Message

42 - 30 Mar 2004 02:10 GMT

>And by the way, your problem becomes much easier in FMP7, where you can
>define a value list to capture the related IDs from the join file, and
>then use a 'not equal' relationship for the portal from the third file.
> The mising piece of this in FMP6 is that 6 relationships may only be
>equal.

Hmmm....I'm not quite seeing this.

Are you suggesting one assign the contents of the value list to a
field, and use it as a multiline key in a 'not equal' relationship?

I think that would work... although I have have reservations with
using unconstrained multiline keys.

Reply to this Message

Howard Schlossberg - 30 Mar 2004 02:36 GMT

Exactly. Set an unstored calc to the valuelistitems and then use that
as a relationship key. I'm not sure why you have reservations about it.
It would have worked perfectly in 6 had there been the 'not equal'
relationship. It needs a little work in 7 because of some current
'issues' that prevent portals from updating properly.

>>And by the way, your problem becomes much easier in FMP7, where you can
>>define a value list to capture the related IDs from the join file, and
[quoted text clipped - 9 lines]
> I think that would work... although I have have reservations with
> using unconstrained multiline keys.

Signature

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California
Associate Member, FileMaker Solutions Alliance

Reply to this Message

David - 30 Mar 2004 16:17 GMT

I feel better that at least I was not missing some easy way to solve
this. I actually have this working properly using a multiline key. I
can display available software in a left portal and installed software
in a right portal. I have a script that when a piece of software is
clicked it moves from portal to portal, either installing or
uninstalling it. The problem is the updating a multiline key is clunky
(lots of code to insert a key with the proper returns, etc) and also
slow. In other words, I have it working now as a many to many
relationship with no join file. A join file seemed cleaner but showing
available software is the challenge. It sounds like I have three
choices:

1. Leave my solution that uses multiline keys and live with the
slowness. The slowness is really only on the portals in Software. The
availble computer portal can run to 400 computers and is slow. In
Computers, the available software portal only runs to 30 or 40 titles
and is more responsive.

2. Keep going down the route of a join file but the join file will get
very large. In the join file will be a record for every computer and
every piece of software that could be installed on that computer. A
third field in the join file would keep track of whether the software
was installed or not. I did a quick calculation and my join file would
have roughly 33,000 records.

3. Upgrade to FMP7 and take advantage of the new capabilities it has.

I have two questions. Can I still serve FMP7 databases using FM server
5.5?
Any additional thoughts on the best route to go?

Thanks
Dave

Reply to this Message