Portal and Conditional Value List Confusion?

[JRHobbs]

Here is my relationship diagram of the section of the database I am having trouble with.

Tables (and some relevent fields):

Software

• Software ID
• Software Manufacturer
• Software Title
• Software Version

Software Installation

• Software Installation ID
• Installation Date
• Various fields from Software table
• Building (from Computers table)
• Room (from Computers table)
• Computer Name (from Computers table)
• Computer ID (from Computers table)

Computers

• Computer ID
• Computer Name
• Building (from Location table)
• Room (from Location table)

Location (for Conditional Value Lists in Computers and another table)

• Building
• Room

Relationship:

Software ----< Software Installation >---- Computers

Portals:

• One in Software to detail each software installation for a particular software (i.e. a piece of software with 10 licences will have up to 10 installs).
• One in Computers to show all software installations (i.e. all software installed a one machine)

Software/Software Installation Portal

When I create a new record in the Software Installation portal via Software, I want to be able to drill down to select where the (Computer) installation occured. The fields from Software table auto enter just fine, so now I just need to add the association to the particular Computer (in the Computers table) via that portal:

1. select from a list of Building

2. then select from a list of Rooms in that Building

3. then from a list of Computer Names in that room

4. auto fill the associated Computer ID once Computer Name is selected.

Note: In the Computers table, location information, i.e. Building and Room are pulled from a table called Locations using Conditional Value Lists. When in Computers a person can select Building A, then only see rooms available in in Building A, etc.

Computers/Software Installed Portal

At the moment, my Computer/Software Installed Portal isn't working at all, but I assume once I get the links correct in the other one, it will. This will be a view records and (maybe) delete only.

I'm just completely baffled about how to get the Software/Software Installations Portal working. I got the Building and Room info in just by doing a values from Computers::Building and Computers:Room, etc, but of course they were all not associated to each other.

A gentle prod in the right direction would be lovely, though it may end up having to be a hefty wack...

[Søren Dyhr]

I think I would ditch the jointable to a mulitinekey placed in software, since I then can monitor the number of installations made and similar evaluate on the uniqueness of the assigned computer.

The crucial point here, is if there's going to be made statisitics on the join tables records ... it escapes me until now?

This is not giving me much of a clue:

I want to be able to drill down to select where the (Computer) installation occured.

To me is it just a portal in the either Computers or Software, showing the related from the other - regardless of having a genuine join table inbetween or not.

--sd

[JRHobbs]

Hi Søren, thanks for your response.

I think I would ditch the jointable to a mulitinekey placed in software, since I then can monitor the number of installations made and similar evaluate on the uniqueness of the assigned computer.

I thought main rule when establishing entities in relational databases is to fix all many-to-many relationships... Software >---< Computers is a many-to-many, hence the Software Installations table, which provides a useful mechanisim to track other data such as installation dates, etc. The FM book and online stuff I've read seem to imply that FM is the same as any other relational db and needs such relationships to be resolved, please correct me if that is wrong.

The crucial point here, is if there's going to be made statisitics on the join tables records ... it escapes me until now?

By that do you mean am I going to compile reports, or perform calculations on data held on Software Installations? Yes to both. Initially I'm hoping that I can count the number of installations in the portal for a software and auto complete the Number of Installations field on Software. I can't see the relevence of that at the moment, my problem is that I can't pull in the data in the manner I need from Computers to Software Installations.

This is not giving me much of a clue:

Oh ok, I thought the numbered list provided the clarification to that sentance, sorry! I meant drill down as a term to narrow results of a field depending on the selection of the previous field*. The person entering the data will do the following steps in order when adding installation information via the portal in Software:

1. select from a list of Building

2. then select from a list of Rooms in that building

3. then from a list of Computer Names in that room

4. auto fill the associated Computer ID once Computer Name is selected.

To me is it just a portal in the either Computers or Software, showing the related from the other - regardless of having a genuine join table inbetween or not.

Yes, with a couple of fields from Software Installation too (Installation Date, Installing Staff, etc).

* I already can do this for Building and Room fields in Computers by using Conditional Value Lists using the Locations table. But how to do it in Software Installations is baffling the hell out of me as I need to drill down further with info NOT in the Locations table. I'm thinking a script or calculation (not sure which, newbie here!) after the selection of Building and Rooms to say IF Software Installations::Room="XXXX", list all Computers records with Computers::Room="XXXX". Then when the inputter makes the selection, there is the join. Not sure if this is possible.

[Søren Dyhr]

The FM book and online stuff I've read seem to imply that FM is the same as any other relational db and needs such relationships to be resolved, please correct me if that is wrong.

Indeed, but it's still a many 2 many relation although the linking is done by a field in one of the tables - take a look here:

http://fmforums.com/forum/attachment.php?attid/10445/

I meant drill down as a term to narrow results of a field depending on the selection of the previous field*.

Well the above linked template does this, it's more if it does what you're after. You might similar get good inspiration by studying this template:

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

Both methods provide allocations that exhaust provisions!

--sd

[comment]

If you intend to do reporting/statistics on installations, then you DO need the join table. *

However, you don't need - and shouldn't have - duplicate information. The join table should have fields that are attributes of an installation, not of its parents. Same for the other tables:

Software

• Software ID

• Software Manufacturer

• Software Title

• Software Version

Software Installation

• Software Installation ID

• Software ID (foreign key to Software table)

• Computer ID (foreign key to Computers table)

• Installation Date

Computers

• Computer ID

• Computer Name

• Room (foreign key to Rooms table)

Rooms (for Conditional Value Lists in Computers and another table)

• Building

• Room

This is the basic structure. You may need to add some auxiliary fields to help in narrowing down a conditional value list - but these are not part of your core data model.

---

(*) Actually, even without reporting, etc. - if you want to record the installation date (or any other detail regarding the installation), then you MUST have a table of Installations.

Edited July 23, 2008 by Guest