6 Replies Latest reply on Nov 15, 2013 5:06 AM by PaulLawrence




      UUID & Foreign Key & Drop-Down List


           FMP-12 & iMac

           The problem I have is how to relate one record to another using a drop-down list. (Perhaps the real problem is that I haven't touched FM for 2 years!)

           I have two tables; PRIMARY and SECONDARY. Each table has a UUID. The PRIMARY table has a foreign key field for the SECONDARY table. Both tables are populated now, but not related by data. I want to manually relate the two tables. (They are related in the RELATIONSHIP graph.)

           I create a simple layout for the PRIMARY table that includes the foreign key field (temporarily.) I also have field that displays the NAME field from the SECONDARY table to show the related data.

           The foreign key field is formatted as a drop-down list with a value list referenced.

           The value list is created with the UUID field from the SECONDARY table with a second field that shows the NAME field from the SECONDARY table. Only the NAME field is displayed in the drop-down list.

           The problem here is that I don't want to display the UUID in the drop-down list, but I do want it to be entered into the foreign key field of the PRIMARY table. I only want the user to select from a drop-down list of NAMES.

           The idea was to minimize the length of the drop-down list field (UUID) to just show the drop-down arrow and place that at the right border of the NAME field.

           Well, that doesn't work, because now the drop-down list of NAMES is offset way down the page by the length of the UUID!

           Is there no way to use the drop-down list in this fashion, or am I just muddled in my thinking?

           Perhaps I'm approaching the table linking process in the wrong way?

        • 1. Re: UUID & Foreign Key & Drop-Down List

               Set up your value list to only display values from the second field. That will omit the UUID from view in the list. If you use a pop up menu format, that's all you need do.

               With a drop down list, you can hide the foreign key field behind your name field from the related table. Use behavior settings to deny access to the field while in browse mode. When you click on this field, the drop down list field pops to the front and deploys the list. When you select a name from it, the list disappears back behind the name field that now displays the name just selected.

          • 2. Re: UUID & Foreign Key & Drop-Down List

                 Hiding the foreign behind the NAME field works until the drop-down arrow is clicked. Then the UUID is highly visible as well as the items from the value list.

                 Also, if the foreign key field width is anything less than the length of the data it contains, the data will wrap and make the height of the field twice or three times higher as well as visible.

                 There just doesn't seem to be a way to work with the drop-down list in this situation.

                 I suppose I'll have to script something to get how I think it should work for the user.

                 Its just hard to believe this is how it works.

                 I thought it was just me.

            • 3. Re: UUID & Foreign Key & Drop-Down List

                   I'd use an auto-entered serial number instead of the UUID myself.

                   You might also take a look at this demo file that inverts the process a bit so that you can use auto-complete with the value list and yet still link records by ID instead of by name:

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


              • 4. Re: UUID & Foreign Key & Drop-Down List

                     Thanks, Phil. I've had a lot of trouble with FM's serial number for an ID. Especially when users are importing records. Also when script upgrades get involved.

                     But mostly I use UUID because I'm advised that this database could expand into 10's of thousands of records and be networked in the future.

                     The drop-down list works nicely as long as the "first" field isn't very long.

                     I'll have to go in a different direction.

                     Thanks for your help.

                • 5. Re: UUID & Foreign Key & Drop-Down List

                       Script that import data can also set serial numbers--if not importing from a backup or earlier version of the file and when importing from backups or older versions, a script can update serial number "next value" settings at the same time.

                       We have a networked database with millions of records in some tables so:


                            could expand into 10's of thousands of records and be networked in the future.

                       Isn't sufficient reason to me to use the more cumbersome UUID values.

                       The main reason UUID's were added as a new feature of FileMaker 12 was to support better synching of data from distributed copies of the file--such as having several iPAD users all creating records on isolated copies local to their device and then needing to upload the data back to the main database without getting duplicate values in the primary keys.

                       And I suggest a look at the demo file I recommended earlier. It eliminates the need for having the primary field as "field 1" completely and makes the argument of UUID vs serial number moot when it comes to setting up a value list.

                  • 6. Re: UUID & Foreign Key & Drop-Down List

                         Finally got the time to say, "Thank you, Phil!"

                         I added a table occurrence of my Customer table with the Select Name linked as shown in your example file with my Site table.

                         I already had a foreign key to Customer in my Site table relationship.

                         All I had to do then was set up a Lookup in the table options for my foreign key in my Site table.

                         Worked like a champ!

                         Now my drop-down lists only show the Name, but the Lookup option updates the foreign key through the Select Name TO.

                         No more visible UUIDs for me!

                         Thanks again!