11 Replies Latest reply on Jun 16, 2014 2:23 PM by philmodjunk

    Creating a relational database

    LeoIstuse

      Title

      Creating a relational database

      Post

           I'm very new to Filemaker Pro (version 13.0v3), but I had some experience creating databases long ago. When I open 'Manage Database' there's only the first table I opened in the 'Table'-tab. In the 'Fields'-tab all fields are there, but in de 'Relations'-tab all the related tables are depicted, because I used the 'Add a table'-button there. However, I'm not sure it will work this way.  This window is called 'Manage Database' with the name of the first (and only) table.

           How can there be more tables in this database? (BTW: is the term 'file' in the manual referring to 'database', or is it something else?).

           Thanks for your input on this.
           Leo

        • 1. Re: Creating a relational database
          philmodjunk

               You don't have tables in Manage | Database | Relationships. You have Table Occurrences. See this tutorial: Tutorial: What are Table Occurrences?

          • 2. Re: Creating a relational database
            LeoIstuse

                 Thanks for your reply. Interesting tutorial, learned quite a few things.
                 I think my question was not clear. Why can all occurrences be seen in the 'Relations' tab, but not in the 'Tables' tab? This tab says "A file can contain more than one table.". Yes, but how? Can I put already made tables here?

            • 3. Re: Creating a relational database
              philmodjunk

                   Table occurrences are NOT tables. They may have identical names at times, but they are different objects.

                   Look again at your tables tab. you should find that you have one table listed on the left and many table occurrences listed on the right.

                   Note the circled text in this screen shot.

              • 4. Re: Creating a relational database
                philmodjunk

                     And in this screenshot, I've circled the same two table occurrences in red. Note how I've color coded table occurrences so that those with the same data source table are the same color.

                • 5. Re: Creating a relational database
                  philmodjunk

                       And using a bit of careful image editing, this shot shows that both of these table occurrences have the same source table, FMP Bugs the table I circled in Red on the left in my first screen shot. These pop ups show when you hover the mouse over the arrow in the upper left corner of a table occurrence box.

                  • 6. Re: Creating a relational database
                    LeoIstuse

                         Thanks again for your answer and patience.
                         Again I don't think you answered my question, but then again: I'm the newbie here...
                         In this picture I sent hereby, you can see my database management  screen. I've converted my Excel NAW table (or 'file', or 'database'), but now I cannot go further to add my other related databases (also in Excel). That is to say: I can't figure out how to do that. Is it something wrong or stupid I try to do?

                    • 7. Re: Creating a relational database
                      obospieler
                           

                      How can there be more tables in this database? (BTW: is the term 'file' in the manual referring to 'database', or is it something else?).

                      On a Mac, you can create new tables by Clicking on File->Manage->Database... Then clicking "Create" in the dialog box (located very near the bottom right corner).

                           For most general and intermediate users, in FileMaker, the term "File" and "Database" are considered to be synonymous (although at the more advanced users this isn't necessarily true).

                           On a basic level, a person would create two tables for two separate things to be related to each other.

                           Upon first inspection of the screenshot you provided, you won't need to "add other related databases" but in fact you will want to add additional tables your existing file/database.  

                           If a person wanted to create a FM Database with multiple tables from an excel document, that person would need to ensure their excel file had multiple tabs where each tab represented a desired table. http://www.filemaker.com/help/html/import_export.16.19.html

                           Does that help at all?

                            

                      • 8. Re: Creating a relational database
                        LeoIstuse

                             Thanks. Indeed one would create two tables for two separate things to be related to each other. I'm desperately trying, but except for the "create" button (to start a completely new table), I cannot get it done from an Excel file. I did had seperate Excel-files, but as you suggested I put the two sheets together in one Excel-workbook. With the explanation via the supplied link, I couldn't import both tables in a new FM database. In the "import_export 16.19.html"-link I didn't find anything that took me further than importing one sheet at a time. In FM Quickstart I can click on the 'Convert an Existing File' option, but pointing to the Excel-workbook, it forces me to choose one of the contained sheets. Could someone please let me know how I could put more tables in one FM database? Preferably by importing Excel-workbooks/-sheets?

                        • 9. Re: Creating a relational database
                          philmodjunk

                               Given this statement:

                               

                                    but in de 'Relations'-tab all the related tables are depicted, because I used the 'Add a table'-button there.

                               It appeared that you were creating multiple table occurrences but no new tables. The most likely scenario was that you were creating multiple occurrences of the same table. Until I could see your screen shot of the relationships tab, there was nothing to show otherwise.

                               Please upload a screenshot of your relationships tab. The only other way that you could get multiple occurrences there and still have the screen shot you have just uploaded on the tables tab would be if you were adding external data source based references to tables in other files. If that's the case, the other table occurrence boxes on your relationship tab will have names shown in italics.

                               But if you only have one table occurrence box there at the moment, then perhaps you want to import tables of data from other Excel files--something that you only mention in your last post. To do so, you can use Import Records with the New Table option selected as the "target table" to import the data in the excel file and also create a new data source table in your file.

                               (and File means "file", just like you can have multiple MS Word files for different documents, you can have different FileMaker Files, each with different combinations of tables defined in them. "Database", is a much fuzzier term as that can refer to a a single file with a single table, a single file with multiple tables or multiple files with multiple tables.)

                               To create a new table "from scratch", you can also enter a name for the new table on the tables tab, click create, then click over to fields to add field definitions, followed by using the relationships tab to link the new table's table occurrence to others in relationships.

                          • 10. Re: Creating a relational database
                            LeoIstuse
                                 

                                      Please upload a screenshot of your relationships tab. The only other way that you could get multiple occurrences there and still have the screen shot you have just uploaded on the tables tab would be if you were adding external data source based references to tables in other files. If that's the case, the other table occurrence boxes on your relationship tab will have names shown in italics.

                                 OK, a screenshot isn't necessary, because what you describe is exactly what happened. I thought the button "Add a table" in the 'Relations' tab referred to a table. Now you explained it is just an occurrence and that's why this 'table' isn't in the 'Table' tab.

                                 

                                      But if you only have one table occurrence box there at the moment, then perhaps you want to import tables of data from other Excel files

                                 Yes, I thought according to the manual, there are different ways to create tables: one is to convert another [database-] file, like Excel. Is this really so different from creating a new file by hand? Apparantly yes.

                                 

                                      To do so, you can use Import Records with the New Table option selected as the "target table" to import the data in the excel file and also create a new data source table in your file.

                                 With searching the help file for the mentioned Import Records I finally found out how to do it.
                                 In the beginning I was searching for a simple way to create the needed tables, creating the needed relations and when everything would be ok I could fill it up with data (or import this from my Excel files). Now I understand how to do this in FM.
                                 As I said earlier: I'm very new to FM, so I have to get used to the naming of things and finding my way through the menus. You showed me the way and now I'm able to crawl around. Maybe in a few months I can stand up.

                                 Thanks for being so patient with me.
                                 Leo.

                            • 11. Re: Creating a relational database
                              philmodjunk
                                   

                                        Yes, I thought according to the manual, there are different ways to create tables: one is to convert another [database-] file, like Excel. Is this really so different from creating a new file by hand? Apparantly yes.

                                   The key difference is that drag and drop takes the entire Excel file and produces a single FileMaker file with just one table. If you plan on using multiple Excel files to produce multiple tables in the same file, this method only works for the first Excel file. After that, you have to use other means such as Import Records with "new table" specified as the Target Table. You also can't use Drag and Drop for more than the first Excel file if you have multiple Excel files and you want to import the data from several of them into a single table--which is a very common thing to need to do in this situation. In that case, you would also use Import Records, but not with the New Table option as you would want to use the table that you have already created with the first Import or drag and drop.