5 Replies Latest reply on Mar 19, 2015 9:13 AM by JoshuaGagnon

    Relationships

    JoshuaGagnon

      Title

      Relationships & Mirroring Records

      Post

      Hi Everyone,

       

       

      I am pretty new to Filemaker and while i've been doing OK (thanks in part to this forum!) setting up our new database after taking over a position that had not updated the database records or the software for almost 5 years (it was a mess) I'm once again stuck.

      I work for a year-round education program that works with students in schools. Our "main" database is entitled UBData that has our student contact information as well as information we need to report in our annual performance review. What I am working on now, is creating a second database for our summer program.

      I'd like to create a relationship between the UBData and Summer2015 Databases where I can pull all relevant personal information (name, address, phone, etc.) from UBData into Summer2015. In UBData I have a studentIDKey that is assigned when a new record is added. I'd like to link the two databases using the studentIDKey and grabbing information for students if the field "Summer Status" is "Active."

      I realize this is a large question and I have tried to search the forum and use the "missing manual" that we have but it still seems to be over my head. Thanks.

        • 1. Re: Relationships & Mirroring Records
          philmodjunk

          You've set what you like to do, but not what parts of this you can't get to work.

          Is your summer database in a different file from the first? Or are these two tables in the same file?

          Do you know how to link two tables in a relationship?

          Are you having problems setting up the layouts or in populating the new database table(s) with data?

          • 2. Re: Relationships & Mirroring Records
            JoshuaGagnon

            The summer database is a different file. We need to store & report on summer data each year (grades, classes attended, etc.) so i figured a separate file was the easiest route for that.

             

            I've created the relationship through File..Manage...Database...Relationships..Add Table and i've linked the studentIDKey and SummerStatus fields.

            What I can't get to work (don't know how, I suppose) is how to populate the data in the Summer2015 database with information from the UBData database. ALL of our participants for the past 10 years are in the UBData database (for reporting purposes) but I only want to bring over information on students who have their summer status marked as "Active" (and I want to bring over their name, address, contact information, high school, etc.)

            Does that make sense?

            • 3. Re: Relationships & Mirroring Records
              philmodjunk

              Actually, it's easier to set up in the same file by a small factor. With a separate file, you'll need to add one or more table occurrences to Manage | Database | Relationships with external data source references to tables in the UBData file. You'd do this by going to Manage | Database | relationships and clicking the button at the bottom left hand corner of the window. This opens a dialog for a new Tutorial: What are Table Occurrences?. You can select "add FileMaker Data source" from the drop down in this dialog. This opens another dialog where you can find and open the original database file. This then presents you with a list of tables found in that other file and you can choose one for this table occurrence. Once you have done that, you can link your current file's table occurrences to this new one just as though the table it represents is part of the current file.

              To populate the table, you can perform a find in your original file for all your "active" students. You can then leave this file open and go to your new file and use Import Records to import data from this table into the new file. But if you set up the table occurrence with the external data source as described in the first paragraph, you only need to import the student ID's as you can use the relationship to access their existing records from the original file. This can be useful since a change in contact info can be recorded once and yet appear up to date in both databases this way. (And if you used a table in the same file, you can still use import records the same way.)

              • 4. Re: Relationships & Mirroring Records
                philmodjunk

                Hmm and BTW, you don't necessarily need to add any new tables or files to do what you want. You might just set up layouts in your existing file to do what you want and just perform finds to limit the records shown to those for students marked as participating in the summer program(s).

                • 5. Re: Relationships & Mirroring Records
                  JoshuaGagnon

                  Hi Phil,

                   

                  Thanks, that worked like a charm. I was missing how to import, but the way you put it made it much more clear!

                  One last question, if you have a moment. I worked through finding the way for a record to be deleted from my summer database if/when they are deleted from my UBData database. Is there a way for the summer database to automatically add a record when they are added to my UBData database? Right now they relationship hinges on the studentID - but I would only want the student to be added to the summer database if, when entered, there summer status is "active" (some students join us in the fall of each year and therefore are inactive for this current summer session).

                  Thanks!