4 Replies Latest reply on Mar 17, 2017 5:42 PM by briancrockett

    Getting A Calculation to Latch


      I want to set a flag if one of the records in a related table gets over a maximum and have it stay set once it reaches that maximum even if if later goes down.




      Current record = 5 if one of the records in the related table becomes greater than 5 I want a flag to set in the current record and stay set no matter what.


      I tried to reference the value of the flag in my calc. [ If( flag = 1;1;0) ] but this is self referential and creates an error.


      I tried using an auto enter field with "Do Not Replace" but the calc won't trigger because the value that's changing is from a relationship.


      I suppose I could use a trigger but it's a big system and I'm not sure of all the ways that the values in the related table can be changed. I'd prefer to do it as a calc.


      Does anyone have any suggestions?

        • 1. Re: Getting A Calculation to Latch

          Use a script trigger. Try the OnRecordLoad on the parent record rather than the the related table of records.


          If [ Max ( RelatedTable::Field ) > CurrentRecord::Field ]

             Set Field [ CurrentRecord::Flag ; 1 ]

          End If


          If this take too long due to very large numbers of related records, you'll need to go with a script trigger on the related table.

          • 2. Re: Getting A Calculation to Latch

            I don't think that would work in my case but you have given me an idea.


            This is to flag some thing as ready to order in yet another table once we have stock. If I do an on record load trigger in that table. Have it go out to the two original tables and see if there's stock. The only problem is the stock level could go up and down again between the users viewing it.


            Maybe a timed server side script to check stock levels every hour and then set the proper fields.


            I'll have to look into it on Monday



            • 3. Re: Getting A Calculation to Latch

              To track inventory, I'd use an Inventory Log where every transaction that increases or decreases inventory is logged by adding a record to this table.

              Edits to this table would then trigger a script to set a flag in the related products table. An unstored calculation field in products could also serve as a flag.

              • 4. Re: Getting A Calculation to Latch

                That's actually what I'm doing. It's just my boss wanted me to start with orders only. We would then go back and add other transaction types to the process later.


                Any way I had a brain storm on the way home. All products are weighed before packaging. The weights are recorded and a flag is set against  the line item.  I can use that to latch my calculation.