5 Replies Latest reply on Aug 8, 2017 7:23 AM by philmodjunk

    Get value from last row in a portal to another field and auto update when changes were made to...

    TaukoririMeita

      Title

      Get value from last row in a portal to another field and auto update when changes were made to portal.

      Post


           So I have Two related tables. Table1 and Table2.

           In Table1 I have field "LBalance" (A Calculation fiedl which based on this topic ??).

           In Table2 I have a Portal with a field "Balance" (A Calculation field of other fields on the same Portal).

           So I want to get whatever value in the last row of Balance to show up in LBalance. So I did a simple calculation of:

      LBalance = Table2::Balance

           Well that works for the first row, but then when a new row is created due to other calculations. The new value of Balance goes into the new row (last row), but the LBalance value stays the same from the first row.

           How to make LBalance always get the value of Balance in the last row of the Portal and update itself automatically when new row is added to Balance?

           check image below.

           Thanks.

      Snapshot.jpg

        • 1. Re: Get value from last row in a portal to another field and auto update when changes were made to...
          philmodjunk

               As you have found any direct reference to a field from a related table refers to the first such related record. This is true even if there is no portal. The first such record may not, in all situations, be the first portal row. If you specified a sort order on the portal that sorted the records in a different order, your calculation still references the first related record even though it is no longer the first row in the portal.

               If your portal is unfiltered and unsorted, you can use the Last( Table2::Balance ) to access Balance in the last related record--of those records where Balance is not empty.

               You could also use GetNthRecord ( Table2::Balance ; Count ( Table2::AnyFieldThatIsNeverEmpty ) )

               In both my examples, you'll need to subsitute actual table occurrence and field names for the terms I've used.

          1 of 1 people found this helpful
          • 2. Re: Get value from last row in a portal to another field and auto update when changes were made to...
            TaukoririMeita

                 Thanks alot PhilModJunk.

                 I actually followed some guide which ends up like what you see in the picture in my first post. And I'm not sure whether what you're saying about the tables if they're unfiltered, unsorted....but I'm sure what you're saying is true. I just use the formular you provided and it works.

                 LBalance =

                 GetNthRecord ( Table2::Balance ; Count ( Table2::Balance ) )

                 This is all I need right now,

                 I'll post another question on how to total up the row ("Total no. of days taken") ONLY in years not from top to bottom. I'll post more details soon.

                 Thanks again.

            1 of 1 people found this helpful
            • 3. Re: Get value from last row in a portal to another field and auto update when changes were made to...
              philmodjunk

                   It's not a case of filtered or sorted Tables. It's a case of whether or not you have specified those options for the portals. If you open portal setup, you'll find you have an option for specifying a filter and also a sort order. But neither of these settings will change the results returned by the two methods that I suggested.

              1 of 1 people found this helpful
              • 4. Re: Get value from last row in a portal to another field and auto update when changes were made to...
                akid83

                Thank you for the question from Tauk and good reply of Phil. I get the idea of getting the latest row in a portal. Here I have a further question. Follow the example of discussion.

                 

                LBalance =

                     GetNthRecord ( Table2::Balance ; Count ( Table2::Balance ) )

                 

                To create the field "LBalance", It looks like we can use an "unsorted calculation field" or a "auto-enter text field" using same calculated value. Are these ways both working? which one is better?

                • 5. Re: Get value from last row in a portal to another field and auto update when changes were made to...
                  philmodjunk

                  Both might be the best choice neither might be the best choice. It depends on your solution's design and what it needs.

                   

                  An an unstored calculation field will recalculate every time a layoit with the field is entered and anytime another calculation that references evaluates as well as any time a script references it. This keeps the value up to date, but can slow down your solution. It might not either, but you have to use them with care.

                   

                  An auto-entered calculation will not re-evaluate automatically if the field from a related record is modified. This can improve performance, but it can also leave the wrong value in your field.