July 22, 200817 yr 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...
July 22, 200817 yr 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
July 23, 200817 yr Author 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.
July 23, 200817 yr 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
July 23, 200817 yr 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, 200817 yr by Guest
Create an account or sign in to comment