6 Replies Latest reply on Aug 6, 2014 10:06 AM by philmodjunk

    Importing to two tables/sharing fields across tables

    kleink

      Title

      Importing to two tables/sharing fields across tables

      Post

           Hi!

           I have four tables in one database file- a main one for projects and three others that are specific to departments working on the projects.  The parent table contains the data pertaining to a project, i.e. due date, contact info, etc., while the other three tables have fields for the departments along with the fields from the parent table (project name and due date).  Is there a way to import into the child and parent table at the same time?  If not, a way to write the fields from the child table to be the parent table fields so when I import into the child that the parent fields are populated?  I tried to do a calculation, i.e. Table A::Project = Table B::Project, but it doesn't work- I'm sure I'm missing something.

           Any insights would be appreciated, thanks! 

        • 1. Re: Importing to two tables/sharing fields across tables
          philmodjunk
               

                    Is there a way to import into the child and parent table at the same time?

               You would need to do two imports.

               But let's back up a second. What folks reading your post can't tell is: What you are importing and why?

               And I wonder if your relationships are set up correctly here.

                  How do you match a record in project to records in the other three tables?

                  Is the relationship one to many, many to one or one to one? (one project record to only one record in each department table, one project to many department table records or ???)

                  And how do the records in each department table differ from each other?

          • 2. Re: Importing to two tables/sharing fields across tables
            kleink

                 Good point- sorry about that.  The tables are connected via a Match ID field, a unique serial number generated upon creation, and the relationship is one project record to one corresponding record in each department table (so everyone can work on entry "1234" in their individual tables but there is only one "1234" in the project table).  We decided to do one database with multiple tables because several departments work on a single project but the departments don't need to share information besides key fields like the project name and deadlines- it would have been built as a flat file but we didn't want to have over 300 fields on a single table. 

                 So an example of an import would be our finance department needs to update records on purchases for Project "A" but when they import, the "Project" field doesn't get any data because it is from the main table.

            • 3. Re: Importing to two tables/sharing fields across tables
              philmodjunk

                   But why are they importing to update the table?

                   In any case, it sounds like you have a "Project Field" in the Finance table field that should be removed from that table. Replace it with the Project Name field from your Projects table. Any fields that show data from the parent record in projects should be fields from the Projects table that have been added to the child layout. They will automatically display data from the projects record linked to the current "department" record.

                   But perhaps you are using Import records to create new records in the department table?

                   In that case there may not be any data in your import file that automatically links the new records to the correct record in Projects. if you can't import that value, you'd have to update any new records created by the import through other means.

                    

              • 4. Re: Importing to two tables/sharing fields across tables
                kleink

                     Yes, the parent field is on the child layout.  I was hoping there would be a calculation or something to populate a child field based on the parent but figured there wasn't. What would be the new means? How can I import into two tables?

                • 5. Re: Importing to two tables/sharing fields across tables
                  kleink

                       Also, we are importing to update the table because wifi is not available yet in all locations so inventory is often done off the server using FileMaker Go (on device file instead of host).

                  • 6. Re: Importing to two tables/sharing fields across tables
                    philmodjunk

                         Please note that I have no idea how your data is set up nor how you have defined the relationships

                         If you are importing from another FileMaker Pro (or GO) file, then the matching value should be one of the values imported along with the other data.

                         If you have this relationship:

                         Projects::__pkProjectID = Finance::_fkProjectID

                         (For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained)

                         Then, when importing data into the Finance table, you should also import the matching _fkProjectID field and then your fields from Projects that you've placed on your Finance layout should then display the correct data. (and this would also be true for fields from Finance placed on the Projects layout.)