8 Replies Latest reply on Aug 25, 2016 9:19 AM by alecgregory

    Testing portal totals before committing records.

    srzuch

      I have a very basic business rule objective.  I want to total Child records in a portal, and make sure they equal to an amount in the Parent (master) record, before committing the Parent and Child records.

       

      This is the simple Table schema:

       

      Master:

        MasterId

        ChildAmountSum (Sum ( Child::ChildAmount )), unstored since reference a field in another table)

        GoalAmount

       

      Child:

        ChildId

        MasterId

        ChildAmount

       

      Relationship (equal, with ability to create records on Child side checked)

       

      Master::MasterId ------< Child::MasterId

       

       

      I also want the total of the Childs records to be currently shown on the layout as I enter or change the Child records.

       

      I use the aggregate SUM function in the Parent (master) record, which updates beautifully as I enter the Child records.

       

      I have an OnRecordCommit script trigger on the layout that compares ChildAmountSum to ChildAmount.  What I am finding is even though the screen shows the amounts being equal, the script fails.  Even the Data Viewer is not being properly updated.

       

      This is how things look before I attempt to commit.screen.tiff

       

      And when I click outside the portal and commit, the script trigger test fails.

       

      screen2.tiff

       

      If I set the GoalAmount to 200, the script trigger test passes, the record is committed, and the data viewer now shows 1000.

       

      I can understand that since the records are not committed, the aggregate SUM function may not return a result that reflects what is being shown on the screen.  Also, ChildAmountSum field is not stored (I do not have a choice).  I am considering using temporary tables (or even repetition fields since I can store the total) to gather and commit the data for testing before adding them to the transaction table, or maybe just committing the records first, flagging the parent record as untested, testing the totals then removing the flag if totals equal.

       

      I am using FMA v14, and I am still determining if FM is the right product development tool for some of the financial business system we are looking to convert from Microsoft Access and Excel.

       

      Any comments appreciated.

       

      Steve

        • 1. Re: Testing portal totals before committing records.
          Johan Hedman

          Have you tried to use Script step Update window before you run your script?

          • 2. Re: Testing portal totals before committing records.
            srzuch

            The the Refresh Window step in at the start of my script, with Flushing the cached join result, and without.  Problem still exists.  Even though the Sum of the portal records on the screen is correct, the actual value displayed in the Data Viewer, and within the Script, is still not updated.  This situation is very misleading, and very confusing.

             

            I should be able to sum the portal records and test the total before committing the records.

             

            Steve

            • 3. Re: Testing portal totals before committing records.
              srzuch

              This is the script:

               

              Untitled.tiff

              • 4. Re: Testing portal totals before committing records.
                alecgregory

                In general, a script will only see updated information from joins after a commit. This includes fields based on those joins, which it looks like your ChildAmountSum field is. The exception is when accessing fields in the join through which you made the changes, i.e. the join that your portal is based on.

                 

                The best approach to handling this in a pre-commit script I have found is to recreate the sum field using a loop through the records in the portal. So, say you have been updating records in the table Child through a portal in a layout based on the table Master and the portal table occurrence is name Master_CHILD, you loop through the related Child records and create the sum as follows:

                 

                Set Variable[$i = 0]

                Loop

                     Set Variable[$i = $i + 1]

                     Exit Loop If [EvaluationError ( GetNthRecord ( Master_CHILD::Amount /*Amount in child table*/; $i ) )     Set Variable[$calculatedTotal = $calculatedTotal + GetNthRecord ( Master_CHILD::Amount; $i );

                End Loop

                If [Master::GoalAmount = $calculatedTotal]

                     Show Custom Dialog for passed test

                     Exit Script[Result: True]

                Else

                     Show Custom Dialog for failed test

                     Exit Script[Result: Get ( LastMessageChoice ) = 1]

                End If

                1 of 1 people found this helpful
                • 5. Re: Testing portal totals before committing records.
                  srzuch

                  After doing some more research work, I think I resolve the issues.

                   

                  Instead of comparing the ChildAmountSum to the GoalAmount, I changed the ChildAmountSum field to be the sum of the portal records LESS the GoalAmount, and then compared the ChildAmountSum to zero.

                   

                  This now works, and I am able to test the totals before committing.

                   

                  Based on what I have read from other postings, it appears that I am forcing the dependency chain to recalculate the unstored calculation.

                   

                  Steve

                   

                   

                  • 6. Re: Testing portal totals before committing records.
                    srzuch

                    AlecGregory

                     

                    Even though I think my suggestion works, your answer is better, and I marked it as the correct one.  It is more "transparent" and also allows for more flexibility in testing.  Since I don't expect my portals to contain numerous amount of records (1-25), I don't think performance is an issue.

                     

                    Thanks for responding (including providing the explanation).

                     

                    Steve

                    • 7. Re: Testing portal totals before committing records.
                      okramis

                      You could use ExecuteSQL() to sum the child records, should work on un-commited records.

                       

                      Otmar

                      • 8. Re: Testing portal totals before committing records.
                        alecgregory
                        You could use ExecuteSQL() to sum the child records, should work on un-commited records.

                        Yes, that will work. However, it is more open to performance issues. ExecuteSQL will need to download every record in the child table from the server to create the query result. With a large child table on WAN or even moderate LAN connections that can take minutes.