5 Replies Latest reply on Sep 22, 2011 11:06 AM by philmodjunk

    Portals and value lists from external tables

    jayGamel

      Title

      Portals and value lists from external tables

      Post

      I can't access the original table value lists for portal fields from an external table. Do I reconstruct the value list in the host table or is there a way to access the relational table's existing list?

       

      Jay Gamel

        • 1. Re: Portals and value lists from external tables
          philmodjunk

          If you define the value list in the external file, you can use the "use value list from another file" option.

          If you use Manage | Database | Relationships to add an occurrence of a table from the external file, you can use the specify field options and specify values from this table occurence like you would an occurrence to a table that's local to your current file.

          You'd use such an occurrence to set up a portal to a table in an external file. (We had to do that with every single table back when FileMaker only supported one table in each file...)

          • 2. Re: Portals and value lists from external tables
            jayGamel

            I should have looked at that closer. It works fine, thanks.

            And I do remember the old days. I actually switched to Access for years to get around that. FMP 11 is a pleasure, but I'm just getting used to it.

            My next goal is to integrate all or most my related tables under one roof. Do you have any philosophy about combining city, states, zip or other common reference tables or leaving them external?

            • 3. Re: Portals and value lists from external tables
              philmodjunk

              I've done that here for several databases. It can be a lot of work so you'll need to look before you leap.

              Don't try it without getting Filemaker Advanced. The database Design Report can be very useful for tracking down issues related to replacing external data source references with local tables that you've either copied and pasted from or imported from the separate file. (Copy and paste requires advanced and only copies the structure, not the data. Import with the new table option selected in the source table drop down works with both Pro and Advanced and imports the data along with the field definitions.)

              Some items can be easily brought into the new merged table file with copy and paste or Import, but others have to be recreated by hand. The order in which you bring over scripts and layouts can save you a lot of time and effort. I found the following order saved a lot of time spent reconnecting layout buttons to the correct script or fixing broken script steps that couldn't find matching layout or table Occurrence::Field names during script import:

              Import or paste the table for the layout first.

              Recreate by hand the needed relationships and make sure that all table occurrence names match those used in the original, multi-file version.

              Fix any calculation fields that were encapsulated in comment brackets /* */ during import. (This happens when the calc refers a related table occurrence that doesn't exist in the new file.)

              Create a blank layout with exactly the same name as the orignal and refer to exactly the same table occurrence in show records from.

              Open the oringal file, enter layout mode, select all layout items, copy them all to the clip board.

              Recreate any value lists used by the layout or used by scripts peformed by buttons on your layout.

              Return to the new file and layout. paste. Don't try to fix any problems with layout design/scale etc. You'll delete all this and paste again in a few moments.

              Import all the scripts performed by buttons on this layout.

              Delete all the objects from your new layout.

              Now resize layout object parts and add any that are missing to exactly match the original layout. You can use the position tab of the inspector to see and modify the height of each layout part. (You can click the units next to the position/size boxes repeatedly to change them into pixels.)

              Now copy all layout objects again and note the Top and Left position values.

              Paste everything into your new layout and note the top and left position values here. If the are different, leave all the newly pasted items selected and edit the top and left values in these boxes to reposition them to accurately match the orignal.

              Now repeat this for every table and layout you want to merge with checks of the Database Design report for "missing" and "unknown" entries to make sure that you haven't broken anything.

              BTW, after I finished doing all of this, I learned that there is a third party product called FMMigrator that is supposed to make this much easier to use. I haven't used it so I can't recommend for or against using it, but I'd check it out before trying to do this manually.

              • 4. Re: Portals and value lists from external tables
                jayGamel

                Phil,

                 
                Thank you for a sobering look at table migration. Considering the problems I built into the original, I think I'm going to be better off constructing a new multi-table file from scratch and importing the raw data I need to make it fly. There aren't a whole lot of layouts I need to manage this DB, and they can all be improved on in FM11 (they were built in 6) using new tools and techniques, and probable a better learning experience for me with 11. 
                 
                And I do have advanced, thanks.
                 
                a question: I use some tables, like states, zips, local cities, in several different dbs. Do you include them with a new db or keep them external, serving several? If there's a discussion on pros and cons of this, I'll be happy to read it.
                • 5. Re: Portals and value lists from external tables
                  philmodjunk

                  It's definitely a case of balancing trade offs as there are pros can cons and experienced developers will give you differing advice on the subject. Much depends on what you mean by "Several different dbs".

                  What makes this a collection of databases rather than one database system of separate files?

                  Multiple files can make password management a real pain unless you use server based authentication. Getting scripts in one file to interact correctly with scripts in another file is a more complicated solution. Deploying updates now involves managing imports for multiple files in some cases.

                  On the other hand, if you make a small change to the table in one file, you can just update the one file with a single update. If you have all your tables in one file, you have to import all data from all the tables, updating all the serial number fields--a much slower process than a single table/file update.

                  One alternative is called the data separation model. You have two files. File 1 contains all the data tables and File 2 is your interface file and it stores all layouts, scripts, value lists needed to provide the user interface. It has table occurrences that link to the actual tables in the File 1. (the "back end" or "data file".) All needed interface relationships are defined between table occurrences in the interface file. The only relaitonships you need in the data file are any needed in order for a calculation field to evaluate correctly. The advantage here, is that most updates are likely to be interface updates. Interface updates now simply require swapping out the old file for the new copy without any importing of records required.