6 Replies Latest reply on May 17, 2012 8:50 PM by disabled_Denker

    Conditional Format vs. Calculated Fields


      I've inherited a large database to work on that was written pre-conditional formatting. The database contains many calculated fields that today would most likely be written as conditional formatting to the layout instead. For example, a calculation that changes the font size based on the length of the field data. Users have complained about response time with the database, so I am working to make the database more efficient. Does anyone know if there are any efficiency gains to be had by removing the calculated field definitions and replacing them with conditional formatting instead?



        • 1. Re: Conditional Format vs. Calculated Fields

          I think you would see performance gains, especially if the calcs are stored. When a FileMaker client accesses any field in a record, ALL of the data in that record (excluding containers and unstored calcs) are transferred from the server to the client, including all of those stored formatting calculations.


          By moving them to conditional formatting, you are reducing the amount of data that needs to go from the server to the client.

          • 2. Re: Conditional Format vs. Calculated Fields

            I think it is a toss up. If a calculation is stored, it only needs to recalculate when one of the referenced fields is modified. If the calculation is unstored, as with an unstored calculation field or conditional formatting, it has to calculate each time the screen refreshes on the layout where they reside.


            What I would do is pick apart the layout that is slow. Duplicate the layout and start removing quadrants of objects, each time testing if the speed is better. Once you find the area, try to find out if there is a specific object(s) that is causing the problem. Once you find the problem object, you can better asses what the solution is.


            If the problem doesn't seem to be associated with a particular layout, then you'll need to be more descriptive about what actions seem to cause delayed response time.

            • 3. Re: Conditional Format vs. Calculated Fields
              Stephen Huston

              Yes, there ARE gains.


              You can remove fields from the table! This is always an improvement IMHO. Fewer fields means less stuff needs to be downloaded/cached from the server before the FM client can act on the data.


              I have done exactly what you described — replacing unstored calcs with conditional formatting— and users report network speed improvements (which is due to not having to cache as much data from the server per record).


              It also makes your code simpler down the road. Change/delete unstored calcs EVERYWHERE you can! Replace them with stored auto-enter Evaluated calcs if you must keep them.


              Just my opinion, but my users have thanked me for "speeding up our network" without any clue how I did it.

              • 4. Re: Conditional Format vs. Calculated Fields

                In FM12 I found significant gains in speed by doing the opposite on one of my layouts.  I am now using an unstored calculation to add a colour to a field that previously used conditional formatting in FM11... I would prefer to use conditional formatting as with the previous version, but unfortunately it was no good on this layout.  I would suggest using conditional formatting if using FM11, but it is a good idea to test both options if using FM12.  I am sure there are many factors that could influence the outcome of the testing.

                • 5. Re: Conditional Format vs. Calculated Fields

                  depends on how amny there are, as to whether a calc load would be noticabe; but in principle yes. How may calc fields roughly? 20, 200?


                  As stephensexton pointed out, on FM12  the current  CSS inpmlementation can be a speed and size complication, as reported by others too.


                  Look too, at any scripts on the layout that cause a refresh + cache flush, and generally not be noticable testing on a local db, but over a LAN/WAN , can become a real speed  problem.


                  Cache flushes too, will be exacerbating the calc load by requiring all the calc related data to be download at every flush; everything is calculated on the client cpu not on the server; afaik.

                  • 6. Re: Conditional Format vs. Calculated Fields

                    If the database is calculation heavy and running quite slow, it is often likely that a calculation field is being used in a Sort. Sorting with an unstored calculation can be very slow.


                    Conditional formatting versus unstored calculation fields though, I don't think you'll see a big performance difference. The engine will perform the underlying calculation when the object is renderred the same, weither its a calc field or a normal field with conditional formatting.

                    For the conditional formatting this is done client side, for the calc field, server side.