6 Replies Latest reply on Apr 5, 2017 7:33 AM by Jason Wood

    Checking uncommitted related data

    Jason Wood

      When you edit related data in a portal, any calculations outside the portal that use the related data are updated immediately on screen (no commit required), however those same fields are not updated in the data viewer (or for any scripts that attempt to read those values) until after commit.

       

      I'm trying to build an OnRecordCommit script to inspect the related data before commit, so that changes can be reverted if necessary, but since the script can't seem to "see" the changes to the related data, it seems to be impossible. And of course if I commit it, then I won't be able to revert it if it doesn't pass the tests.

       

      Am I missing something?

        • 1. Re: Checking uncommitted related data
          philmodjunk

          Use onObjectValidate to check the data before you commit it.

          • 2. Re: Checking uncommitted related data
            Jason Wood

            The current record is a transaction line that has a number in either "debit" or "credit" fields.

             

            The related records are other transaction lines (1 or more) that also have "debit" and "credit" fields.

             

            My objective is to confirm that the transaction is in balance (debits = credits) before commit, but I can't do that if the script can't see the related values. So onObjectValidate won't help in this case.

             

            But I just noticed that I could use "Go to Portal Row", loop through the rows and pull out the values. Is that the best way? The portal is on a popover and it flashes on the screen (even with a "Freeze Window" script step), which is slightly annoying...

            • 3. Re: Checking uncommitted related data
              philmodjunk

              The script should be able to "see" the data. By using OnObjectValidate, you can use revert to roll back the data as though it had never been entered.

               

              If necessary, your script can "do the math" in order to compare values. It can get the current balance and either add the debit or subtract the credit to predict the new balance that will be produced when you commit the new transaction record.

              • 4. Re: Checking uncommitted related data
                alecgregory

                You can use GetNthRecord to avoid having to use Go To Portal Row. See this thread for more detail:

                 

                https://community.filemaker.com/thread/164946

                 

                Note that GetNthRecord does have an issue where it doesn't work properly if it's inside a List function. As long as you're not doing something like

                 

                List (

                     $data;

                     GetNthRecord ( <somefield>; $i ) // Tends to always evaluate as GetNthRecord ( <somefield>; 1 )

                )

                 

                Then I think GetNthRecord is the best way to go.

                1 of 1 people found this helpful
                • 5. Re: Checking uncommitted related data
                  srzuch

                  Yes. I have the same problem when totaling portal records using sum aggregate function in the parent table prior to committing the records.   There appears to be a bifurcation between the data referred to by the field name (incorrect) and actually what is displayed on the screen (correct), prior to committing the records.

                   

                  I need to go back to my notes, but as I recall, you need to force the calculation engine to trigger by adding additional calculations to the layout.  Another answer that I got and mentioned above was to write a script to loop through the portal and calculate a total for testing prior to committing the records.

                   

                  But then, do I really want to roll back many, many portal lines of transactions if the debits don't equal the credits.  The user may not be happy with that.  Or do I just want to note that this is an unbalanced entry that should be ignored until corrected.    So mabye after committing the records, but prior to existing the current records, I would test to see that the entries balance.  If they do, I would mark the parent record to note that the debits = credits.   If they do not, the parent record would not be approved, and the user would be given the option to correct or exist leaving the unapproved record. 

                   

                  Steve

                  1 of 1 people found this helpful
                  • 6. Re: Checking uncommitted related data
                    Jason Wood

                    srzuch wrote:

                     

                    Yes. I have the same problem when totaling portal records using sum aggregate function in the parent table prior to committing the records. There appears to be a bifurcation between the data referred to by the field name (incorrect) and actually what is displayed on the screen (correct), prior to committing the records.

                    Right. It's just aggregate functions that have the issue.

                    I need to go back to my notes, but as I recall, you need to force the calculation engine to trigger by adding additional calculations to the layout.

                    I did try putting the calculation field on the layout. It did update on screen but right next to it in the data viewer it still showed the previous value. This is what surprised me the most.

                    But then, do I really want to roll back many, many portal lines of transactions if the debits don't equal the credits. The user may not be happy with that.

                    Yes, good point, I only wanted to give the option to revert, but more importantly I didn't want data committed that was out of balance.

                    alecgregory wrote:

                     

                    You can use GetNthRecord to avoid having to use Go To Portal Row.

                    Awesome. It works. Thank you!!!