12 Replies Latest reply on Aug 3, 2016 3:05 AM by Johan Hedman

    How to remove redundant tables?

    macc

      Hi,

       

      encouraged by helpful answers – I let myself ask another problem.

      Any help and explanation will be appreciated.

       

      I have school database (one for two schools). About 95% work is done around present students, other 5% concerns those who has resigned, and our graduates.

      So most work is done within  Student table – repeating task are assigned to scripts and buttons. But in my work there is also many searches within present students. To perform searches I use Find Mode and Show All from Status Bar. In Form layout is about 25 fields that may be entered to perform search.

       

      At the beginig (14 months ago) I had little time to create solution so I put FormerStudents to seperate table. And the same I have done with Graduates.

      All three tables have some common fields (like name, birthDate, etc.) and little fields that are assigned to only certain group (like diplomaNumber, ifPassedExams – only for graduates).

       

      This way whenever I started search to find only present students i didn't have to set studentStatus field in Find Mode to Present. It look like time saver. But reading some of topics here I found that many times such tables are suggested to be just one table with differentiate field (for me It would be studentStatus field with three possible values (Present, Former, Graduate).

       

      If this is the right way – I will go for it. I can easilly merge those tables into one. But how to perform my finds easilly when I will merge tables? What will I gain merging those tables?

       

      If anyone is willing to help but find my explanation blurry – just ask question and I will elaborate to clarify the problem.

        • 1. Re: How to remove redundant tables?
          philmodjunk

          A common approach is to set up a unified table of all the fields that are common to all three groups but then add up to three related tables for the fields specific to a particular group.

          • 2. Re: How to remove redundant tables?
            Johan Hedman

            There is a tool called BaseElements (BaseElements | Goya Pty Ltd     where you can use a step by step report to consolidate tables into one table with all layouts and functionality and then import records.

            1 of 1 people found this helpful
            • 3. Re: How to remove redundant tables?
              macc

              Thank you, philmodjunk for guideline.

              1) I know that you are very busy man, but can you explain what will I gain from that merge? Now i got 3 tables, but after changes you suggest I will have 4.

              2) How should i perform finds within only present students? If in every find I will need to enter present in studentStatus it looks like there is no gain. And I'm sure I am missing some big thing in this picture. I would be glad if you could point that out.

              • 4. Re: How to remove redundant tables?
                macc

                Thank you johanhedman for your answer.

                Quick look at BaseElements site gave me impression that this is some powerful developer tool with some really nice features (why there are no such features in FM natively? ). And I guess that in my case upgrading from FM to FMAdvanced seems to be first step to take .

                And I would like to apologise for title for my question may be misleading. I have no problem with actuall merging tables. The problem lies in "life" after-merge and particulary: setting new realtionship (if needed) and perform finds.

                So I guess my problem is a basic one – but so far I couldn't figure it out on my own.

                • 5. Re: How to remove redundant tables?
                  Johan Hedman

                  I use BaseElements daily in my development to make sure I always have man solution without Errors, Unreferenced Items, Warning (as much as possible) and with best Performance for each object. I think with that I can stamp "No FileMaker errors" on my solutions and then it is only up to me to solve the logic.

                   

                  FMPA is a perfect tool during development

                  • 6. Re: How to remove redundant tables?
                    Mike_Mitchell

                    macc wrote:

                     

                     

                    If this is the right way – I will go for it. I can easilly merge those tables into one. But how to perform my finds easilly when I will merge tables? What will I gain merging those tables?

                     

                    The most common strategy for handling this is to modify the Perform Find command using Custom Menus. Basically, you constrain the found set of records to include only the ones you want. (One idea I've used is to capture the type from the layout on which the Find is executed using either Get ( LayoutName ) or Get ( LayoutTableName ).)

                     

                    Another method is to use portals with filtered relationships based on the layout.

                     

                    What you gain by merging tables is simplicity (the solution is easier to maintain), the ability to search all records (past and present), the ability to report on all records, etc.

                    • 7. Re: How to remove redundant tables?
                      macc

                      Thanks Mike_Mitchell for help.

                       

                      1) I have only FileMaker Pro (14) – so creating Custom Menus is beyond me.

                       

                      // Currently I have only present students on main table. Those students come from two schools. To facilitate my searches i put two buttons on my form layout to constrain finds to only one school. And normally i perform finds on both school (no input in schoolName field on layout during Find Mode). And then (if needed) i constrain my found sets to only particular school. 

                      I guess I could use similar buttons for studentStatus – but putting so many buttons only for finds seems like it isn't the way to go.

                       

                      2) I don't know if I understand correctly, but portal offers list-like view (one row for one record). And I need to perform my finds on form Layout.

                      • 8. Re: How to remove redundant tables?
                        Mike_Mitchell

                        macc wrote:

                         

                        1) I have only FileMaker Pro (14) – so creating Custom Menus is beyond me.

                         

                         

                        Get Advanced. You'll thank yourself.

                         

                        // Currently I have only present students on main table. Those students come from two schools. To facilitate my searches i put two buttons on my form layout to constrain finds to only one school. And normally i perform finds on both school (no input in schoolName field on layout during Find Mode). And then (if needed) i constrain my found sets to only particular school.

                        I guess I could use similar buttons for studentStatus – but putting so many buttons only for finds seems like it isn't the way to go.

                         

                        It isn't. Consider using a global field with a checkbox set that indicates what you're searching for (i.e., what constraints) and structure your script to work with that. (Hint: Script Parameters)

                         

                        2) I don't know if I understand correctly, but portal offers list-like view (one row for one record). And I need to perform my finds on form Layout.

                         

                        It's just another option. You can manipulate the relationship between the tables in a similar manner to performing a Find. However, it's a little more technical to do. It depends on how many fields you might include in such a Find.

                        1 of 1 people found this helpful
                        • 9. Re: How to remove redundant tables?
                          macc

                          FMPA is on my software shopping list.

                           

                          I am going to try global field with checkboxes – that sounds quite elegant as opposed to my buttons.

                          Time to learn something about script parameters . Thank you for showing me the way.

                          • 10. Re: How to remove redundant tables?
                            philmodjunk

                            1) I know that you are very busy man, but can you explain what will I gain from that merge? Now i got 3 tables, but after changes you suggest I will have 4.

                            The total number of tables isn't really an issue here. It's how you orgainise the data in those tables. Yes, you might have up to 4 tables, but with a structure that can work better for you overall.

                             

                            Consider what you now would have to do to find a student if you didn't know which table the student is located in--you'd have to perform the same find in each table. Plus, each time that a student's status changes, you have to copy the record to a new table-that's not efficient.

                             

                            And consider a report that needs to list students both present and graduated in the same report. You'd find that you can't list them easily on the same layout.

                             

                            And if this grows to be a large solution where performance is a concern, that unified table will be more of a "narrow" table with fewer fields per record. In FileMaker, when you reference even one field for a given record, FileMaker fetches the entire record so, in some cases, you can get better performance with a narrow table.

                             

                            2) How should i perform finds within only present students? If in every find I will need to enter present in studentStatus it looks like there is no gain. And I'm sure I am missing some big thing in this picture. I would be glad if you could point that out.

                            Yes, you would have to specify the status to find only records of a given status. Not only can this be done at the relationship level with a portal or a custom menu as described earlier, but if you have a layout dedicated only to one student status value, OnLayoutEnter is a trigger that can perform a script that finds all records of a given status and OnModeExit is a trigger you can set up to perform a script when the user exits find mode to constrain the found set to the same status. This last trick allows the user to perform their own find for students without specifying the status and yet they only get records for a given status.

                             

                            Also note that while the default arrangement of fields in a portal is for a single row of fields per record in a kind of table view of the related data, you can reduce the number of rows, make the rows taller and re-arrange the fields and other layout objects inside the portal row to produce a kind of form view of the data if you wish. That might not be ideal for you, but it's an option many new developers overlook.

                             

                            And I concur with others that you need FileMaker Advanced. Wouldn't touch a FileMaker database without at least that item in my tool box.

                            • 11. Re: How to remove redundant tables?
                              macc

                              Edit: As suggested by Johan Hedman – posted question as new.

                              • 12. Re: How to remove redundant tables?
                                Johan Hedman

                                Set your question as a new to not get to confusing with two different cases in one question