11 Replies Latest reply on Jan 13, 2016 2:55 PM by googull

    Using calculations on merge fields in Layouts (?)

    googull

      Newbie to FMP and I am struggling to figure out how to apply calculations to merge fields as I am not finding a calculation control (object) to place on my Layouts.  I am creating a series of summarized reports that need uniquely strict formatting on much of the data, concatenations, etc -  One example desired result is to place an "object" that displays a calculated result as follows  "(" & Trim(Table:Field_A) & ")"  .  I have dozens of such formatting needs and really didn't want to "pollute" my tables with a bunch of calculated fields just to trim fields and concatenate text for use as merge fields on a series of reports.   As a workaround, I've inserted Tab Controls just to get access to the Calculated Tab Labels - that works but dropping a tab for every calculation need can't be ideal.   So my question is if there a way to apply a calculation directly to a merge field or is there a calculated display object that I am simply not seeing?  


      thanks


        • 1. Re: Using calculations on merge fields in Layouts (?)
          BruceRobertson

          You can place merge variables on a layout and use scripts to set them.

          You can have auto-enter calculations on the fields in question so they trim themselves and do not need another calculation.

          You can use single-element buttonbars; their content is a calculation.

          • 2. Re: Using calculations on merge fields in Layouts (?)
            googull

            BruceRobertson wrote:

            "You can place merge variables on a layout and use scripts to set them."

             

            I've tried this using merge variables via the OnRecordLoad script trigger to set them. This doesn't seem to trigger at the row level when a summariezed layout is displayed in List View.  Am I missing something here?

             

            You can have auto-enter calculations on the fields in question so they trim themselves and do not need another calculation.

             

            I suppose that's the problem with posting an example. I am doing much more than trimming fields. Still it would be great If I could control and ensure perfectly trimmed data. Many of the fields are calculated already, coming from case statements, and imports for many sources - this is question is about finding an elegant means to ensure a properly formatted output despite the data.  

             

            You can use single-element buttonbars; their content is a calculation.


            I tried the control bars first but resorted to Tabs instead for a few reasons: 1. No need to delete default button count of three, but the primary reason is that the Tab Control readily accepts formatting from the brush (Format Painter) making it easy to clone formatting from surrounding fields.  The button bar object seems to reject formatting from other field types via Format Painter.

             

            My take away from your reply though is that there isn't a calculated layout field object today nor any means today to apply a calculation to a merge field directly. 

            • 3. Re: Using calculations on merge fields in Layouts (?)
              beverly

              My take away from your reply though is that there isn't a calculated layout field object today nor any means today to apply a calculation to a merge field directly.

               

              That might be a good Idea (if there is not one already!)

               

              I don't like to use tooltips or other Inspector areas (that call the calc engine), but I wonder if there is a way to 'trigger' the update of the Merge Variable for each record. - Let's see if that brings other responses.

               

              beverly

              • 4. Re: Using calculations on merge fields in Layouts (?)
                googull

                I spent more time looking for ways to address this challenge with script/merge variables.  The challenge being to use scripts and merge variables to output row level content on a Layout.  In List View using the OnRecordLoad trigger the script doesn't get called until the Layout is filled with rows.  In Form View the result is late meaning the script is not called until after the merge variable has been displayed on screen.  Consequently the displayed results are always off.  Unless there is another trigger it's becoming clear to me that the design expectation is to create calculated fields in the tables.  I struggle with that practice for the purpose of report formatting - not just because it needlessly clutters up tables but because it means table changes are needed when crafting future reports... painful especially for high use environments.  It is just odd that we can use formulas to apply formatting to a label in a layout but not process a labels content.  And we can use calculations to craft titles for Button Bar and Tab Names.  It would clean to do the same for a Calculated Label object.  Am I missing something and/or is this worth posting as a wish-list request?  In the meantime I sheepishly continue placing Tab Controls on my Layout [reports] to gain access to its calculate tab name and will no longer belabour this.  

                • 5. Re: Using calculations on merge fields in Layouts (?)
                  BruceRobertson

                  Note that discussions of style performance strongly recommend that you never use the format painter.

                  Regarding the style aspect of this question - is there some reason you don't want to define styles, whether for tab objects or button bars, etc.?

                  • 6. Re: Using calculations on merge fields in Layouts (?)
                    user19752

                    For use of report, you may be able to use "hide object when" calculation to set merge variable.

                     

                    Let ( $$mergeVar = "any calc" ; 0 )

                    • 7. Re: Using calculations on merge fields in Layouts (?)
                      googull

                      19752 - Your suggestion is very clever and for my purposes effective at least for printed reports (Preview Mode).  Browse Mode for screen reports works too but only after manually scrolling rows off screen and back again into view (some kind of refresh needed).   Thank You!

                      • 8. Re: Using calculations on merge fields in Layouts (?)
                        user19752

                        I meant "report" as printed. I saw list view browse mode the values are all same that is evaluated in last record on screen, then activate a record then the value in the record become expected, etc.

                        • 9. Re: Using calculations on merge fields in Layouts (?)
                          googull

                          user19752 wrote:

                           

                          ...I saw list view browse mode the values are all same that is evaluated in last record on screen, then activate a record then the value in the record become expected, etc.

                          Yes, and my primary interest for this is List View in Browse Mode.  While selecting a record to "activate" it does update the displayed value I was noting earlier that I can get the value in many rows to display properly by scrolling rows off screen or even reducing the window size to mask those displayed values then resizing back out.   All row values covered and uncovered via scrolling or a manual window resize then refresh the displayed values without having to activate any individual rows.  Even switching to Preview Mode and back to Browse doesn't perform this "refresh, nor does minimizing and then restoring the window, etc.   Perhaps there is a way to "refresh" the opened window to get your technique work in List View Browse Mode after all?

                          • 10. Re: Using calculations on merge fields in Layouts (?)
                            BruceRobertson

                            One tool you seem to have overlooked so far is the placeholder calculation. By using an empty global field or field that is always empty of content, you can display the placeholder text instead; and placeholder text is a calculation.

                            • 11. Re: Using calculations on merge fields in Layouts (?)
                              googull

                              Bingo Bruce - That is working beautifully.  Remarkable how many indirect ways there are to get a calculated output result onto a  Layout (Tab Names, Button Bar Names and Empty Field Placeholder Text).   I prefer the cleanliness of the placeholder solution.   Thanks for posting these creative solutions.