8 Replies Latest reply on Oct 21, 2011 9:07 AM by philmodjunk

    Creating mutliple "layers" to my database



      Creating mutliple "layers" to my database


      I have a few databases that I need to have accessed in a particular way. They are being shared via IWP where there is a list of the various databases on the homepage and when you click on one it takes to to the layout of that database. I would like to be able to have layers or steps of how people can access different parts of the database. For example, I have a document database where each record/document is classified as a different class or type of document and I want to be able to have the choice to either access the entire table with every document OR choose to view a layout that is distinguished by class/type of document. I know you can do this by performing a search of the database but was hoping there is a way to create a drop down list that gives options of which class to pick and then takes you to a layout of just those documents. Maybe something that looks like this:

      Homepage, list of databases, Click on "Documents Database" --> links to the next page with a category called "Document Class" and a drop down list/list of links of each option of Document Class, Click on an option "Contracts" for example --> links you to a layout of the database that only includes presorted documents of that Document Class

      Another way I would like to implement a similar concept is to have the Homepage list different databases to click on but be able to have layers in which you can have databases within databases. What I mean by that is to have links such as "Documents Database" and "Contracts Database" listed on the homepage but when you click on them it takes you to another layer that lists groupings of databases under that title. I want to be able to organize my databases in a way that you can quickly sort through the databases and records you don't want to find the ones you do want more easily. One way I have thought of is to create a database within a database where you have a container field in one database that contains the link to the other database but since I am setting this up via IWP this doesn't work because whoever tries to open the link must have FM Pro, which is what I am trying to avoid by using IWP. Also, I thought of using relationships but for two databases that I want grouped under the same heading, there are no relationships between fields within those databases so I cannot link them in that way.

      I realize this may be more on the web developing side of things but it seems like this shouldbe possible. I hope I explained this well enough for someone to be able to help so if I didn't and you need me to clarify anything let me know.

        • 1. Re: Creating mutliple "layers" to my database

          I have a document database where each record/document is classified as a different class or type of document

          Do you have a single table where each record is s different document and a field in this table identifies the type of document or do you have separate tables for each type?

          Either way, you can select a value in the drop down and then click a go button placed next to it. The button's script then uses the value selected in the drop down field to determine what layout to switch to and what records to pull up on it.

          in which you can have databases within databases.

          "Database" is a very fuzzy term with Filemaker. A database can consist of one table or many tables. The tables can be located in one file or many files. Links between these tables can be set up both for tables in the same file and in separate files.

          Thus, whether you have one file or many, you can set up a single database with menu layouts where you drill down the the specific layout and records that you need. This same basic approach also works when you publish to the web as long as you don't use script triggers.

          • 2. Re: Creating mutliple "layers" to my database

            Yes, I have a single table where each record is a different document and a field in the table specifies the type of document. Would it be better/easier to arrange it the other way and have multiple tables? If so, how can I do that easily?


            Also, "database" is a fuzzy word. From your answer I am also a little "fuzzy" on what you call a file. I have inserted files into container fields but I assume that is not what you mean by file, can you clarify?

            I am unclear about how to make multiple tables in one database but I think that would answer both of my questions. I know how to make multiple records and databases and layouts but I cannot figure out how to create more tables. I am sure this is probably the easiest part but I am very new to this program.

            Thank you!

            • 3. Re: Creating mutliple "layers" to my database

              I suggest keeping them in a single table, not dedicated tables. This usually is much easier to work with.

              I am talking about FileMaker files that have .fp7 on the end of their file names. Each time you select "New Database" or "Save a copy as..." from the file menu you create a new file. If you open Manage | Database | Tables, you'll find that you can define as many tables as you need that are part of the current file. If you click over to the Relationships tab, you can use the bottom far left button to add a new "table occurrence" box to this relationship window. If you then use the Add FileMaker Data Source option in the data source drop down in the dialog that pops up when you click this button, you can set up a reference to a table in another FileMaker file. This means you can use separate files for each table or put them all in one file and still get much the same functionality either way.

              Thus a "database" can mean many different things depending on how you design your system. To avoid confusion, I suggest avoiding the term 'database' and using "File" and "table" instead as they are more specific terms here.

              However you mix and match files, tables, and layouts in your system's design, you can do what you describe, but the details will differ with your design. You can put buttons on your layout that change to layouts to other tables in your current file, or a button can open a different filemaker file or it can perform a script in that other file so you have a number of different options possible that will all take the user from file to file and layout to layout as needed.

              • 4. Re: Creating mutliple "layers" to my database

                1) I now understand your previous advice so that is great but now I am having trouble with one key element to my set up. I have created multiple "files" prior to understanding that you can just create different tables in the same file and link between those (which is what I would like to do). I have it set up so that there is a button on one file that links to another file but I would prefer to have all of my tables in the same file and have a button on one layout that links to another layout. Since I didn't plan it out like that I am having some trouble. I imported the tables I want into one master file but the records and formatting do not import. Is there a way around this? I unfortunately spent a lot of time making the separate files the way I want them to be but now I don't want to have to redo them all when transferring them into the master file.


                2) Also, I am having some complications when publishing to IWP. First, my buttons that I created do not work. They work in my FileMaker program but not on the web. There are also some formatting issues when viewing online as well and I'm not sure why. I know that IWP has some limitations but I cannot find where/what those are.


                • 5. Re: Creating mutliple "layers" to my database

                  You can get the layouts pretty close, if not exactly the same, by going to your old file where you have the layout set up - go to edit layout - select all - copy  then go to your new file - create a blank layout based on the table you want - and paste - fine tune the positioning so it looks right.  Then double check that all the fields are assigned to the correct new table.

                  • 6. Re: Creating mutliple "layers" to my database

                    Expanding on Mark's advice...

                    Paste to a layout that has exactly the same text in Show Records From in Layout setup as is found in your original file.

                    Buttons that don't work in IWP may perform scripts with steps that are not comptible with web publishing. YOu can open a script in the script editor and use the compatibility drop down in the lower left corner to check a script over for incompatible steps.

                    Please note that you don't have to merge your tables in a single file. What you have described can be done with multiple one table files. Many developers prefer to keep to the one table, one file structure--but that structure is harder for "newbies" to work with. A button in file 1, for example, can perform a script in file 2 that switches to a specific layout in that file and uses select window to pop that file's window to the front.

                    • 7. Re: Creating mutliple "layers" to my database

                      Since I have already set up the database into one file with different layouts I will probably keep it that way until I learn more but now I am looking at doing a combination of that and your advice on creating a button in one fule that goes to another file. I am having trouble figuring out exactly how to create that link between files and was hoping you could expand more on the last part you mentioned - "a button in file 1 performs a script in file 2 that switches..." Before reading this I tried creating a button that would have the action of Open File which I linked to my 2nd file I wanted it to open but on IWP it does not do that. Only in FMPro does it open the correct file but when I go to the webpage online it does nothing. Then I read your comment and tried to have the button Select Window as you said but it then asks for a calculation, which is throwing me off a bit. Obviously I'm missing something so any advice? Thanks.

                      • 8. Re: Creating mutliple "layers" to my database

                        I'm not sure how well this will work with IWP. You'll have to experiment and see...

                        In File 2, create a script such as:

                        Go To Layout [//specify a layout]
                        Select Window [Current Window]

                        In File 1, create your button and select the Perform Script option. Click the Specify button and then pull down the drop down at the top of the specify script dialog that starts with "current file". If you see your file listed in the drop down, select it. If you do not, select the Add FileMaker data source option. This opens yet another dialog where you can find and open File 2. Either route takes you to a list of the scripts defined in File 2 where you can select the script in File 2.

                        Now try it out. In FileMaker, this script will open File 2 and bring it to the front. IWP systems have significant limitations when it comes to window control so I don't know if this will work in IWP.