Newbies ktolis Posted September 30, 2007 Newbies Share Posted September 30, 2007 (edited) 1st post so be gentle : OK here we go. I have 2 tables. a) Assets : Computers The concept is that all hardware peripherals and internal computer components end up in the table Assets. A Computer record has an id identifying the computer and multiple fields as a) cpu motherboard c) HD Drive d) RAM slot 1 e) RAM slot 2 f) Printer attached The Assets table has data like: a) type (like motherboard, ram, HD, GFX card etc) title (like "ASUS A8NSLI") c) serial (the serial of the specific part available in the inventory) All Computer fields are foreign keys from the Assets table. I tried making a layout form that would allow a user to add a new Computer record and displaying 6 drop down menus each fetching only the records from Assets of the specific type required but at no help. I am using value lists as drop down menus but I seem to not be able to constrict the values to a specific type. Building a form based on mysql would be quite simple using multiple queries (each for every foreign key) like: "select * from Assets where type = 'motherboard'" and using the data I need. I haven't managed in succeeding in this simple task on FMPA and it really frustrates me. :( Edited September 30, 2007 by Guest Link to comment Share on other sites More sharing options...
Ender Posted September 30, 2007 Share Posted September 30, 2007 Welcome, Each value list will need a different relationship to the Assets table, where each is filtered by a different criteria. A good way to do this is to use a bunch of globally stored text fields as the parent keys in Computer (gTypeCPU, gTypeMotherboard, etc.) where each relationship matches the global in the Computer table to the Type field in Asset. Then populate each respective global field with the right Type. The value lists will then use each different relationship, showing related values only. Link to comment Share on other sites More sharing options...
Newbies ktolis Posted October 1, 2007 Author Newbies Share Posted October 1, 2007 I was afraid of such an answer. This was the only possible modeling idea I could come up with but I was thinking if there is possibly another solution. If the categories are solidified then this is a viable solution. But if the categories aren't and they are typed as-we-work with the db then it's not a viable solution. I think this is a shortcoming from FM's side. Unless someone else has another possible solution. Link to comment Share on other sites More sharing options...
Newbies ktolis Posted October 3, 2007 Author Newbies Share Posted October 3, 2007 According to the reply i got here and the self-join help from FM's guide I got the following setup. -- The Peripheral Types Table consists of a list like: Processor, Motherboard, RAM, VGA Card, Sound Card etc etc.. -- The Peripheral Table consist of the list of peripheral devices that are available like: Western Digital ATA HD 80Gb, Western Digital ATA HD 120Gb, Western Digital ATA HD 160Gb, ViewSonic 17" TFT VGA Monitor, ViewSonic 17" TFT VGA Monitor , Pentium III, Pentium IV, Pentium M etc.. -- The Computers Table consists of multiple entries from the Peripherals table each should be from the correct type. e.g. a Monitor, or a Hard Disk or a Processor --- What I did was create another (global) field at the Computers table which holds the value of the relevant (and correct) Peripheral type id. e.g. gMotherboardType is 1, gCPUType is 2 etc.. The Computers table has a relations with the Peripherals table, one for each type of Peripheral as seen on the right hand. All tables as viewed on the image on the right are instances of the Peripherals table and the relation is: Computers Motherboards Motherboard x id AND gMotherboardType = Type I have also created another relation as a self-join as seen with the 2nd column of table instances on the left side which are all instances of the Computers table So I created value lists (one for every peripheral type). For example the Motherboard VL uses values from the Motherboards table (id and Title to be specific, using id and displaying Title only). It uses only related values from gMotherboardTypeRel table (the self-join of Computers table). I created then a Layout (L1) with drop down menus for each type on the computers table. If no records exis then when I create a new record displayed on all VL. If I create a record (by hand from another layout (L2) without drop down menus) and then go back to L1 then I get the correct VL I expected. Why don't I get values at the VL's in L1? How can I get them without having a prototype record in the start? Link to comment Share on other sites More sharing options...
Ender Posted October 4, 2007 Share Posted October 4, 2007 Looks about right on the right side. Those relationships can then be used for looking at the chosen component's details in the Peripherals table. But the value lists' relationships don't look right (you can't really use a relationship that has globals on both sides). It should something like: Computer <=> Motherboard_VL = Computer::gMotherboardType = Motherboard_VL::Type Computer <=> CPUType_VL = Computer::gCPUType = CPUTType_VL::Type etc. That way it shows ALL peripherals of the matching type in each value list. Once you get that working, you might also consider adding another criteria to each VL relationship to only show the Active peripherals of each type. This way you could keep the records of the old deactivated peripherals in the system, but only look at the active peripherals when you assign them to Computers. Link to comment Share on other sites More sharing options...
Ender Posted October 4, 2007 Share Posted October 4, 2007 Looks about right on the right side. Actually, if the ID in the Peripherals table is a unique ID, like an auto-enter serial number, you won't need the global to Type criteria in the relationships. You can simply go from the ID in the respective componentID field to the ID, like: Computer <=> Motherboard = Computer::MotherboardID = Motherboard::ID Computer <=> CPU = Computer::CPUID = CPU::ID etc. Link to comment Share on other sites More sharing options...
comment Posted October 4, 2007 Share Posted October 4, 2007 There is another way, using three tables: Computers AssetTypes Assets You relate Computers to AssetTypes with an x relationship, and AssetTypes to Assets as: AssetTypes::TypeID = Assets::TypeID AND AssetTypes::gComputerID = Assets::ComputerID The disadvantage of this you cannot just browse records in the Computers table - you need to match the gComputerID field in AssetTypes to the currently viewed computer. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6176 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now