7 Replies Latest reply on Jun 25, 2016 7:42 PM by clayhendrix

    How can I display dependencies of selected calculation field?

    CaraByrt

      I am creating a database that replicates an existing spreadsheet. The spreadsheet contains some complex calculations, and users are accustomed to click on a cell to see the raw formula, with all the dependent cells highlighted in different colours - a useful feature of Excel. Is there any way to replicate this effect in FileMaker?

      Thanks, Cara.

        • 1. Re: How can I display dependencies of selected calculation field?
          StephenWonfor

          Cara

           

          I imagine that a tooltip should fulfill this function.  If there are many of them I'd consider a distinct table of tooltips that are field specific.  The inter-cell linkage would be tough to replicate - maybe popovers with conceptual images would work. 

           

          Stephen

          • 2. Re: How can I display dependencies of selected calculation field?
            jjohan

            Cara,

             

            I, too, am working with some who are using Excel. They are so different, or different enough, that I don't think this would provide sufficient language translation for them. Are they going to understand relationships and FM Case statements?

             

            It can be done, using a combination of FM functions and conditional formatting and tooltips, and.. but why? It doesn't translate and what are they going to use it for?

             

            If you have a simple solution, then maybe, but if it's simple, why database it at all? If it's simple enough and they like Excel, do it there.

             

            I think I'd either:

             

            - Give them plain english descriptions of fields and calcs.

             

            - Convince them that it's not useful for them, not the same as Excel, not worth the extra expense of developing an application to mimic a spreadsheet feature. Or,

             

            -  Give them admin privileges, invite them to learn relational database applications/FileMaker and all their answers will be available for those who wish to look under the hood.

             

            I think my first question to the users would be: "Why do you want this?" To check my work? To make sure the calcs are correct? You can do this other ways in the process that don't require the application to try to become like a spreadsheet they can understand.

             

            FWIW

            1 of 1 people found this helpful
            • 3. Re: How can I display dependencies of selected calculation field?
              coherentkris

              imho replicating this functionality would be difficult at best.

              You should ask yourself what benefit it serves in the context of Filemaker.

              FM is NOT a spreadsheet and building functionality to replicate such mak make the users feel better but does it actually make their jobs easier?

               

              If the user does not have the capability to edit a calculation or sufficient FMP knowledge of what the calculation actually does ( when you start trying to abstract things calcs can get very lengthy/complex ) what use is it to show them?

              1 of 1 people found this helpful
              • 4. Re: How can I display dependencies of selected calculation field?
                CaraByrt

                Thank you all for your comments.

                For this project, FileMaker offers the advantage of being able to maintain a number of raw datasets that can each be run through the same sequence of calculations. However if people can't check what's feeding into what, then that might reduce the appeal. I think I will try putting plain english descriptions onto layouts, and if that is inadequate, I'll to back to Excel.

                • 5. Re: How can I display dependencies of selected calculation field?
                  BruceRobertson

                  You could print the field definitions to PDF and give that to them; or store it as a document. All sounds like a very sad throwback though.

                  • 6. Re: How can I display dependencies of selected calculation field?
                    mathepac

                    Yes, it sounds throwback-isa unfortunately.

                     

                    If the users know, understand and trust the Excel calculations, why do they need to view them? Is there some doubt about data integrity or formula / calculation accuracy and consistency?

                     

                    If any of the matters above arise, then I'm afraid FileMaker is not the answer as it doesn't seem to resolve the users' problems.

                    • 7. Re: How can I display dependencies of selected calculation field?
                      clayhendrix

                      1.) If you are replicating a spreadsheet, then they can check the work of the solution you build by entering the same data in Excel and your solution anytime they want to verify that it is working correctly.

                      2.) You could make another layout (tab, popover) or something that seems behind-the-scene that would display the result of the calculation at different points throughout the calculation. I am assuming that this is a multi-step calculation, so you might give them a place to go and see the "results" at different places between the raw data and the end result(s). Without knowing more about the solution, I can't be as specific as I'd like to be.