3 Replies Latest reply on Jul 9, 2013 4:07 PM by jbell2355

    Value Lists and Database Structure

    jbell2355

      Title

      Value Lists and Database Structure

      Post


           I am new to Filemaker, but understand the concepts behind relational databases.  I am still in the process of learning how to best do things in Filemaker.  I have come to an area that I am really stuggling with, Conditional Value Lists.  I have been able to make them work with sample exercises, but I have not been able to incorporate them into my own project.  So I am posted a snipit of my database structure to show what I have created.  I think I will need to use table occurrences better, but I just have not figured out how just yet.  So for Summary here is what I have:

           I have many Accounts that will each have multiple CONTRACTS. 

           Each Contract will have multiple Shoot Types. 

           Each Shoot Type can have multiple Jobs.

           Each Job will have multiple work orders.

           Each Work Order will have multiple services (some of the services will be repeated for each work order some will not).

           Now with that Background here is what I am attempting to do:

           I want to go to my Work Order Table and create a new work order based on the Account Name -> Contract -> Shoot Type -> Job. 

           It is possible for me to go about this in other ways such as through portals and such, but it seems to be very cumbersome for the end user.  Any help is appreciated.

            

           *Please note the Green Table Occurences I was able to make work individually for the Home Table from other samples, but not able to incorporate into other areas of my structure.

           The Orange Table Occurences are my initial attempt and base for my database structure.  I am not 100% sure if this is the best way or not.  I am open to ideas and suggestions.

            

      Databse_Structure.PNG

        • 1. Re: Value Lists and Database Structure
          philmodjunk

               Not all of that appears in your screen shot, but assume that you have:

               accounts----<contracts----<ShootTypes----<Jobs-----<WorkOrders---<Services

               But I'm not absolutely sure what you mean by this:

               

                    I want to go to my Work Order Table and create a new work order based on the Account Name -> Contract -> Shoot Type -> Job.

               In what field are you entering/selecting data that will use the conditional value list?

               Do you want to select an account and get a value list of only the Jobs for that account? Do you want to set up a hierarchical value list where you select an account, select a contract from a list of contracts for that account, then select a shoot type from those for that contract, etc?

                

          • 2. Re: Value Lists and Database Structure
            jbell2355

                           *Sorry I thought I had posted in the wrong area previously.

                            

                           Phil,

                           Thank you for the quick response.  Sorry for the lack of clarity of my previous post.  However, yes I was looking to create a hierachical value list that I can select from.  Attached is a sample of a screen where I am trying to use this list. 

                           Essentially upon the creation of a new record a Work Order number would be generated (simple serial Number) The user would select the account then the contract, shoot Type, and Job form the value lists.  They are all based off of primary and foreign keys to relate up through the database.

                           I have other occurrences where I would like to use these Hierarchical Value lists as well, so I am trying to not only implement it in this situation, but also understand it better so I can reuse it in other areas.  However, if there are better ways to do some of these things I am all for that as well.

                           Thank you again for your help with this matter.

                  

            • 3. Re: Value Lists and Database Structure
              jbell2355

                   Ok so I recently was able to get a filemaker consultant from RCC to help me out with this and some other issues I was having.  I am posting the results of my findings and new structure. 

                   What I learned is that it is impossible or extremely difficult to have conditional value lists spanning multiple tables, however, you can set up a table to do just that for you with the use of multiple foreign keys.  For example I had to add the foreign keys of AcctID and ContractID to my jobs table to make it work properly.  I originally had thought this might pose a problem, but so far it makes it easier to enter the data and a way to track things a little easier. 

                   Phil Thank you again for your help and response.  I appreciate the support.

                   Josh