Interested to understand how you manage lookup tables and changes that are made to these tables.
I am utilising a Table for storing Codes (Department and Cost Codes). These are managed by the Administrator and are linked to other Tables via Lookup. Users access theses Table and select the Department/Cost Code via the Lookup (Drop Down Menu). This works fine until a change is required to the Table storing the Department/Cost Codes. Any change made to the existing values will ripple through and change historic records. I have contemplated copying the Users selection at the point they select the Department/Cost Code but this could lead to a situation where the Edit value in the Drop Down box is different from the Stored value?
I hope that makes sense!
Thanks Scott
ID 568
Category TeamDesk
Author Scott Miller
Date Created 10/8/2012 6:18:17 AM
Date Updated 3/27/2015 3:16:15 AM
Comments
Slava Shinderov Staff 10/8/2012 8:04:30 AM
@Scott in similar case we recommend doing following:
PROBLEM: The price should never change for the invoice item even if the catalog price changes at later date in related Product table.
SOLUTION: Create Price column in the Invoice Details table and use Product's Price lookup as its default/calculate value.
This way if any change in price table will occurs - particular invoice details record will remain unchanged.
Rick Cogley 1/10/2013 12:11:42 AM
Yes, we do what Slava mentions, and sometimes also write the value to a history table, so we can see the changes over time (like to exchange rates used in expense calculations).
martin oliver 1/11/2013 6:14:38 AM
Obviously this only works when this is the last table to be edited where the value may be derived from formulas using values in other tables. I have a problem where this is the first table edited and subsequent editing of linked tables, which result in the correct value in the formula column but obviously does not change the default/calculate column. Has anyone got any suggestions on how to get this column value to change without having to re-edit the first table.
Thanks
Martin
Valencia Benjamin 6/30/2013 4:04:27 PM
Still new to this. I have no idea what you guys are talking about.
rishi uttam 3/27/2015 2:04:41 AM
Hi
Do you think you can use the "New Summary" Option and select Aggregate options sorted by Date created. ? This way it will always pull the latest price??
How did you solve your problem as this posts go back to 2013.
Slava, How do you lookup in default/calculate value a field from another table?
Slava Shinderov Staff 3/27/2015 3:16:15 AM
@Rishi my suggestion from above is for following case: You've product table with a price column which is used via lookup in invoice details for price calculation. If later you'll edit price in product table and don't wish to break your historical data, then you can store price in details records with help of Default/Calculate property of separate physical column.
