8 Replies Latest reply on Mar 26, 2009 1:45 PM by comment_1

    Relational inputs - at wit's end!

    miw

      Title

      Relational inputs - at wit's end!

      Post

      I have three tables, Rock, Location and Strata.  Location (one) is linked to Rock (to many).  So far so good in designing my layout screens and establishing my relationships between those two tables.  It's my Strata layout that I am having problems with.  I have Strata and Location linked to one another.  Any relationships I have between tables are based on the autocalc'd unique identifier for each table.  I want to establish a link between Strata and Location and then to a specific rock through the Rock table.  In my Strata layout, I want a popup field in which I can first select a Location and then go to another popup field where I can then select a specific Rock (only those found in that location, not all rocks).  I have tried all sorts of value lists, scripts, etc. without success; I cannot seem to get the Rock popup field to show just the Rocks linked to the Location.  What do I need to do?  Adjust the relationships?  A script?  If a script is the answer, please let me know what it would look like.

        • 1. Re: Relational inputs - at wit's end!
          comment_1
             What are the relationships in real life? Does each stratum belong to a specific location, or does every location have the same set of strata?
          • 2. Re: Relational inputs - at wit's end!
            miw
               Each location and rock in that location have a specific stratum.  Stratum would be at the top of the hierarchy, with a linked Location next and a linked Rock attached to the Loaction.
            • 3. Re: Relational inputs - at wit's end!
              comment_1
                

              OK, now you have confused me. I thought a rock would be found at location "Adam", in stratum "Alpha". Another rock would be found at location "Betty", in stratum "Beta".

               

              If stratum is at the top of the hierarchy, then ALL rocks found at location "Adam" must automatically belong to stratum "Alpha". IOW, you first divide the terrain into strata, then establish several locations in each stratum. Is that correct? I must say it doesn't sound very plausible (to someone who doesn't know the first thing about this). I would expect you establish locations first, then dig in them - and find rocks at different strata.

               

              Please answer this without reference to "linking" or to Filemaker at all - I am trying to understand the REAL situation.

              • 4. Re: Relational inputs - at wit's end!
                miw
                  

                At a Location, I can have a selection of different Rocks.  A Rock can be in multiple Locations.

                For each Strata, I can have a selection of different Locations (and thus those Rocks found in those Locations). A Location can be in multiple Strata.

                 

                In the Strata layout, I want to be sure to connect first to a Location and then to just one (not all of them) of the Rocks in the Location.

                 

                I hope this helps... Thanks for the help.

                 

                • 5. Re: Relational inputs - at wit's end!
                  comment_1
                    

                  I am afraid you have lost me completely. A Rock can be in multiple Locations?

                   

                  I suggest (again) you describe the real situation - leaving aside for the moment what connects to where, and what selections you want to make. 

                  • 6. Re: Relational inputs - at wit's end!
                    miw
                      

                    It would be a type of Rock, not an individual Rock.  So a type of Rock, say quartz, can be found in multiple Locations, say Georgia, Florida, and Tennessee.  Also, assume another type of Rock, shale, can be found in the Locations of Georgia and Arizona.  I may have a Strata, say riverbed, which only applies to Georgia and quartz.  In the Strata layout, I would like to first pick the Location (for example, Georgia) and only then pick the Rock (either quartz or shale in the case of Georgia) found in that Strata for that Location. 

                     

                    (remember I am using the autocalc'd unique identifier for each table).

                     

                    Does that help?

                    • 7. Re: Relational inputs - at wit's end!
                      comment_1
                        

                      It's a step forward... I believe you are missing a table, possibly two.

                      If a rock of type quartz can be found in Georgia, this fact needs to be recorded somewhere. It is NOT an attribute of either the location, nor of the type of rock. It needs to be recorded in a table of its own (say Findings), which could be related to Locations and RockTypes:

                      Locations -< Findings >- RockTypes


                      However, I am still not sure how do Strata figure in this. If Strata is a list POSSIBLE strata types, that may or may not be found in a location, then another table is needed to indicate which strata do actually exist at each location:

                      Locations -< LocationStrata >- Strata

                      And in such configuration, you'd probably want to make Findings a child of LocationStrata. So the logic would be something like this:

                      Locations:
                      1. Georgia
                      2. Florida
                      3. Tennessee

                      ...

                      Strata:
                      1. Riverbed
                      2. Gravel

                      ...

                      LocationStrata:
                      1. LocationID 1, StrataID 1 (meaning: there is a riverbed stratum at the Georgia location)
                      2. LocationID 2, StrataID 2 (meaning: there is a gravel stratum at the Georgia location)
                      ...

                      RockTypes:
                      1. Quartz
                      2. Shale

                      ...

                      Findings:
                      1. RockTypeID 1, LocationStrataID 1 (meaning: quartz can be found at the riverbed stratum of the Georgia location)
                      ...


                      MIW wrote:

                      (remember I am using the autocalc'd unique identifier for each table).


                      I am not sure what you mean by that. Your unique ID should be an auto-entered serial number, divorced of any meaning.

                       
















                      • 8. Re: Relational inputs - at wit's end!
                        comment_1
                          

                        A simpler option could go like this:

                         

                         Locations:
                        1. Georgia
                        2. Florida
                        3. Tennessee

                        ...

                        Strata:
                        1. Riverbed
                        2. Gravel

                        ...

                        RockTypes:
                        1. Quartz
                        2. Shale

                        ...

                        Findings:
                        1. LocationID 1, StrataID 1, RockTypeID 1 (meaning: quartz can be found at the riverbed stratum of the Georgia location)
                        ...

                         

                        Note that this doesn't really say which strata do exist where - only by implication.