2 Replies Latest reply on Sep 12, 2012 7:21 PM by pilot_john

    Accessing Values from Previous Portal Rows

    pilot_john

      Hello,

      This is my first post on this forum, I hope I'm in the right place. I am using FM 11 Pro on a Mac. I do have FM 11 Pro Advanced on a different mac if I need it.

      I am trying to do a conditional format based on information from a previous portal row. I am having a very hard time figuring out how to do this. There are two fields in this portal that I am trying to work with, one named "start" and one named "stop". For example, Portal row 1 may have 100.0 under "start" and 100.9 under "stop" , row 2 may start at 100.9 and stop at 103.5. I want to highlight when there is a gap between the current rows start time and the previous rows stop time. In my example above nothing would need to be highlighted because start of row 2 is equal to stop of row 1.

      I belive the first step is to be able to reference the current portal row number in a calculation and then subtract one. I have tried to learn about various Get functions and none of them seem to number the portal rows right. There are gaps in the numbers because the layout record determines which related record show in the portal. Because of the gaps, I can not just say "record ID - 1" or "record number - 1". The only way that seems to number the portal rows correctly is inserting the Record Number Symbol "@@" into a text box, but then I don't know how to reference that text box in a calculation even after naming that box in the inspector.

      Am I going about this all wrong? After searching the internet for a couple of days, I have saw a few people talk about self join relationships but that concept hasn't clicked with me yet. I'm not sure what the purpose of a self join relationship is. So if that is part of the solution for my problem, can someone please explain how that works?

       

      If needed, I can provide more information, and with some time I could probably even upload the database but I would first have to strip it of all sensitive information such as customer addresses.

       

      Thanks,

      John

        • 1. Re: Accessing Values from Previous Portal Rows
          comment

          pilot_john wrote:

           

          Because of the gaps, I can not just say "record ID - 1" or "record number - 1".

           

          Actually, you can - but you have to do so in an unstored calculation field in the child table. When this field is placed in a portal, it will be evaluated from the point-of-view of the portal. Unfortunately, conditional formatting doesn't work that way - as you have discovered.

          • 2. Re: Accessing Values from Previous Portal Rows
            pilot_john

            Ok great, I wasn't telling it to be "unstored". I will have to study up on the effects of calculations or fields being stored vs unstored.

            When I first tried this solution I thought it wasn't working because I used Get ( RecordID ), but then I changed it to Get ( RecordNumber ) and that did the trick.

            The conditional formatting is working now too !

             

            Thanks so much for your help !

             

            John