3 Replies Latest reply on Mar 30, 2009 3:08 PM by philmodjunk

    Importing Databases, Creating Relationships - beginner!

    yawa

      Title

      Importing Databases, Creating Relationships - beginner!

      Post

      Hi All,

       

      I'd really appreciate some help with a very simple problem (I'm a beginner!)...

      I need to import a table from one database into another to combine information into one database.  I am importing a "project management" table into a "content management" database, and I selected file -> import records -> from file -> filemaker and then selected the "project management" database.  However, when I import that info, I'm not able to view it in the same way that I was able to when it was a separate database.  How do I keep the layout and info the same? Can I switch between views of my "contact management" table and the imported "project management"? When I select edit layout -> manage database -> tables, I am able to view that there are two tables in this database now ("contact managment" and "project management", but in browse mode, I can't see any of the imported "project managment" info. How do I switch between these two tables?

       

      Also, I'd like to define a relationship between the two tables that would allow me to view projects sorted by "company" field (in the contact and project tables), even though the "contact management" database I'm importing info into in is organized by "client" rather than "company" ("company is just a field, not the primary category the records are categorized in. (The logic behind this is that projects are managed by "company name" not "client name" as we have several clients at the same company, so projects mustn't be linked to contacts).

      What I've attempted to do to accomplish this was to select edit layout -> manage database -> relationships. I can see the "project management" table there, and I clicked and dragged a line from the "company" field in the "contact managment" table to the "company" field in the "project management" table. Is this how I would accomplish the previous paragraph's goal?

      For some reason, I still can't find the project management table anywhere (it's just present when I'm in  -> manage database) but it's invisible in browse mode....making it so I can't test my newly created relationship. Where did the table from the imported database (projects) go in the contact database? (Note: there are no records in the "project" table yet, just in the "contacts" table.)

       

      Please let me know if I wasn't clear, or if I'm doing something wrong - any help would be greatly appreciated!

       

      yawa


        • 1. Re: Importing Databases, Creating Relationships - beginner!
          philmodjunk
              

          Congratulations, You've gotten off to the right start in merging two databases into a single file with two tables. You now need to create layouts that will enable you to see and work with the contents of these two tables.

           

          in browse mode, I can't see any of the imported "project management" info. How do I switch between these two tables?

          In the top left corner of your screen is a drop down menu labeled "Layout:" All layouts that exist in your file can be selected from this menu. When you imported your second table, a new table displaying the contents of this table should have been automatically created and this layout will have the same name that was given to your table. You should be able to select either your original layout or this new layout by selecting from this menu.

           

          Also, I'd like to define a relationship between the two tables that would allow me to view projects sorted by "company" field (in the contact and project tables), even though the "contact management" database I'm importing info into in is organized by "client" rather than "company" ("company is just a field, not the primary category the records are categorized in. (The logic behind this is that projects are managed by "company name" not "client name" as we have several clients at the same company, so projects mustn't be linked to contacts).

          What I've attempted to do to accomplish this was to select edit layout -> manage database -> relationships. I can see the "project management" table there, and I clicked and dragged a line from the "company" field in the "contact management" table to the "company" field in the "project management" table. Is this how I would accomplish the previous paragraph's goal?

          You are correctly creating a relationship that links your two tables. However, changes to relationships do not automatically change what is displayed in a layout. You will need to either modify your existing layouts or create a new one inorder to display data from more than one table on the same screen.

           

          On a side note, I'd recommend not basing your relationship on the company name field. I'd use a company number field instead. Company names could change at any time and if they do, you'll have to update the company name field in both tables in order to change a company name and yet avoid breaking the relationship that links your tables. I wouldn't worry too much about this issue until you can create the layouts you need. You can add ID number fields and update your relationships to use them once you understand a bit more about how to design things in FMP.

           

          To see which table is the source of records for a given layout:

          • 1. Enter layout mode.
          • 2. Select Layouts | Layout Setup... To see what table is the source of records for your layout. If you want a different table or table Instance to be the record source, select a different one here.

           

          To add fields to a layout,

           

          • 1. Enter Layout Mode
          • 2. Select the field tool at the top and drag it onto your layout.
          • 3. A dialog box will open where you can select a field. A menu at the top of this dialog will allow you to select fields from your related tables. Select the table and field you want, then click OK to select for the field you have just added to your layout. You can resize and format this field as needed.

           

          You will probably, want to display multiple project records while viewing information about a specific company. A typical method is to use a Portal. I suggest reading the help file on portals, experimenting with this feature and then posting back to this forum if you encounter problems.

           

          Hope that gets you to the next step.

          • 2. Re: Importing Databases, Creating Relationships - beginner!
            yawa
              

            PhilModJunk:

             

            Thanks much for the assistance. I can now switch between viewing both tables, and will read up on using a portal instead of a relationship to establish the connection from company to project, while still retaining multiple clients at each company.

             

            The last question I have is regarding the loss of all my formatting when I imported the "project managment" database into the "contact managment" database as a table. I had tabbed, colored, and organized all the fields I wanted, yet now that they're imported as a table - I lost all my formatting. Is there a way to retrieve that info, or do I need to re-create that entire table?

             

            Thanks much again!!!!

             

             yawa

            • 3. Re: Importing Databases, Creating Relationships - beginner!
              philmodjunk
                

              The Formatting you are referring to is also known as a "layout". You can copy and paste layout elements from one filemaker pro file to another much like you would copy and paste text from one word processor file to another.

              1. In your new file, enter layout mode and select New Layout. Select the same exact table as the table from your old file. The table names must match letter for letter.
              2. Select the layout options that most closely match your old file's layout.
              3. Do not select any fields to be placed on your layout.
              4. Click OK to return to your layout.
              5. Switch to your Old file and Enter Layout Mode.
              6. Press Ctrl-A (windows) or Command - A (Mac) to select all.
              7. Press Ctrl-C or Command-C to copy everything to the clipboard.
              8. Switch back to your new blank layout.
              9. Press Ctrl-V or Command-V to paste everything to your new layout.
              10. Adjust the layout elements you have just pasted and the size of any layout parts to match your original file's layouts.
              11. Any buttons that you have pasted will not work until you reconnect each to a script in your new file.
              12. Double-click each button and select the same settings as they did in the original file. (You can double-click each button in your original layout to compare settings.)
              13. If the button performs a script in the original file, you will need to import that script from the original file and then reconnect the button to it.