14 Replies Latest reply on Oct 21, 2010 1:44 AM by LaRetta_1

    Merged variable in portals

    HowardRathbun

      Title

      Merged variable in portals

      Post

      I need to do a calculation on a couple of fields in each row of a portal and place the respective result on each row.  I placed a Merge Variable in the edit layout of the portal.  I then created a script to loop through each row of the portal, do the calculation and place the calculated result in the Merge Variable field.  This worked just fine if there is only one row in the portal.  But if there are multiple rows, the same answer is placed in each row.  I think this is because the Merge Variable has to be global so all rows get the same answer which is the answer created for the last row.  I can't find any other way to do a calulation in a layout without using this Merge Variable scheme, but surely there must be some other way.  I could add calculated fields to the underlying tables but that seems to be a brute force approach.

      Furthermore, I will need to add a dozen or so conditional error messages to the layout which will be based on calculations involving various fields on the layout and portals which is another reason I hope there is some way to place a calculated value on the layout without using global variables or adding calculated fields to the underlying tables.

        • 1. Re: Merged variable in portals
          sunmoonstar.13

          As far as I know, Merge Variables won't work in portals (or List View) the way you'd like them to work because they can't be "refreshed" independently for each record in the portal.

          You'll have to use calculation fields in your underlying tables (and put Merge Fields in the portal, if you want). It's not a "brute force" approach, it's standard practice.

           

          Nick

           

          • 2. Re: Merged variable in portals
            LaRetta_1

            Hi Howard,

            There isn't much that can't be done with merge variables.  If you can provide me with a link to a file with simple table and relationship which represents the two related tables and an example of the calculation, I can show you how you most likely can fill your portal with only merge variables - no need for calculations.

            However, from the sound of your post, you might be struggling with a bit of denormalized structure.  It would be best if you could upload your file (sans data) and point out specific example of calculation you need in your portal.

            • 3. Re: Merged variable in portals
              HowardRathbun

              Hi Nick,  I added a calculated field to the underlying table and then everything worked just fine.  Thanks

              howard

              • 4. Re: Merged variable in portals
                LaRetta_1

                Just as an update for future reference ...

                "Merge Variables won't work in portals (or List View) the way you'd like them to work because they can't be "refreshed" independently for each record in the portal."

                Indeed you can.  Merge variables work in portals and list views quite well.  And it saves having to create calculations which are only used for display in layouts.

                • 5. Re: Merged variable in portals
                  sunmoonstar.13

                  > Merge variables work in portals and list views quite well. 

                  I'm happy to be corrected. Could I trouble you to describe or give an example of how merge variables work in portals and List Views? 

                   

                  Nick

                   

                  • 6. Re: Merged variable in portals
                    LaRetta_1

                    I've been providing examples of this as links in many of my posts but I will put together a demo which shows both list use and portal use which includes enough depth and heavy calculations to show its power but still not too much to confuse.  I will include what I consider the key ingredients.

                    We have replaced entire financial spreadsheets with complex calculations (all created only using layout-level variables) and we have saved ourselves 20-30 calculation fields in one table alone AND ... they are much faster to redraw and are less resource intensive than calculations.  There are times that this technique would be the ONLY way to provide the figures without creating another relationship and calculation.

                    I posted link few months back where request was to know the difference between a person's age and the minimum for each activity they were engaged in but the actual relationship didn't exist to provide that information.  Using layout variables in the portal pulled the pieces together, and allow calculation between the two tables at a deeper relationship level.  Anyway, I could over-talk the subject so here's the link:  

                    http://www.4shared.com/get/e3w1Sqvf/PortalCalcs.html

                    The Eligible table occurrence is only to display all activities for purpose of demo (in portal to the far right).  The only real relationship is Eligible2.  Notice the 'I declare variables' (unashamedly stolen from Comment) under the first portal row.  It is key and any time we use this type of technique, stacking order is important.  I'm currently quite busy but I'll have something together by end of this weekend.

                    Once one understands the power of this technqiue, life is never the same.

                    • 7. Re: Merged variable in portals
                      sunmoonstar.13

                      Thank you, Laretta, much appreciated. I look forward to seeing your demo in due course. In the meantime, I'll have a look at the PortalCalcs file.

                       

                      Nick

                       

                      • 8. Re: Merged variable in portals
                        LaRetta_1

                        Thanks, NIck.  I should clarify that I learned most of these techniques from Comment and Mr_Vodka and you can check out their demo files over on http://filemakerforum.com/

                        The power of layout-level variables with conditional formatting (once you grok them) will make it difficult to sleep at night; coming up with all the wonderful ways the pair (layout variables and conditional formatting) can be used together.  I still am amazed at what I can do with them.

                        • 9. Re: Merged variable in portals
                          HowardRathbun

                          Thanks LaRetta, I also am looking forward to being able to use this technique.  I assume this is the "layout-level variables" you mentioned somewhere.

                          Howard

                          • 10. Re: Merged variable in portals
                            LaRetta_1

                            Hi Nick,

                            You had asked and Howard brought up the issue again so I put together (quickly) a few examples, particularly how you can use layout-level variables in list without calculations, globals, scripts or script triggers.  It also shows use for a report and creating 'calculations' in portals. 

                            Hi Howard,

                            Thank you for spurring me a bit.  I confess that I'm beyond brain dead so I hope my examples aren't too ugly.  The power of layout-level variables working with conditional formatting is incredible.

                            Layout-level variables

                            • 11. Re: Merged variable in portals
                              david583

                              Hi All,

                              Is this available in FM10 Pro, I have tried to look at all these examples and none appear to work. Am i doing something wromg or is it FM11 Pro specific?

                              All i get is text of the variable, no values at all.

                              • 12. Re: Merged variable in portals
                                davidhead

                                Hi David583

                                Merge variables were first introduced in FileMaker Pro 11. So you may want to upgrade :)

                                TS_Oz, FileMaker Inc.

                                • 13. Re: Merged variable in portals
                                  david583

                                  Damn! have both 10 and 11 but built on 10. time to rebuild

                                  • 14. Re: Merged variable in portals
                                    LaRetta_1

                                    Hi David,

                                    It is a powerful technique.  And those that USE the program instead of only learn from what they read in FM Help, are taking advantage of its power. :^)