12 Replies Latest reply on Feb 16, 2012 8:04 AM by beverly

    Cleaning up my table occurences


      My current solutions is a result of 10 years of developments. If you looks at the table occurences it looks like spaghetti. It's really time consuming to reorganize the table, but thereis one thing i would like to do is to remove relationships that exists twice or even three times. So relationships that are exactly the same


      Question 1: what is the fastest and easiest way to locate and find these multiple existing relationships without manualy going through the spaghetti bolgonese

      Question 2: after find a relationship that should be deleted how i'am shure that it will not harm my solution. It might be used in scrips, lauouts etc.

        • 1. Re: Cleaning up my table occurences

          Baseelements 3 from goya !


          and to find the needle and the haysack, developper assistant from dracovention !

          • 2. Re: Cleaning up my table occurences

            I've been straddled in a similar situation more than once in the past few years. While cleaning it up is a possibility, you're correct in worrying about breaking scripts, layouts, etc.. At ten years old, have you considered just rebuilding using newer techniques and fully leveraging what FMP has become? Even fairly complex systems are much easier now and would probably take a lot less time than cleaning up.


            For what it's worth - HTH,



            • 3. Re: Cleaning up my table occurences
              Stephen Huston

              If you have FileMaker Advanced, run a full Database Design Report (DDR) on the open system and then search for the TOs by name. In HTML DDRs you will need to try searching on all possible redundant TO names, noting where each appears in Scripts, Relationships, Value Lists, and Calculations. (DDR is under the Tools manu in Advanced).


              Once you have determined which occurances are most used and least, you can then use that info to locate and modify the least used TO to use the one you want to keep, editing the file references in scripts, etc. When you think you're done, run the DDR again and be sure any TO you want to delete is no longer referenced in any of those spots, and restructure the TO Graph where you can without creating those forbidden relationship circles.


              Also do this with FileMaker Data Sources so that you only have one data source for any given file. It's a lot of work on a big project (I'm slowly working through 65 files developed over 12 years by multiple developers) but it can really improve things and avoid possible user conflicts due to multiple connections to a single file via multiple IP data sources.


              Well worth doing, just not much fun.

              • 4. Re: Cleaning up my table occurences
                Stephen Huston

                I agree entirely with Tim's rebuild idea --  if it's feasible.


                However, full rebuilds can be nearly impossible in huge systems with a decade of development in them and ongoing daily modifications to live systems.


                In my case, I am in-house at a place with daily FM changes, new reports every week, and the requirement that the system be kept live to staff 24/7/365 -- we don't even turn out the lights for legal holidays.


                Just stopping and starting our server maybe once a year  at 4AM for an upgrade takes weeks of planning. When I first started here 2 years ago, I envisioned a rebuild of an old migrated system. Now I just try to simplify all the bloat as I can isolate it, while constantly modifying  the live system. Never have found any time to start that rebuild.

                • 5. Re: Cleaning up my table occurences

                  Ongoing daily modifications - I don't miss those days.


                  In other systems where downtime had to minimized, I built the new database as well as a set of scripts to manage the import, and ran repeated test until things were ready to go. We turned the lights out, ran the script and moved people over.


                  Just in case this method helps.



                  • 6. Re: Cleaning up my table occurences

                    And to this I'll add - backup, backup, backup. Test against an offline copy. This is where I've been bitten more often than I care to remember.


                    • 7. Re: Cleaning up my table occurences

                      I would also Add to Stephen's:


                      1. Use of a Consistent Naming Convention will go a long way.


                      2. For Found Dupes add a leading "bullet(s)", or another flag, to all but One TO to identify duplicate Relations to delete. Bullet will show up in all Scripts, Calculations, Value Lists & layout's Portal Names -


                      2.a. Then run the DDR.


                      2.b. May want to adjust those that are Bulleted based on which Relationship is used most.


                      2.c. I also give the TO to delete A Hot Purple Color.



                      • 8. Re: Cleaning up my table occurences

                        Once you run the DDR, use the enclosed XSLT (stylesheet) and import (as XML) your DDR relationships into a new table. You will get these fields:



                        LeftTO (table occurrence name on the left side of the relationship)

                        LeftField (field used on left side of the relationship)

                        LeftFieldID (id of field used on left side of the relationship)

                        predicate (the symbols that make the 'MATCH')

                        RightTO (table occurrence name on the right side of the relationship)

                        RightField (field used on the right side of the relationship)

                        RightFieldID (id of the field used on the right side of the relationhip)

                        File (name of the database with this relationship)


                        relationships with multiple matches, will have more than one record imported.


                        TIP: if you have a lot of files and/or relationships, create a new DDR with just the Relationships and import as XML with this stylesheet.


                        HTH, I use this to give me the "old-style" (pre graph relationship list) look at Relationships.


                        • 9. Re: Cleaning up my table occurences

                          Be careful when using the HTML DDR, it has it's own limitations.  I've written about a few of these here : http://www.goya.com.au/baseelements/manual/32-comparison-be-html-ddr


                          If you start with the XML DDR and then use either Beverley's code or something like BaseElements you can get all the info you need.


                          But there is no real way to tell what a duplicate relationship is.  The relationship consists of two TOs ( which can have different names ) joined by one or more predicates, and you can put the predicates in any order, so trying to compare them is hard.


                          You can use BaseElements to find unreferenced TOs though, so removing those will help in your cleanup process.






                          • 10. Re: Cleaning up my table occurences

                            Stephen's is pretty much how I've done it when I've had to do massive TO clean-ups; in those cases, I've also taken the opportunity to reorganize the relationship graph into something more logical. One of the biggest problems I've found with spaghetti graphs is going through a script and having to trace through 8-10 hops just to clarify what records are being accessed; so the point becomes not just trying to remove duplicate/redundant TO's, but to modify the graph so that the relationship link chains are much shorter with greater clarity.


                            The way I've gone about it is to start building a new table structure that contains just the needed TOs and relational links; using the DDR to identify all the uses of the old TO's and change them to use the new table structure; run the DDR again to verify that they are all removed; and then remove the old cruft.


                            And because of the issues nickorr mentioned, I don't just depend on the lists of "In Relationships/Scripts/Field Definitions/etc." in the Table Occurances section of the DDR; I do full-text searches of the DDR for the TO name. Sometimes this means changing the names of TOs in the old structure so you can easily search for them, if the TO is named with a word or term that's commonly used in the solution.

                            • 11. Re: Cleaning up my table occurences

                              One other thing that can help a great deal, if you're working with a large relationship graph: Make a printout of the graph, then manually check off each TO once after you do the search-check, and a second time after you fix all the references to it. It may help if you do some minor re-arrangement to make the TO's fit neatly within the page boundaries before printing; I've found that (on the Mac side, at least) using a Page Setup of landscape mode, scaled to 65%, does a good job of fitting more on the page without making the TO titles too small to read. After doing the page setup, use the page grid button (to the left of the Page Setup button) to show the page divisions, and shift your TOs to fit neatly within the boundaries.

                              • 12. Re: Cleaning up my table occurences

                                tbutler, makes a valid point IF you have on database and one relationship graph. I think we get into the most trouble when converting from pre-fm7. Before then, each database was one table and each "graph" upon conversion would need to be printed.


                                I also print to PDF. As I do, you may have "annotation tools" and can "check off" without all that paper.