4 Replies Latest reply on Aug 4, 2013 5:57 PM by user19036

    make a new calculated field, or bring in the info via related table?

    user19036

      When I have a field that belongs in Table A and I want to bring it in from the context of table B, I do one of these two things:

       

      1) From a layout based on Table B, I put the field from Table A into it via a relationship.

      OR

      2) Create a field in Table B, that matches the field in table A, via calculation, either a Calculation field, or a Text field that auto enters a Calculation.

       

      Is one of these ways much better than the other?

       

      Thank you in advance!!

      Darryl

        • 1. Re: make a new calculated field, or bring in the info via related table?
          Mike_Mitchell

          Darryl -

           

          Under normal circumstances, it's poor practice to echo the value of a field into a second table. It's a violation of good relational design (which basically says, in part, that you only store data in a single place to avoid bloating the size of the database unnecessarily). There are only a few situations where you would want to do this:

           

          1) You need the field from the related table as a key on the "child" side of a relationship (i.e. it needs to be indexed).

          2) You need the field value from the related table to be static (i.e., not to reflect changes in the related table as those data are updated).

          3) You need to base a calculation on the field in the parent table and need that calculation to be indexed for performance during searches or as the "child"-side key in a relationship.

           

          Situation 2 might occur if, for example, you have a Products table and an Invoice table (with associated Line Items). In such a case, it would be beneficial to reflect, for example, the price of a product at the time it was invoiced, not its current price, for historical purposes. Similar situations can occur in other applications as well.

           

          HTH

           

          Mike

           

          Edit: There's one other situation I just thought of. There are certain situations where you're showing a set of related records in a portal and those records are reflecting records in other tables via relationships where simply showing a related field in the portal may show incorrect data (for example, a join table). In those situations, it may be necessary to echo the grandchild data into the child table so the portal will show correct information.

          • 2. Re: make a new calculated field, or bring in the info via related table?
            user19036

            Mike,

             

            Thanks for all that!

            I do have one situation like your #2 example. I'd like to show information from a Parent Table in the Child Table that is accessed through a portal. (The Layout where this happens is based on an entirely other table) But I want to be able to overwrite that info sometimes.

            I am not sure whether the new field I create in the Child Table should be a calculation field, or a text field with auto entered calcualtion.

             

            Darryl

            • 3. Re: make a new calculated field, or bring in the info via related table?
              Mike_Mitchell

              Darryl -

               

              If you want to be able to overwrite the information, a calculation field won't work. You can use either an auto-enter calculation, or a lookup.

               

              Mike

              1 of 1 people found this helpful