7 Replies Latest reply on Feb 3, 2012 9:19 AM by Stephen Huston

    Child Records and Unstored Calc's

    kaostika

      Hi,

       

      I am working on a database that I did not design. The previous designer put calculations in the child records. For instance if there is a set of conditions that needs to be met he would have an If statement the evalutated all the conditions and then set itself to 1, and then relate to the number 1 in the parent. Rather then have all the contitions in the parents and then simply get the Child records that way.

       

      My question is how much of a performance hit is taken on this and also I was under the impression that you could not get unstored records to show up in the child records.

       

      Oreste

        • 1. Re: Child Records and Unstored Calc's
          comment

          kaostika wrote:

           

          he would have an If statement the evalutated all the conditions and then set itself to 1, and then relate to the number 1 in the parent. 

           

          Relate how? If the calculation is unstored, it cannot be used as the matchfield on the child side.

          • 2. Re: Child Records and Unstored Calc's
            Stephen Huston

            Hi Oreste,

             

            One's definition of Child may at issue. The Child is not necessarily the Many side of the One-to-Many relationship when determining if the unstored calc Keys will work.

             

            If you are viewing the records from  table A, the related table B will not work if the unstored calc is part of the foreign Key of Table B. but it will work if its in Table A.

             

            If you view changes from A to B, and the unstored calc is part of the primary Key in table A, then you won't see the related records in B.

             

            Stephen Huston

            • 3. Re: Child Records and Unstored Calc's
              Stephen Huston

              An additional note: if the unstored calc is not part of either the primary or foreign keys, it will still be visible from both tables; it just won't necessarily support the relationship.

               

              It can still be used when filtering a portal, for instance, but doing so can make things really really slow if in the child/portal records.

               

              If you can moved the calc to the parent record (the One side of the One-to-Many relationship) things will be faster as it only needs to be evaluated in the one record.

               

              Stephen Huston

              • 4. Re: Child Records and Unstored Calc's
                kaostika

                If ( Invoice Done = "no"  and Post Party Tally Done  =  "Done"  and (Status = "Present" or Status = "Completed" or Status = "Sold" ); PartyDate ;"1/1/2200" )

                 

                Here is a sample Calc.  There is on the other side a Global that is the Current Date.  This one is an indexed calc. So it works.  But there are hundred of these in the solution.  Let me remove the word Child records, since it is a bit tricky. On the Many side of the relationship

                • 5. Re: Child Records and Unstored Calc's
                  comment

                  kaostika wrote:

                   

                  This one is an indexed calc. So it works.

                   

                  Then how does it help in understanding the issue?

                  • 6. Re: Child Records and Unstored Calc's
                    kaostika

                    Ok, is there a way for me to know what calculations are used in relatiohships and what are unstored?  Putting things in the child records is the wrong thing to do and I am wondering what I am up against.

                    • 7. Re: Child Records and Unstored Calc's
                      Stephen Huston

                      The only place unstored calcs on the MANY side are really problematic is if any of the calcs are used as "KEY" fields in relationships.

                       

                      That can keep the relationship from resolving when viewed from the ONE side, though the relationship will still usually be valid when viewed from the MANY side where the calc resides, though it can be slow.

                       

                      You can check the relationship graph(s) to see which fields are KEYs in the relationships to diagnose that problem.

                       

                      Other than that, unstored calcs should be minimized because they slow performance. They will still evaluate on either side of relationships (though they don't work both ways as Keys), but moving them to the ONE side instead of the MANY table greatly reduces how often or how many have to be evaluated.