6 Replies Latest reply on May 5, 2011 5:46 PM by smalljoe

    Displaying a calculated result without creating a field in a report

    smalljoe

      Title

      Displaying a calculated result without creating a field in a report

      Post

      Hi:

      I'm creating a custom report. I would like to display a calculated result on the report without actually creating a field to store the result. How can I go about doing this?

      Thanks.

        • 1. Re: Displaying a calculated result without creating a field in a report
          philmodjunk

          This is a frequent request by people who have been using other software such as MS Access.

          It's possible if you have FileMaker 11, but it really is much, much easier to add that calculation field or fields to your table definition.

          You can place a global variable on your layout like this:

          <<$$GlobalVariable>>

          and then a script like this can compute the value and put it in the variable:

          Set Variable [ $$GlobalVariable ; Value: //put your calculation here ]

          And if you need to compute a value that represents the aggregate of a value from a group of records, you may have to create a much longer script to do this--which makes using calculation and summary fields for your report even more the better option in FileMaker.

          • 2. Re: Displaying a calculated result without creating a field in a report
            smalljoe
            Thank you. So off to building calculated fields I am.
            • 3. Re: Displaying a calculated result without creating a field in a report
              LaRetta_1

              Actually, you can use calculations at the layout level and you do not need to create calculations in field definitions and you do not need script to refresh them.  I have many examples but here is one:

              http://www.4shared.com/file/mw-u1Tly/VarDisplay.html

              Notice the text field in the row behind the <<$price>>.  I left it in red and you will see it when you are in layout mode.  This "I declare variables" holds the calculations within the Let() statement.  Where it is placed and in what stacking order makes the difference.  In a list view, it is placed under one of the variables it will evaluate so it refreshes.

              In the first record, change the royalty amount from 3 to 11.  Neither of the two 'fields' to the right even exist except in the Let() portion of the red field.  The 'I declare variables' is a method I picked up from Comment (Michael Horak) over on FM Forums where he frequents. 

              These types of 'layout calculations' do not work all the time.  If the calculaton is very complex sometimes it requires a Freeze Window and sometimes if VERY complex it will require a Refresh Window [ flush cache ].  But on simpler needs, don't bother creating all of those calculations whose only job is to display something in text depending upon certain local results.

              I have many other examples, refreshing portals etc.

              • 4. Re: Displaying a calculated result without creating a field in a report
                philmodjunk

                Thanks LaRetta. That's actually what I was asking from you in another post yesterday. Wink

                I'm intrigued by the fact that you are using <<$VariableName>> instead of <<$$VariableName>> that might explain an issue I was having where the variables weren't updating at the right time for me even though the calculation involved was pretty simple.

                • 5. Re: Displaying a calculated result without creating a field in a report
                  LaRetta_1

                  Hi Phil, it's not necessarily (in this instance) whether we use one $ or two but rather it is where we place the 'I declare variables' in the stacking order and the situation where/when it is used.  If you change my example to $$ it will work just as well.  There are a series of theories on where/when it will refresh and when not but I haven't put them all together and I probably should.

                  They should not be used to replace all calculations in a large report with multiple summaries and complex calculations.  We have done this and it can drain resources even more than aggregate when used to replace 11x17 spreadsheets with 15 columns of calculations. Tongue out

                  But on instances where we have previously created calculations for things such as my example that will only be used for certain layouts and not used repeatedly throughout a solution or exported or required for other calculations then it is perfect.  I have other examples where, if placed under the first row of a portal, calculations can be used in portals and refresh just fine.  Each situation is different.

                  $ is actually good because it does NOT refresh so it works when a layout is first drawn and the value then stays unless forced to refresh by being slightly under another field.  I cannot give a specific example off the top of my head but using them effectively is a fascinating subject.  I have replaced easily 40 calculations in a complex solution whose purpose is simply displaying different values.  Conditional formatting with merge variables (what I call layout-level variables) rocks.

                  • 6. Re: Displaying a calculated result without creating a field in a report
                    smalljoe

                    Thanks guys. I'm going to try it.