5 Replies Latest reply on Jul 11, 2012 6:15 AM by philmodjunk

    Performing look-ups from external FM files.



      Performing look-ups from external FM files.


      I have just taken the plunge after many years and upgraded from  FM 5 to 12.  I did buy FM 8 a few years ago, opened the box, looked at the features, decided there was nothing new I needed and left it in the box - sop now I have the big leap from 5 to 12 which is causing me problems.

      In 5, I have a stock database which contains several thousand records of collector coins and antiques.  I also have other Filemaker files which contain information which I look-up from the main file.  

      For example I list stuff on a web mall site which needs a category code number and a sort code.  Say  I enter a fresh coin for the emperor Nero in my main database. I have the main database set to do a look-up for the date of Nero's reign from an 'emperor' file, a look-up from another file to generate the web site category number for Nero, and another look-up on a separate file containing sort codes for each emperor so that the coin of Nero appears in the correct sort order within its web site section. There are complications which stop me have all the look-up data in a single look-up file.

      All pretty simple as it was.

      I cannot for the life of me see how to do the same function in FM12. All I want to be able to do is create a coin layout and look up required data from several external filemaker files.   Or create a new record for an antique and do similar functions to those I do for coins.

      Is it no longer possible just to say "please look up a matching emperor in external file xxxx and if you get a match copy the web site category number into the category field on the main file"

      Help (please).

      John Cummings

        • 1. Re: Performing look-ups from external FM files.

          Are you building a new database file from scratch or have you converted your FMP 5 files to FMP12?

          This would require a two step conversion, but can be done if you download a trial copy of Filemaker 11 so you can convert to the .fp7 format and then use FileMaker 12 to convert to .Fmp12 format.

          Might save you a lot of work and the converted file would then contain examples of external file lookups of date via a relationship.

          Here's how to build the needed relationship to a table in an external file from "scratch":

          Open Manage | Database | Relationships and click the button at the extreme left, bottom corner of this window to add a new "table occurrence" to your database. This opens a dialog for specifying the data source for your new occurrence. Select "Add FileMaker Data Source" from the Data Source drop down. This opens a dialog box where you can find and select the file containing the table you want to connect to. Selecting it opens a list of tables in that file and you can then select the table you want. Once you've done that, you can drag from a field in one of your other table occurrences to a field in this new table occurrence to define a relationship and once you have a relationship, you can use the auto-enter options for a looked up value or a calculation to copy data from a field or fields in this table into a field in a table in your current file.

          Note: if you are building a new solution from the start, I suggest that you not use separate files for each table but to instead define all your tables in a single file. This makes setting up your relationships, layouts and scripts much easier than when using a separate file for each table.

          • 2. Re: Performing look-ups from external FM files.

            Thanks for the help.   I wanted to create a system using FMP 12 that did as near as possible exactly what I used in version 5. I only upgraded because 5 does not work on Lion and without Lion I cannot have Cloud.  To be honest, I only upgraded to FMP 5 from 2 because when OS 7 came out there was the same problem with older programs.  

            However, I now have the new version and I must try to get to grips with it.

            Regarding having all the information on one database: all the data on the look-up files is normally completely static, is very rarely altered in any way and in content is probably as much as 20 times larger than the active database data.  It just does not seem the best thing to have a database where 95% of the data is just static baggage only used for an hour or so once a month when I am entering new stock. Is this correct?

            I did start off by creating a mini database to play with with a few records in each layout. I created a layout for stock, a layout for each of the look-up files (I am presuming 'tables' are a similar thing to layouts) and went through exactly what you described to set up relationships. After more than an hour, the nearest I got to getting it working was to manage to get the reference field appearing in the field that should have contained the looked-up data.  So, if I was looking up data for Nero, I ended up with the word 'Nero' in the data field but no sign of the data I was looking for.  So far I have not managed to sort it out but will keep trying.

            Thanks again for your help.




            • 3. Re: Performing look-ups from external FM files.

              Is this correct?

              Not really. It complicates your design--especially for someone who's a "newbie" and FileMaker files can contain hundreds of tables with millions of records and have a file size measured in Gigabytes. So I don't see any real negatives to having all the tables in one file. Keep in mind that this has been a new feature of FileMaker since FileMaker 7: previous versions could only have one table in each file. We can now have all those tables inside the same file.

              Try setting it up with multiple tables unless you decide to convert your old database file instead.

              (I am presuming 'tables' are a similar thing to layouts)

              They aren't the same thing. Remember how you could create multiple layouts in one file in FileMaker 5? That was a case where you had one table, but many layouts. THe same is true here, you can have more than one layout that refers to the same table. So even though fileMaker creates a new layout with excactly the same name each time you create a new table, layouts and tables are two different things.

              You may find this tutorial on Table occurrences--the "glue" that links a layout to a table and is represented by a "box" in Manage | Database | relationships: Tutorial: What are Table Occurrences?

              After that, you may find this thread on Lookups helpful: Auto Fill

              • 4. Re: Performing look-ups from external FM files.

                Thanks for that. I must admit, file size  is worrying me.  The total file size of all the data integrated into one database would be around 2.5 gigabytes running on an imac with 4gb of memory and a  Macbook Air, probably with less memory. I used to be a mainframe maintenance programmer a long time ago and then the  mantra was to  modulise everything to make it simpler: this seems to run on the opposite principle. 

                I think I have to step back and think before I go any further.  It may well be that there is a simpler solution using another simpler database program - possibly even the database attached to Apple Works. I have no use for any of the Filemaker's network capabilities nor anything in the program that is anything to do with the web. There may be something out there that is a lot more suitable for what is essentially just organising a lot of text records ready to go on web sites that have their own sofware systems.

                Thanks for the help.


                • 5. Re: Performing look-ups from external FM files.

                  To repeat, an all in one file is a SIMPLER design--that's the main reason that I am recommending that you use that. You have to establish the same relationships, the same scripts, the same layouts either way, but with an all in one file, the relationships are all in one place as are all other design features. In particular, this can make scripts that interact with your file simpler.

                  FileMaker files do not load into memory in their entirety so the numbers you quote do not pose any issues for what you describe as long as you have sufficient disk space--which would be the same issue only more so if you put each table in its own file.

                  You seem to be ignoring a key option here: Use the download link in my first post to install a trial copy of FIleMaker 11. Use it to convert your original files to that format. Then use FileMaker 12 to convert these to the format for FileMaker 12.

                  You may find that after the two file conversions, you can just open and use your original, but now converted database. It is also possible that some adjustments to the final version may be needed--it depends on the design of your original database as to whether that is needed or not, but can require a lot less effort than building your database all over again from the ground up.