10 Replies Latest reply on Apr 22, 2017 10:11 AM by stuart91

    I have dug myself a hole. What's the best way forward from here?


      I'm looking for advice on what to do with a Filemaker system that I (try to) manage. It has been built over a number of years, and is intended to keep track of an equipment rental business. Since we are a relatively small concern, it has always struggled for attention. Maintaining and improving it is not even close to being the main part of my job, so my focus is often pulled elsewhere.


      A while ago we decided that we would abandon Filemaker and move onto a proprietary cloud-based system, that has been specifically written for the rental industry. Whilst I was partly saddened to see many hours of work put aside, I was also quite glad that I wouldn't have do any further development work on our Filemaker solution, as it has become bloated and confused over the years.


      The problem is that, despite some promising early results, all of our staff hate the cloud-based system. It doesn't fit our workflow very well, so it feels cumbersome, and lots of basic things that we are used to doing in Filemaker are now impossible. The additional features of the cloud-based system don't make up for the loss of speed and simplicity.


      We've been trying out the cloud-based system for a couple of months now, and today it was pretty much decided that we will be going back to Filemaker. However I've been given a "wish list" of improvements to try and tackle in the coming weeks/months.


      My challenge is that, basically, the structure of the database is a mess. I can count fifty table occurrences on the relationship graph, out of 13 actual tables. There are dozens of relationships, most put into place to make a particular portal work, or as a hack or workaround. I suspect I've missed something fundamental with relationship tunnelling, and bodging my way around the problem has added too much complexity. There are lots of workarounds and techniques that must have made sense to me at the time, but are now quite difficult to follow.


      Whilst I could try and simplify the relationships, that's going to be a huge amount of work, because there are countless instances in scripts, calculations, and conditional formatting where field names would need to be changed. The amount of troubleshooting required will be ghastly.


      It's enough to make me consider starting again from scratch. I could probably copy a lot of layout elements etc. but it would still be a huge amount of time. And I'm not convinced that I won't end up in a similar mess, and come to grinding halt again, maybe a little further down the line.


      It wouldn't be out of the question to hire a developer, but I'm not sure if anyone would be too happy with a job that is essentially replicating an existing solution and interface, but with better arrangements under the bonnet. Plus would be realistic to expect anyone to be able to work out everything that's going on in the existing file?


      Are there any third party solutions that can help "map out" a database in a bit more detail, and show what relationships are used where? If I could automate some of the troubleshooting then it might be easier to tackle.


      I'm open to any suggestions - let me know what you think.


      I've attached a screenshot of the relationship graph, which at least should give everyone a laugh.

        • 1. Re: I have dug myself a hole. What's the best way forward from here?

          About hiring a consultant: As long as the check is for the agreed upon amount and clears the bank, they'll probably be OK with taking this on.


          Your relationship graph isn't all that awful in terms of the number of relationships and table occurrences.


          Breaking it up into table occurrence groups--possibly organized into Anchor buoy trees might make it easier to work with.


          Modernizing your interface and using a bit of Selector Connector might cut down on the number of special purpose TO's.


          But if you decide to do the work yourself, the two main things that you need is FileMaker Advanced and at least on of the third party developer tools such as Base Elements, Inspector, etc as they will be invaluable in tracing the spaghetti while doing a step by step revision of your system.

          1 of 1 people found this helpful
          • 2. Re: I have dug myself a hole. What's the best way forward from here?

            I agree with everything that philmfdjunk has said.

            • 3. Re: I have dug myself a hole. What's the best way forward from here?

              What Phillip C. said!


              This is the kind of work many consultants do on a regular basis. Cleaning up in-grown files or rebuilding the system from scratch is often done. When a system appears bloated or has too many iterations of changes or has so many new additions that the current system is almost impossible to modify to meet the new requirements are all valid reasons to start over.


              When talking to a developer I recommend also asking for clear documentation about how it's built and the dogma of field, layout, and relationship names/definitions. To maintain it in the future you will have to understand how it was built.


              Or you can ask how much to make changes later? Or what would be the cost of a maintenance agreement?


              Unlike prepackaged software this is customized to your business requirements and should meet your companies work flow and habits. Make sure everyone who needs to use it has a say in designing the UI. No one person (except the developer) will understand why the UI should be a certain way for the specific person to use it. The project manager (you?) will probably not understand why the Admin user needs to view different data than the data entry person. Probably the data entry person is the only one who really knows the tab order the fields should have. Small details like these make a huge difference in productivity. And acceptance of the new system.


              Good luck!

              1 of 1 people found this helpful
              • 4. Re: I have dug myself a hole. What's the best way forward from here?

                You can also do a rebuild and import the existing data into the new corrected version.

                • 5. Re: I have dug myself a hole. What's the best way forward from here?

                  not just ask for documentation, but you can also ask to be part of the process. you can be given tasks to do that get you "in there" and learning what's done and how to do it.

                  not all developers work this way, but some do!


                  • 6. Re: I have dug myself a hole. What's the best way forward from here?

                    You can straighten that out in no time.

                    1. Collapse all of the TOs so that you cannot see any field names.
                    2. Select all of the TOs and move them to the right, to give yourself a bit of work space
                    3. Select the TO that has the most connections and move it to the left.
                    4. Select it again, then use the command to select all occurrences of the same TO. Give them a colour.
                    5. Select the most connected TO again and use the command to select all TOs that connect to it.
                    6. Move them into a clear space, align them, then move them to the right of the TO they are linked to.
                    7. Keep doing this until all of your TO's are arranged in a left to right fashion.
                    8. Shift things around until they make sense.
                    9. Arrange your TOs in a way that doesn't allow any of the lines to cross.


                    You should end up with a colourful and neat arrangement. Add text notes: use large font sizes that really shout at you as headings for groups. Add explanations for later reference.



                    2 of 2 people found this helpful
                    • 7. Re: I have dug myself a hole. What's the best way forward from here?

                      +1 to Malcom.  That relationship graph structurally looks pretty good.  You clearly have an order, a person, an organisation, products, on top of that you have this dashboard.  I'd keep working with the database you have.

                      Having a relationship "just for a portal" is fine, etc.  The relationship graph is not an ERD, it's just a FileMaker tool so it's okay to have lots of the same table on the graph.  It could look neater , it looks kinda like a pre FileMaker v7 database that has had a rush job of going through the file format update process...

                      I haven't seen the rest of your database but based on that graph I'd just throw the wish list at an experienced developer and they'd probably be happy to throw the changes on top of what you already have.  The amount of tables you have is pretty low.


                      2 of 2 people found this helpful
                      • 8. Re: I have dug myself a hole. What's the best way forward from here?

                        Hi stuart91 ,


                        I had a look at your Relationship Graph and recommend you move towards anchor-buoy (there is lots of debate on Relationship Graph strategies...anchor-buoy offers a “safe harbor”)

                        I always give credit to anyone who can build anything in FileMaker that works at all. That said, developers that have been studying and working with FileMaker for years can help steer a system toward standards that ensure performance, maintainability and scalability (with less overall effort).

                        Here are 2 post on the topic of Relationship Graphs that you might find useful:

                        [begin excerpt]
                        Your journey will be smoother if you have clearer conventions for managing your Relationship Graph...
                        [end excerpt]

                        [begin excerpt]
                        Attached is a Sample TWDI_RelationshipGraph which (as luck would have it) is part of the system used to manage the FileMaker-Fanatics.com post production work flow.
                        [end excerpt]

                        Hope that helps.

                        Tony White

                        2 of 2 people found this helpful
                        • 9. Re: I have dug myself a hole. What's the best way forward from here?

                          As in all the other comments here already said your graph doesn't look that bad and If you just organise it just a little neater, it can look pretty clean!


                          The real issue is probably that you've lost the interest in the further development and that you need to find someone who can and wants to take this over from you.


                          If you think the relationship-graph is a mess, then the rest of you solution probably looks much better and then I think you should not have any trouble to find a good developer who'll be happy to take this of your hands.

                          2 of 2 people found this helpful
                          • 10. Re: I have dug myself a hole. What's the best way forward from here?

                            Thanks, everyone. I have only just had the chance to sit down and read through all the responses properly. It's all been very helpful, and far beyond what I had expected.


                            There is lots for me to read through and explore. Despite using Filemaker quite extensively since v7 I had never come across a mention of the Anchor-Buoy scheme before, for instance.


                            Part of me is excited for the possibilities of taking this solution further - I certainly enjoy the intellectual challenge of tackling problems in Filemaker. On the other hand, I know that my time is limited and it's very easy to hit frustrating roadblocks.


                            I'll try shuffling the TOs around on the graph and see if I can get a more logical layout from what is there at the moment. Part of the problem in the past has been that I've struggled to work out which relationship is appropriate for which layout/portal etc. and most of the lines on the graph have ended up there through trial and error! However even having a decent overview of things would improve matters. I hadn't even realised that it was possible to colour-code table occurrences on the graphs, that in itself is a major help!


                            If it's helpful, I would probably be able to upload a clone of the file? I'd be interested in people's opinions of the front end, above all the spaghetti.

                            1 of 1 people found this helpful