8 Replies Latest reply on May 27, 2017 9:54 AM by philmodjunk

    Value lists/relationships


      Hello again,

      I'm building a database for a law office to track customers, contacts, companies & projects.  The office practices various kinds of law, so eventually there will be many similar groupings of tables for each area of law we practice.


      Some of the companies we deal with quite often, so I have separate layouts for our "rolodex."  But they are often related to the various projects we have going, so I want to relate them to the projects.


      I want to use drop-down lists of the companies and contacts by stacking a field with the ID, with the calculated text-string result of the Company + Dept + street + City, State, Zip.  I want to use all of that string because there are many companies in our "rolodex" with many branches.


      I have been able to get the ID number to appear in the first field, but I can't get the proper calculated results to show in the 2nd field.  See the screenshot below.  Am I supposed to relate something differently?



      And for the second field, I am asking to it to show the CO-ID_Calc field in the FILE_Company_Manag table.


      But here is what I get.  Not sure what I'm doing wrong.  Help?


        • 1. Re: Value lists/relationships

          Tables, Table Occurrences (The "boxes" on your relationship graph), and layouts are three different things.


          When you say: "This is what I get", are you referring to the "<Table Missing>" error text in your Contact First Name field?


          That is first a problem with your layout design. You have added a field object that refers to a table occurrence that has either been deleted or that is not related to the table occurrence on which your layout is based. (Show Records From in Layout Setup identifies the table occurrence that serves as the basis or context for your layout.)


          You need to double click that field while in layout mode and respecify it by selecting a field from the correct table occurrence.

          • 2. Re: Value lists/relationships

            Actually, no.  Once I get the value list fixed, I  know how to fix the <<table missing>> field.


            And actually, that wasn't the correct screen shot to send.  It didn't illustrate what I was trying to say very well.

            Let me try this one instead.

            The Opposing Attorney Firm ID 18048 is NOT All Fund Mortgage Final Docs.....

            And the Opposing Attorney ID (he's the person with the firm) ID won't show anything in the 2nd field.

            • 3. Re: Value lists/relationships

              And what table occurrence is specified as the context for this layout?


              And when you select the 2nd field while in layout mode, what text shows to the left of :: in the "display data from" box in the inspector?

              And what is the name of the first field? (and I assume it is the field formatted with your value list?)

              It's the relationship between those two table occurrences (The one for the layout and the one for the second field) that will control what data appears in that field.

              • 4. Re: Value lists/relationships

                Thanks for responding!


                The layout is based on a table called FILE Info.  I'm using tabs, for ease of navigation, and I wish I could base each tab on a different table, but I suppose it can't - at least from what I've been able to deduce from info I've dug up on the filemaker community website and the filemaker website.  That's true, right?


                So the first field is based on a field in FILE Info called _kp_FILE_CIV_OPP_ATTY_Company_ManagID (I know - it's too long, but best for keeping future fields clarified), and yes, it is the file formated with the value list.


                The second field called ::CO_ID_Calc is in a table occurrence of FILE_Company_Manag, and the new table occurrence has been renamed to CIV_OPP_ATTY_Company_Manag.


                I know I don't have some relationship configured properly, but I can't figure out how.


                And in the relationship diagram, please ignore the table occurrence of "Old File." It will disappear soon.

                • 5. Re: Value lists/relationships

                  the first field is based on a field in FILE Info called _kp_FILE_CIV_OPP_ATTY_Company_ManagID

                  This field is not used in your relationship graph to match to records in a related table. Thus, selecting/entering a value into this field cannot match to records in another table and thus has no effect on what data from related records might appear on your layout.


                  The second field called ::CO_ID_Calc is in a table occurrence of FILE_Company_Manag

                  Then if your first field was _kp_FILE_Company_ManagID, selecting a value from the value list should work, but not the field that you are currently using. You'd have to either change fields or change your relationship.


                  And a note about how you have named your fields. Name them as long as you need to tell which is which and the general purpose of that field.


                  But I see that you have at least 4 fields in File Info that start with _kp_. "kp" stands for "primary key". A primary key has a unique value in the field for every record in the table. You normally only have one primary key field to a table. Your field names and the fact that you have many such with _kp_ suggest that these are not actually primary keys and thus should be named differently to avoid confusion.

                  • 6. Re: Value lists/relationships

                    "But I see that you have at least 4 fields in File Info that start with _kp_. "kp" stands for "primary key". "


                    I think this is where I am very confused.  In FILE Info, you're correct that there are a lot of primary keys. All the tables relate except for the opposing attorney tables that I want to relate through the CIVIL table, back into FILE Info and then into FILE_Company Manag.  So since that relationship hasn't worked, I keep trying to add primary keys and make relationships that might carry through all the tables to make it work.  But it never seems to work.  Maybe  I'm out of my depth.


                    How do you suggest I change something so that the relationship will work?

                    • 7. Re: Value lists/relationships

                      First, let's be clear on what a "true" primary key should be.


                      A primary key:


                      Uniquely identifies each record in a table. In FileMaker solutions, that means that the field is usually defined to auto-enter a serial number into a number field or Get ( UUID ) into a text field (with "do not replace.." cleared.)


                      The following should also be the case if you want to use "best practices":


                      1. A Primary key should never, no never, ever under any circumstances be changed once it is assigned to a newly created record.
                      2. A Primary key should be devoid of any "encoded" or "special" meaning. It's a unique identifier, nothing more. (no including the year, part of a name or other such "secret decoder ring" info.)
                      3. A feature used to assign primary keys should be as simple and as "bullet proof" as possible--hence the use of serial numbers or the Get ( UUID ) function.
                      4. A primary key is best kept hidden from the users, they should never need to see it's value. (If they never see what value is used as a primary key, they can't get the idea that it needs to be changed and they can't accidentally change it by mistake--such as entering find criteria into the field while in Browse instead of Find mode.)


                      All of these rules come from dealing with the consequences that ensue when a user either changes the value of a primary key, accidentally generates duplicate primary keys or requires you the developer to change the content of a primary key. All of which can create complicated situations where you have to carefully update the values of matching foreign key fields before updating corresponding primary keys and all without screwing up the relational links that bring order to your data.

                      • 8. Re: Value lists/relationships

                        Note that given the above implementation of a primary key, you never need more than one in any given table.


                        I previously pointed out:


                        if your first field was _kp_FILE_Company_ManagID, selecting a value from the value list should work, but not the field that you are currently using. You'd have to either change fields or change your relationship.


                        You have to decide if your layout design is wrong or your relationship. Please note that if you are formatting a field with _kp in its name, it is either named wrong or is the wrong field to set up with a value list as the user should not ever be allowed to change its value in the first place.


                        Another option is to hire a developer to work with you on your project--either doing the work for you or coaching you in your efforts to create your own.