4 Replies Latest reply on Oct 31, 2012 10:30 AM by philmodjunk

    Associated Tables

    VSP

      Title

      Associated Tables & Layouts

      Post

           Hello,

           Can someone please explain or direct me to a simple explanation of how Associated Tables and Layouts works so that I can import all of my tables into another file? I am able to use and display all of the fields and records needed on my different layouts but when I try to import records into a Cloned file (that has been slightly modified with additional fields, revised layouts) I am limited to only import one of the tables from the original file.

           Example:

           I have a Demo file with dummy records that is used for public viewing. I also make and test design changes in this Demo file. I have another file that contains Actual client records. When I create a clone of the Demo file (no records but with the revised fields and layouts) and try to import Actual client records into that Clone I am not able to import all of the tables.

           In both the Clone and Actual file, three of my tables are Demographic, Notes and Transcript which are all related by a primary key _pk_ESA_ID in the Demographic table and secondary keys _fk_ESA_ID in the Notes and Transcript tables. Three of the Layouts are Demographic, Notes and Transcript and all are Associated with the Demographic table.

           I have fields from the Demographic table (name, address, etc.) displayed on all three Layouts, but am not using a portal to do so. I input records onto the Transcript and Notes layouts while displaying the Demographic fields on the same layouts. Also, I display Transcript table fields (GPA, Graduation Year, etc.) on the Demographic layout. However, when I try to import records from the Actual file to the newly revised Clone, I can only import Demographic records. During Import Field Mapping, I am not able to select the Notes and Transcript Tables to import those records; even when using Manage Database, those tables are not selectable.

           I suspect my problem is that all of my layouts are associated with the Demographic Table but am not sure...So, I guess my questions are:

             
      •           When fields from different Tables are on the same Layout, what determines which Table to associate with that Layout?
      •      
      •           Since my tables are related using primary and secondary keys, must I use portals to display fields on different tables? I didn't use portals because I couldn't get the layouts to appear user friendly.

            

           I've read the FM online instructions and searched this and other Forums but have not found an explanation for guidelines/logic for associating tables with layouts (at least that I could understand). I've used FMProAdv 11 for about a year and have less than 2 yrs experience with simpler databases.

           Thanks in advance. Any help is greatly appreciated!

        • 1. Re: Associated Tables & Layouts
          davidanders

          http://forums.filemaker.com/posts/bd5b545c88

          Title:  Importing
          Post:  is there a way we can import data in the fields which are from different table?
               I've table A and layout A that shown records from Table A.
               I've some fields and also portal tool that fields are from Table B.
               I am wondering if I can import my excel fields into all the fields including fields from table B?
               now it's grey color and can't import.

                

          Excel import into 2 tables

          <SNIP>You have two tables into which you want to import data, but data in each table both come from the same excel spreadsheet?
                              How is the data organized in this spreadsheet? Is the contact info repeated over and over again for each checklist entry?

          In general, to import data into two tables requires importing the data twice, once into each table. In FileMaker, this requires importing the data for contacts into a layout based on contacts and then to import checklist entries, you do a second import records, but from a layout based on checklist instead of contacts.
          There will be issues with this process that you'll need to resolve depending on the exact structure of the data in your source (Excel) file to make sure that you don't import duplicate contact data.

                

               I have no great Table Theory Links.  Most are very basic.

          • 2. Re: Associated Tables & Layouts
            philmodjunk

                 Table Occurrences are the "glue" that link a layout to a specific data source table. And then they also determine how data in other layouts may be accessed from the "context" of a given layout. Thus, this link on table occurrences may help shed some light on the subject: Tutorial: What are Table Occurrences?

            • 3. Re: Associated Tables & Layouts
              VSP

                   Thank you for your replies. I created the example provided by Phil along with reading a FM-7 article from several years ago about Tables and Table Occurrences and while I have a better understanding, I could not get my database to work as desired.  I created additional Table Occurrences (duplicates with different names) but could not get the records to line up across the layouts. Each time I open a layout, I have to re-sort the records. Also, some of the records did not appear at all. The tables are related by a single unique identifier and some fields are is 2-3 table occurrences away on my relationship graph.  Are there any other suggestions or examples with more tables and layouts that show the use of table occurrences?
                   Thank you.

              • 4. Re: Associated Tables & Layouts
                philmodjunk

                     You might want to take a closer look at that tutorial I recommended. If you have multiple table occurrences with a layout for each one, each has it's own current record, sort order and found set, for each window for this file that you have open. Thus each layout can show a different set of records sorted in a different order and changes made to the found set/sort order/current record settings on one layout do not affect what you see on the other.

                     If you want to see the same records with the same current record and sort order on two or more layouts, specify the same table occurrence in Layout Setup | Show Records From for each.

                     Warning: if you change this setting for an existing layout, you'll probably need to double click each field and each portal on the layout to update the table occurrence references that were set to work from the context of the original table occurrence.