Please need help with a model i'm working at for my class group.
I've been trying to get my head around this issue for weeks but to no avail. So finally decided to seek help here. There's a project we are working on where there are certain Jobs ( A, B, C, D ... ). Each project has a release ( kind of like sub units of the projects which are divided for easier management , so release 1, 2, 3, 4 and so on, some releases have the same number but belong different projects, lets say Project A and B both have release 1 and 2 ) . ANd further down the tree, each release has it own Categories like Desktop, Appliances, Games etc etc. And each category has its own parts. Te part numbers can be the same for each category belonging to each release and project ( but the construction of each part is different , so a part called ABC in release 1 , is different from a part called ABC in release 2 ) . None of the parts are same even though they might have the same part number, they're only differentiated by which project and release they belong to.
So i have three tables, Parts, Line items and Production sheet. Data is entered through the production sheet layout with a portal to line items, its pretty much like a sale invoice type model. THe problem im having is, in the portal section to the line items, i've set it up so that the person entering the data would have to choose the Project first ( from the project field), then the associated releases for that projects (release field), and then the associated category ( category, such as desktop, stationary, appliance ), and then only the parts from that category ( in the part field) and finally a manual entry for amount produced. The project , release and category fields are all present in the part table , with lookup fields for them in the lines table.
I've looked around on how to dynamically updat portals and tried several things, such as duplicating the parts table and connecting or linking the project field from the duplicated table to the lines table and so on. It does work somewhat when i select Projects, release and category it goes fine, but when i get to the parts field in the portal, it shows alllll the parts related to the category that is selected in the category field, and by all i mean all other parts from other releases too for THAT category, buut not the other projects. Example i select Project A, then select Release 1, Then select Category Desktop and then in the 'part' field drop down all the parts of desktops that show up in Release 1,2 and 3 of Project A show up in that list. I can't figure out how to filter it to the prior selected project > release> category only. :( Any help would be appreciated, have to hand this in soon . Thanks in advance.