5 Replies Latest reply on Jun 22, 2014 11:26 AM by abunce515

    Showing Values of Lists



      Showing Values of Lists


           I have two questions - I have attached an image to identify them.

           Question 1 - This is a portal that I am using to show the values from a table called "FinanceTerms".  It shows the different terms (Conventional, FHA, VA, Contract,...) a home is able to be sold with.  Some homes will have one value, some will have 10.  I have a join table linking "Listings" to "FinanceTerms".

           The portal works great for adding and deleting records.  The problem I have with it is the only way I can seem to get it to show the actual term names instead of the primary key is to do a pop up menu.. And I just hate how it looks.  So just looking for your advice on ways that you may have encountered this and overcome it.  I am very much open to suggestions on changes, I just need it to show the value, be able to add a record or delete a record in the join table. 

           Question 2 - All of these fields make me pretty happy with how they function.  It is giving me the exact result that I desire.  I am just wanting to know if I am going completely overkill by giving all these tables primary keys... I just don't want to be in the position where I am thinking a year down the road, "shoot, which i would have gave that table a primary key, now I have this mess to fix".  So I like to be organized to have a better understanding.  All of my lists are separate tables with primary keys fields and then a filed for the value.  Is this going to help or hinder me in the long run?



        • 1. Re: Showing Values of Lists

               View of the tables in question one and "finance terms" also shows what my value list tables look like

          • 2. Re: Showing Values of Lists

                 Q1. A pop up menu is the simplest way to get a field formatted with a value list setup to display names but enter ID numbers to show the name once you exit the field. It looks fine to me, but if you want to use other options this is certainly possible. Another option is to use a drop down list instead of a pop up menu. You can take the name or description field from FinanceTerms that serves as "field 2" in your value list and put it on top of the drop down list formatted field. Give it an opaque fill color to hide the drop down list field and use Behavior in the Inspector's data tab to block access to this field when in browse mode. When you click on or tab into the drop down list field, it pops in front of the name/description field and deploys your value list. When you select a value in the list, it disappears back behind the name/description field and that field now shows the corresponding name or description--matching the value selected in the value list.

                 You might also be able to adapt the method used in this demo file:

                 FileMaker 12 or newer users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                 Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7


                 I have never, ever seen a problem occur due to setting up a table with a primary key that later turned out to not be needed for that table. I have often seen issues occur when a table did not have a primary key and needed  one. On the other hand, adding a Primary key to a table that did not originally have one is pretty simple in FileMaker.

                 It's important to understand what makes the ideal primary key.

                 The ideal primary key is:

            1.           Always unique in the table where it is defined.
            3.           Never, ever changed once assigned to a new record in the table where the key is defined.
            5.           Never includes any additional "encoded meaning" beyond that "unique identification" in 1.
            7.           Is implemented in as simple and "bullet proof" a fashion as possible.

                 That's why most properly set up primary keys in FileMaker databases are auto-entered serial numbers and a smaller subset use Get ( UUID ).

                 But keep in mind that this is for the main "back bone" relationships that define the structure and function of your database. You may find yourself using, such as illustrated in my demo file, special purpose match fields that do not use actual primary keys to get the job done. Whenever you find yourself thinking about using a field in a relationship that cannot meet the definition of a primary key, ask yourself: "What might go wrong if I later have to change the value of this field?" Match fields defined in relationships  used to find records, for example will most likely not have an issue with that and using a name field for that special purpose use will then be OK to use.


            • 3. Re: Showing Values of Lists

                   Thank You - Very Helpful as always. 

                   When I make my pop up lists for my info on my layouts... should I make it populate the primary key and show the related type... or should i just make it populate the type.  Do i really need it to populate the primary key?  I really can't think of why that would be a big deal for what I am doing. 

              • 4. Re: Showing Values of Lists

                     If you don't either set up the value list to enter the Primary key or set up the Primary Key to be looked up from the related table (the trick used in the demo file), then you are not using the Primary Key to link records in one table occurrence to another. Essentially, you'll be using your name field to link records--with all the possible problems that you avoid by using an auto-entered serial number as your primary key.

                     Note: Are there times that I "break the rules" and use a name field in place of an ID number? Yes. But I do so with full understanding of the potential consequences and with systems set in place to handle them and thus protect database integrity. Typically, I might choose to use a name field when the potential list of name values is very small.

                • 5. Re: Showing Values of Lists

                       thanks - you are on the ball as always