4 Replies Latest reply on Jul 2, 2013 3:14 PM by Boltonjohn

    Can I lookup data from a separate FM12 database.



      Can I lookup data from a separate FM12 database.


           I have been using FMP 5 for years, and bought FMP12 recently because the old version was getting very cranky.  

           I spent a few hundred pounds worth of time trying to get to grips with the new system and have given up - I just want a version of a working system that used to do well on FMP5 without spending an enormous amount of time struggling to get to grips with an programme which is alien to me and which, as far as I can see has nothing to offer me that I did not have in the old program. 

           So, I have converted all the old databases to 12 (via 11 or whatever) but have lost all my lookup relationships on which the old system was based.

           I have tried specifying a field as a look-up field but cannot find any way to look up data on another database.  All that seems to be available is defining relationships with field in different tables within the same database.  I did try creating a new layout with the information for the dates file but when I tried to create a relationship the new layout does not appear as an option in the relationships box.

           Can anyone tell me how to look up and import data from another FM12 database? Also, a simple explanation of the difference between a table and a layout may clear some of the mist away.

           Any help would be greatly appreciated.




        • 1. Re: Can I lookup data from a separate FM12 database.

               Lookups in Filemaker 12 function exactly the same as lookups in Filemaker 5--provided you are referring to the looked up value field option.

               BUT data indexing has changed along the way and this may be why your look ups no longer work as this changes how matchfields that contain text values match values with the match field in the related table

               In fileMaker 5, the following data examples work to match values but will not work in FileMaker 11 or 12:

               Mr. Jones >> Mr Jones
               Fred's Data >> Freds Data

               In the older versions, the punctuation characters are ignored while they are not ignored in current versions.

               This is just a guess from my own experience converting a very complex DB from 5.5 to version 10. Other issues could easily be involved that are keeping your system from working as expected due to the large "jump" in versions that you've made with your file conversion.

          • 2. Re: Can I lookup data from a separate FM12 database.


                 What I ended up with after conversion was six separate databases each containing specialist information from which I used to look up data to pull into the main database.  On 5 all I had to do to set a look up field to work was to specify the target file and set the field to be copied if there was a match on the key field.  Trying to do the same thing on 12, I get asked for the tables I want the relationship to apply to (none in this case) and there seems to be no option to look up data from an external file as there used to be on the earlier non-relational versions.


            • 3. Re: Can I lookup data from a separate FM12 database.

                   This is not the case, but because you can now design a database where all your tables are in a single file instead of one table per file, the steps for making this change are different.

                   Open Manage | Database | Relationships.

                   Click the button in the lower left corner of this screen. (I know, the buttons have neither tooltips nor labels--to see what they do you have to ether click them or look up this screen in FileMaker help.)

                   This will create a new Tutorial: What are Table Occurrences? in your relationship graph and you can use the Add FileMaker Data source option in the data source drop down of the dialog that pops up to select first your file and then the table within it as the data source for this table occurrence. You can then link the table in your current file to this file by dragging from a field in one table occurrence box to another.

                   But this should have been done automatically when you converted the files to the new file format so I'd check the table occurrences over (Those with names in italics come from a different file) and see if you don't already have what you need here. You'd set your look up to copy from the table occurrence--which in turn can refer to a table in an external file as well as any tables defined in the current file.

                   BTW, while it is new and likely confusing to you, this change where FileMaker can now have multiple tables in the same file represents a really major improvement in FileMaker first introduced in FIleMaker 7.

              • 4. Re: Can I lookup data from a separate FM12 database.


                     Thanks for that. I will have a go at it tomorrow.