3 Replies Latest reply on Mar 5, 2013 2:41 PM by philmodjunk

    Multiple tables within a file/Linking formula-driven cells in different tables

    JohnFleury

      Title

      Multiple tables within a file/Linking formula-driven cells in different tables

      Post

           I just downloaded the trial for FileMaker Pro today and am still learning the basics, but there are some techniques I need to get the hang of before anything else.

           The first is trying to figure out if it's possible to make and contain multiple tables within a single file.  I'm trying to import and recreate an Excel file that has multiple worksheets within it, and things will be a lot easier if the database file in FMP can do something similar.  Importing each worksheet one at a time, each one appears below the previous one on the same table.  Is it possible to give each one a unique table, but have them all be contained in the same file?

           The other, more important factor is linking the data in specific cells to other cells.  The file I'm trying to recreate starts out with an inventory list containing several items and their prices, followed by individual sheets that focus on different clients, and the number of items our company uses and their total cost.  I'm hoping I can somehow connect the cells for each item's price on the client sheets back to their equivalent on the inventory list at the beginning, so that whenever prices for items change, all we have to do is edit the inventory list, and the cooresponding cells will automatically update.  Is this possible?

           One last thing: How do you apply formulas to cells, if you can at all?  The Excel file already has them linked to certain other cells, both making use of multiplication and division.   Is it possible to recreate these formulas in FMP, or even copy them automatically from the Excel file?

        • 1. Re: Multiple tables within a file/Linking formula-driven cells in different tables
          philmodjunk
               

                    The first is trying to figure out if it's possible to make and contain multiple tables within a single file.

               Since the release of FileMaker 7, that has been a standard feature for Filemaker databases. Go to Manage | Database | tables and you can create as many tables as you need in a single file.

               

                    Importing each worksheet one at a time, each one appears below the previous one on the same table.  Is it possible to give each one a unique table, but have them all be contained in the same file?

               Certainly possible, and there is more than one way to do that.

               Option 1: Use Import Records | File and select "new table" as the target table. You can then import your records, one worksheet at a time into different tables, each row of data in the excel worksheet will be a different record in the new tables, column names can become field names in the new table.

               Option 2: Go to manage|Database|Tables and define the tables and fields you want in advance of importing the data. FileMaker will automatically create a new layout for each new table. When using Import records|File to import this data, first select the layout that corresponds with that table. With this approach, you can select which field in your defined table will receive a given column in the excel file.

               

                    I'm hoping I can somehow connect the cells for...

               Manage | Database | Relationships can be used to link the tables in relationships. The "boxes" found on the relationships window are "Tutorial: What are Table Occurrences?". You can create additional table occurrences that refer to your tables as needed to define the relationships that you need. What happens next will depend on the structure of your tables and now you need those relationships to work.

               

                    One last thing: How do you apply formulas to cells

               In FileMaker, we call them "fields" or possibly "columns", not cells. You cannot import the calculation expressions from the spreadsheet--only the current value of each cell. The calculations in Excel are simply too different in syntax and based on too different a basic structure to the files to be something that can be imported. But you can go to Manage | Database | Fields and add as many calculation fields as you need. You can also define data fields to have an auto-entered calculation. Learning the differencese between these two options and how to choose the correct option in each circumstance will be one of the important things to learn as you get your FileMaker Database to do what you need it to do.

               A final comment:

               Bringing data from excel into FileMaker is a very commonly performed task, but Spreadsheets and Relational Databases are two very different applications. If you try to simply replicate the look and function of your spread sheets in Filemaker, you may have a very difficult and frustrating time of it as FileMaker can't easily be set up to do things that are simple in Excel and Excel can't do some things that are very simple to do in FileMaker.

               It's best to fully understand the capabilities of both applications and then exploit FileMaker's strengths while minimizing the weaknesses. This may produce screens that look very different from Excel but either get the same job done or achieve tasks nearly impossible in Excel.

                

          • 2. Re: Multiple tables within a file/Linking formula-driven cells in different tables
            JohnFleury

                 Thank you.  Is there a list anywhere of field calculations that can be typed in?

                  

            • 3. Re: Multiple tables within a file/Linking formula-driven cells in different tables
              philmodjunk

                   That's pretty much an endless list.

                   Think of how many different calculations you could construct with just the basic four math functions. And there are many different functions and more than just the basic math operators that you can use.

                   But you can research functions in FileMaker Help.