14 Replies Latest reply on Apr 28, 2011 2:43 PM by rjlevesque

    Novice frustrated with keys!  This seems it should be fundamental, but...

    JamesBransom

      Title

      Novice frustrated with keys!  This seems it should be fundamental, but...

      Post

      Hello everybody,

      From my understaning of table relationships, it seems to me that my problem should have a very basic, built-in solution, but I can't for the life of me figure it out.  Here's a simple senario to illustrate my problem.

      In real space, addresses are associated with houses and names are associated with people. To me it would make sense, then, to have a "Houses" table and a "People" table.  For simpllicity, each table has two fields.  The houses table has an "Address" field and a "pk_House_Id" field.  The people table has a "fk_House_Id" field and a "Name" field.  The "*k_House_Id" fields are the Match fields for a one-house-to-many-people relationship.  If there are two records in the "Houses" table with addresses A and B and one record in the "People" table with the name "Jim" and Jim currently lives in (and therefore holds the key to) house A, how does FMP move Jim to house B?  Take the House-A key away and give him the house-B key, right?  From a user's perspective, Match Keys are meaningless, so what is the best way to do this without confronting the user with manipuating, or even seeing key fields?

      Dis/re-associating records by simple key manipulation seems like such a fundamental advantage of relational db's but I can't for the life of me figure out how to do this.  I'd be grateful for any pointers.

      Thanks, Jim

        • 1. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
          LaRetta_1

          "Dis/re-associating records by simple key manipulation seems like such a fundamental advantage of relational db's but I can't for the life of me figure out how to do this. "

          You create a value list called Addresses from the House table (usually called Addresses).  First create a calculation which concatenates street, city & Zip (as Address1 & ", " & City & " " & Zip).  Next, select 'use values from field'.  In the left pane, you select the pk_houseID and in the right pane, you select the calculation.  Below, specify 'all values' and select 'only show values from second field.

          Then go to your People, fk_houseID field and attach Control Style (Inspector > Data) of Pop-Up Menu.  Select your newly created value list in Values From.  When you pop the field it will display the addresses.  When you select one, it inserts the ID into the field.  After you leave the field, it displays the address.

          This is how we re-associate the IDs with their real values.  If you want the person to select the state (or zip) and then the addresses filter down to only those associated addresses, it is called a Conditional Value List and it filters.  One good example would from Comment (go to post #5 in that thread):

          http://fmforums.com/forum/topic/61948-simple-3-level-hierarchical-valus-list/page__p__293003__hl__%22conditional+value+list%22__fromsearch__1#entry293003

          • 2. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
            rjlevesque

            Wow, LaRetta I think you just blew his mind. LOL I am trying to look at this from a beginner level and thinking I never would have understood that even though you expalined it perfectly. hehehe

            I think perhaps we need to point our friend in the direction of some reading on basic structure of a relational database, because LaRetta...you know as well as I, if someone can't grasp the whole "relational" part of a database and think in 3D they will never get it. Sounds to me if he just had a good read on the basics of a relational database the ole lightbulb just might pop on.   Cool

            Just in case...going to go see if I can dig up a really easy to understand basics tutorial that will get him up and running.

            The biggest mistake we all make (in my opinion) when we first start with relational database development is we try to make it waaaaaay more complicated than it really is. Once you get the "OH!!!" of understanding everything will just start to make sense to you. 

            Keys:

            Primary Key - All tables have a primary key, which is a way to make that record unique in it's own way. This keeps us from getting data mixed up, keeps things all organized.

            Foreign Key - Ok, bare with me here...remember how I told you we use primary keys in our tables? Ok, and remember how this is a RELATIONAL database? So I need the two tables to get along with each other...so we need to introduce them. 

            EXAMPLE

            Table 1: my primary key is 1234 ( example field name: kp_Table1_ID)

            Table 2: my primary key is 7896 (example field name: kp_Table2_ID)

            So their ID is almost like their name isn't it?

            So I introduce Table 1 to Table 2 by giving eachother their name like so...

            Table 1 will now have its primary key of kp_Table1_ID and a foreign key of kf_Table2_ID

            He holds on to Table 2's ID or name, now they are related.

            -----------------------------------------------------------------------------------------------------

            Just a very VERY basic little info on keys...now going to find you a good tut on relating tables within a database. 

            • 3. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
              rjlevesque

              And here is an excellent tutorial...

              http://databases.about.com/cs/administration/g/primarykey.htm

              It has two links that explain things in further detail depending on which area you want to know about next. But we're not chasing you away now...still feel perfectly free to ask questions here at any time. I know LaRetta or Phil either one can always offer some great advice and assistance, and I am here often to help whenever I can with what I can.  

              Smile

              And we're just the regulars here...you have an entire community here so don't get frustrated, we all are here because we love and believe in FileMaker and we are happy to help you if we can.

              • 4. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
                JamesBransom

                Thanks to both of you for your very thoughtful and thorough responses! You renew my belief in the intrinsic goodness of fellowship : ).  I am obligated to try to be just as thoughtful in expressing my appreciation.

                LaRetta's solution was perfectly clear to me, but, rjlevesque, thank you for taking me under your wing (you are right to assume that there are gaping holes in my understanding of RDB's).  In fact, I had already implemented that solution in several variations.  Unfortunately, I used a dropdown and I never thought to try the pop-up conrol (I knew the answer must be simple!).  Now I have a new problem.  If I include in the Value List the option of "Other...", it presents a dialog with the "House_Id" field value which is meaningless and untenable as an option for creating a new, complete record in the Houses table.  Can I trigger a script from the selection of the "Other..." option to change that behavior?  Should I use a scripted button or is there a better solution?


                @ rjevesque:  I appreciate your defense of simplicity.  As an experienced pragmatist, I'm sure you'll appreciate my beginner's idealism; as you said, "Been there, done that".  Call me stubborn, but I'm approaching this from the point of view of reducing the amount of duplicate data, decreasing the propensity for runtime error, extensibility and providing the user with an elegant, usable interface.  A category table just makes more sense to me than a category field.  I'm aware that this approach could become fractal ad infinitum, but I think Idealism is a required course at the University of Pragmatism.  I'd be interested in you predictions about how I might feel about it three months from now.

                Thanks again.  I look forward to furthering.

                • 5. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
                  JamesBransom

                  BTW- @LaRetta

                  Thanks for the edit before the quote ; )

                  • 6. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
                    rjlevesque

                    Perhaps yours is just a case of "exitus acta probat" where your particular need warrants that particular frame of thought. Plus, I must admit it does make a huge difference as to how large you expect the database to be. If it is not a stable foundation, when the subject grows it will become unstable and throw spurious erros or even crash.

                    ------ quote ------

                     Call me stubborn, but I'm approaching this from the point of view of reducing the amount of duplicate data, decreasing the propensity for runtime error, extensibility and providing the user with an elegant, usable interface.  A category table just makes more sense to me than a category field.

                    ----- end quote ------

                    • no duplicate data in the manner I suggested, that was my point exactly...not even any unneccessary extra weight to add to the application like extra tables.
                    • runtime errors should not exist regardless, FileMaker creates your runtimes...you have no control over that, the wizard does it all.
                    • Elegant User Interface not as important as User Simplicity and ease of understanding. End-users want to be able to open the software and just use it. (a web 2.0 look is always nice, very minimalistic)

                    Speaking from experience as a software engineer who designs GUI for his software for a living:

                    BSSE - OOP - UNA - DBA

                    ----------------------------------------------------

                    But you see that is the beauty of relational database design, you are given a set of guidelines, then after that...pretty much your only limitation is your imagination for creativity.

                    Have a great one! Smile

                    • 7. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
                      JamesBransom

                      Uh oh...I spoke to soon.  LaRetta's suggestion works as expected with a Value List whose second field is "text" (and presumably other data types). However the "calculation" field in the Value List is not working.  The list is populated with only data from the first record in the related table and selecting that value doesn't change the value of the foreign key field.  Anybody?

                      • 8. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
                        philmodjunk

                        Check your calculation and make sure that it is a store/indexed field. Use calculation fields in such value lists all the time...

                        • 9. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
                          JamesBransom

                          http://dl.dropbox.com/u/11193844/another.fp7

                          Here's a link if you'd be so kind?  Your suggestion gave me hope but to no avail....

                          • 10. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
                            JamesBransom

                            BTW-I'm using FMP 11.0v3

                            When you're done teaching me FileMaker you can teach me how to post a link. ; )

                            • 11. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
                              philmodjunk

                              Open up the calculation field's definition and change the return type from Number to text. (It's a drop down in the lower left corner of Specify Calculation.)

                              • 12. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
                                JamesBransom

                                Oh man!  You're the greatest!  It would have taken me another week to find that and I only have three weeks left on my trial version.  I'd like to buy you a beer!

                                • 13. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
                                  philmodjunk

                                  Sigh, I have more people offering to buy me beers in this forum these days and I don't drink alcohol! Oh well, I gues "virtual beers" are non-alcoholic anyway. Wink 

                                  • 14. Re: Novice frustrated with keys!  This seems it should be fundamental, but...
                                    rjlevesque

                                    DOH! Dang it Phil! One of these days I will beat you to the answer! LOL I was just about to tell him about his calculation.