1 2 Previous Next 27 Replies Latest reply on Oct 23, 2013 8:23 AM by AliSheikh

    Please need help with a model i'm working at for my class group.

    AliSheikh

      Title

      Please need help with a model i'm working at for my class group.

      Post

           Hi guys, 

                    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. 

        • 1. Re: Please need help with a model i'm working at for my class group.
          philmodjunk

               For starters, it seems like you need a fourth table, Projects so that you can link set up relationships that link to projects and so that your drop down list of projects has a value source table from which to draw values. And then a fifth table to manage the releases for each project.

               Your first paragraph documents an extremely confusing situation. Just from a project management situation--without getting into database design, it sounds like you risk frequent and major cases of miscommunication!

               And what do you mean by: "and then only the parts from that category ( in the part field)"?

               Is this a conditional value list? Is this what is not working for you?

               Do you have fields in the Production Sheet table that identify the project, release and category?

          • 2. Re: Please need help with a model i'm working at for my class group.
            AliSheikh

                 Thanks for answering. Sorry i was typing in a bit of hurry and didn't read through my post properly before posting it. 

                 What i mean by " only parts from that category" , is that on the Production sheet when i select  the desired 'Project', followed by ' Release', then 'Category', and finally the 'Part".... i want the drop down list in the part field to only show those parts that belong in the previously selected project > release > category. My problem is whenever i select the parts, it shows all the parts of the selected category for ALL  the releases in the selected project.  It won't filter based on the selected Release. 

                 And yes i'm trying to work with conditional value lists i guess so that i can select a particular part based on prior selections in the portal, along with a button at the end of the portal that would take me to the part record. 

                 And no , on the production sheet all i have is the production id, date created. Project release and category are only in the parts table and the Lines table as a lookup field. 

                  

                 Thanks for replying , i will try the additional tables and see if that helps. If you have any more advice please let me know :) 

            • 3. Re: Please need help with a model i'm working at for my class group.
              AliSheikh

                   Here's a screenshot , to give a better idea :

                    

              http://imageshack.us/photo/my-images/62/hv8n.jpg/

                    

                   that layout is based on the the production sheet table with the line items in the portal that fetch data from the parts table.

                   The drop down under 'part' shows items from all the releases in the project EAP under the category 'mullions' , it doesn't confine it to the mullions from release 14. 

                    

                   I hope this gives a clearer picture. 

              • 4. Re: Please need help with a model i'm working at for my class group.
                philmodjunk

                     What you describe is not only a conditional value list, but potentially a hierarchical conditional value list. The selection you make for Project can limit the choices available for Releases and that, in turn, can limit the options for Categories. Once you have selected all of that, a conditional value list can list items from Production sheet only if they are correct for that project, release, and category.

                     Here are some links on the subject:

                     There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

                     The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

                     Forum Tutorial: Custom Value List?

                     Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                     Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                     Hierarchical Conditional Value lists: Conditional Value List Question

                     Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                • 5. Re: Please need help with a model i'm working at for my class group.
                  AliSheikh

                       Thanks a lot! i'll go through them tonight and follow up . yes

                  • 6. Re: Please need help with a model i'm working at for my class group.
                    AliSheikh

                         (green text denote tables, red -fields, and blue - layouts)

                          

                         Ok so i got somewhat of a grasp and i made the following tables:

                         Projects ----< Release ----< Parts -----< Line items >----- Invoice    (the lines between them denote one to many relation)

                         Each table down the chain has a foreign key to the previous table. So what i'm trying to do now is to somehow pull up info in the portal to line items which is created in the invoice layout. 

                          

                         I have a projects, and release text field in the line items, and also a parts_fk foreign key field which is linked to the part_id in the parts table.

                          

                         I tried to duplicate the projects and release ( project2 and release2) tables and relate them to the project text &  release text field , respectively, in the lines table. But i can't figure out how to pull up values from the portal in the invoice layout. 

                         In the line items portal ( on the invoice layout ), i use the project value list from the projects2 table using all values and shows up fine. But when i tab to the next field in the portal, which is the release field, i can't figure out what to do here. Nothing shows up. I've tried different settings in the value list setup but nothing really helps. What am i doing wrong? 

                          

                         I just want to be able to select categorically the items in the portal and even be able to add new items if they're not on the list . :( 

                          

                          

                          

                    • 7. Re: Please need help with a model i'm working at for my class group.
                      philmodjunk

                           I can see two different ways to do this depending on the answer to this question:

                           Can a single invoice list line items linked to more than one project or will an invoice only list parts for only one project?

                      • 8. Re: Please need help with a model i'm working at for my class group.
                        AliSheikh

                             for this part i want it to be able to list line items linked to more than one project. 

                             But i'd also like to the the solution for the second option you mentioned , list parts for only one project. That just sprung an idea in my head to try a different layout later. 

                              

                             Thanks Phil

                        • 9. Re: Please need help with a model i'm working at for my class group.
                          philmodjunk

                               Ok, but one solution at a time. Let's focus first on just limiting the list of parts to a selected project, we can then extend the method to restrict the list in more ways once you have that first step working and it should illustrate the concepts used.

                               Make a new occurrences of Projects, Release, and parts. Link them like this:

                               Line items>-----Projects|selectedProject ----< Release|SelectedProject ----< Parts|SelectedProject

                               Line Items::_fkProjectID = Projects|selectedProject::__pkProjectID

                               The links from Projects to Release to Parts in the above relationships use the same match fields that you have now.

                               Define a value list to use with LineItems::_fkPartID selecting the "use values from a field" option.

                               List your values from Parts|SelectedProject and select the "Include only related values starting from Line Items" option to limit the parts shown to just those parts linked to the selected project.

                               Once that is working, review the link I shared earlier for hierarchical conditional value list and if you still have questions let me know before we discuss how you might set that up so that you could select a project, then select a Release for the list of releases that exist for that project and then select a part from the list of parts listed for that release.

                               Note: Setting this up to select all parts from the same project uses almost the same setup, but you link this chain of new occurrences to the Invoices table occurrence instead of Line Items. That approach can save the user time during data entry as they only have to select that project once instead of on every line item.

                                

                          • 10. Re: Please need help with a model i'm working at for my class group.
                            AliSheikh

                                 is this how it should look like? 

                                  

                                 I made a value list too as below

                                  

                                 I tried to use the value list  for the project_fk field ( in the lines portal on the invoice layout) which seems to work if i pull it from the selected_project table . But i'm confused when it comes to selecting the release. I did go through the conditional value thread you pasted but it confused me a little on how to setup value lists for each field that i need to populate in the lines record ( in the invoice layout's portal ). Appreciate your help Phil. 

                            • 11. Re: Please need help with a model i'm working at for my class group.
                              philmodjunk

                                   To repeat:

                                   

                              Let's focus first on just limiting the list of parts to a selected project, we can then extend the method to restrict the list in more ways once you have that first step working and it should illustrate the concepts used.

                                   The current setup allows you to select a project and then your list of part numbers should list all part numbers for that project. We haven't gotten to the revised version of this where you select a project, then select a Release and get a list of all parts specific to that release.

                                   Have you read the thread on hierarchical value lists? Did you understand it?

                                   We can do that first and then modify our relationships to get just the parts for a specific release.

                                    

                              • 12. Re: Please need help with a model i'm working at for my class group.
                                AliSheikh

                                     I read it and i do understand to the point that in that particular example Kris had two tables with a bunch of categories that are present all in one table and how it works out , i figured that part out. But in my case i've created a separate table for each category ( project, release ).  I'm just not able to create an understanding between the two cases . I can understand that thread well , but its having those extra tables for each category in my case that has me put in a loop as to what do i do with my setup. 

                                • 13. Re: Please need help with a model i'm working at for my class group.
                                  AliSheikh

                                       So i tried the current setup . It won't show any part  in the partfk field when i select a project. I've attached the value list to the partfk field as shown in the screenshot . 

                                  • 14. Re: Please need help with a model i'm working at for my class group.
                                    philmodjunk

                                         Thanks. I needed that answer to know were to start with my next reply.

                                         First to get a conditional value of Release ID's so that you can select a release for the current project:

                                         Define a value list based on Selected_Release that lists Released in field 1 and ReleaseName in field 2.

                                         Format Line Items::_fkReleaseID with this value list.

                                         This isn't the final step here, but now, when you select a project, the list of releases should be limited to that project.

                                         The final step will be to modify the relationships and value list definition for parts to get just the parts for this release.

                                         Note: I am assuming that Released functions as a primary key for the release table. This won't work unless Released uniquely identifies each record in the release table.

                                         Let me know when that works and then we'll be a short hop from the end of this journey.

                                          

                                    1 2 Previous Next