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.
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.
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.
1 of 1 people found this helpful
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.