6 Replies Latest reply on Oct 5, 2010 3:43 PM by WinstonChurchill

    Tables and Relationships



      Tables and Relationships


      I believe tables and relationships are something I should really know about if I'm going to use FM to it's best. But I'm trying to self teach myself FM without reading the manual back to front.

      My goal is a multi-tabbed layout (8 tabs on main sheet) each tab has at least 3 or more tabbed sheets within them and in some cases these second layer tabs have tabbed sheets within them as well. The purpose of the database is for data gathering and I would require only 1 record per job.

      I've created everything in the first 2 tabs of the upper tab sheet and have about 600 fields in one table. Tab 7 & 8, will have a 4 tabbed sheet within each and each of these tabs will have a 5 tabbed sheet within them and each of these tabbed sheets will have a further 10 tabbed sheet within them. So 200 pages under tab 7 and the same under tab 8.

      Each page would be identical, so it occurred to me that the easiest solution was to make a table with all the fields (about 30) that will appear in each of these pages and duplicate the tables for every instance.

      However as soon as I created a new table and added a few fields to the document, all of the fields that were related to the table to the first two tabs went missing.

      So what am I doing wrong, is it something I can correct or do I need to go back to my last saved version. What do I need to do to avoid this, is there a simple guide to tables and relationships, the tutorials and help sections seem to have this info spread out over the whole tutorial/help base and it seems like I'l need to read everything to get started.


        • 1. Re: Tables and Relationships

          > I believe tables and relationships are something I should really know about if I'm going to use FM to it's best.

          There's no "belief" about it. It's plain fact. FMP simply cannot be used successfully without understanding the basics of tables and relationships.


          > But I'm trying to self teach myself FM without reading the manual back to front.

          Uh huh. And how's that working out for you so far?

          You may not need to read every word of the FMP Help manual from start to finish, but you should at least read the sections about the basics of using FMP and creating/designing databases.

          I'm sure your eyes just glazed over, so let me repeat what I just said:

          Read the sections about the basics of using FMP and creating/designing databases.


          I'm no Filemaker guru, but I'll be honest, your idea of using such huge numbers of tabs, sub-tabs and sub-sub-tabs sounds like a nightmare in terms of database design. And 600 fields in one table?! Good grief! I would suggest that you need to seriously re-consider your database design from the ground up - after you read the first few chapters of FMP Help, that is. Or would you rather stumble along, blindly, wasting enormous amounts of time and effort, re-building your database over and over trying to get it right, and expecting others to help you out when you're too lazy to do a bit of reading beforehand?






          • 2. Re: Tables and Relationships

            Thank you for your reply, allow me to add a little more information.

            I have worked through the tutorials at beginners level in the VTC site (http://www.vtc.com/products/FileMaker-Pro-11-Beginner-Tutorials.htm) and a few of those for intermediate level. I have done some of the tutorials on tables but cannot find them easily again and there is 15 hours of video to work through to find it. I was hoping for some guidance as to which part of the tutorial or which part of the user guide might set me in the right direction.

            As for excessive fields, I don't believe I have a way around the number of fields I intend using, let me explain a little more:

            I'm an energy surveyor, I survey commercial property (workshops, hotels, factories, libraries etc etc) and record data about the property in terms of it's fabric, HVAC systems, solar panels, lighting etc etc. So for example in a room I need to record info about the heating device, the length of pipes, the type, wattage, luminance of lighting as well as the fabric in that room amongst many other things. To further explain, I need to record the length, height and area of a wall, it's construction (info about each layer, such as plaster, brick etc) what sort of conditioning is behind the wall, info about thermal bridging, U-values etc etc.

            In short there are about 15 types of data I need to record about each wall, each room may have say 10 walls. Each property might have 40 rooms on each of say 4 floors and then there is all the info about the heating, lighting etc etc. In a typical property I may need to record 3-5000 individual bits of data. Once I've finished my work can be audited by the government backed accreditation scheme and in order to comply with the audit the information I have collected must be in a prescribed format.

            The number of tabs are largely unavoidable I believe, the fields within the first couple of tabs are mostly unique, but as I say I'm now coming to a part where they are repetitive and feel I could use tables much more effectively. I have done this and lost the first relationship to the original table and don't know why. I'm only asking for someone to point me to the part of the user guide or tutorial that would tell me why. I have actually spent around 4 hours looking for this info so far.

            • 3. Re: Tables and Relationships

              A common "new to database" design mistake is to confuse the need for records that can be sorted/grouped into different categories with the need for different tables that are used to store completely different but related types of data. Thus, they start trying to define more and more tables--each with huge numbers of fields where they can instead greatly simplify their design by simply putting all or most of the data in one or a few tables with an added field or two that identifies the category into which that particular record falls. That appears to be at least part of your problem here.

              Another issue appears to be using tabs where layout naviation buttons that take the user to completely different layouts will work much better for you.

              Take that screen shot at the top of this thread.

              You have 6 rounded rectangles that frame a repeating set of fields. I'd suggest a portal for this, but you appear to have fields within the rectangle called Layers (and their thickness in millimeters) that form a list of repeating values and these should also be presented in a portal and you can't put a portal inside a portal. You can, however, create a list view layout that lists this data so that each of these rectangles is a different record in the same table with a field for IDnumber, name, age, description and total thickness and a related table of layers that has fields for IDnumber, LayerDesc, and Thickness.

              That's just one example of how proper table design with sound relationships can greatly simplify the design and function of your database.

              Try this for starters:

              Get rid of the file tabs. You have design requirements that make the tabs a poor choice for what you are doing. Replace the tabs with buttons that take the user to completely different layouts where you can set up table views, list views or Form views of the appropriate data to display what you need the user to see. You can attach scripts to these buttons that perform finds ans sort the found records to display them in a form that makes sense to the user. Wait on using tabs, until you have a better understanding of how FileMaker layouts work so that you can better choose when they make a good layout design option for you.

              • 4. Re: Tables and Relationships

                I'd sort of realised that I'd be better off with some buttons instead of all those tabs. I'm trying to learn different areas at the moment and was considering the button route while investigating custom graphics in a parallel project.

                I'm looking into changing the info in each rectangle to use a portal, however I've run into an issue which I'll start another thread about. Again I'd already considered a list of records instead of multiple 'rectangles' in one record, but I'm not sure whether I could get it to display with just one set of buttons/tabs at the top of the whole list rather than at the top of every record.

                • 5. Re: Tables and Relationships

                  In list or table view. You can put a row of buttons in the Header and they will stay put while you scroll through your records.

                  This is, in fact, a way to reclaim some window real estate by hiding the status area and replacing it with a simpler row of buttons and other layout objects that take up a fraction of the same space. I sometimes develop a basic "button bar" for my solution and then copy and paste it from layout to layout in order to create a standardized user interface for my users--adding layout specific button to the right hand end of the button bar or in a second row of buttons located beneath it.

                  • 6. Re: Tables and Relationships

                    Many thanks, that sounds simple enough, I'll see how it goes, but need to concentrate on portals because it's got me totally confused.