How to query calculated field?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-06-2010 11:43 AM
There is table called "Table 1" to store all the types of assets of a business and a table "Table 2" to store the assets based on the asset type in Table 1.Table 2 is related list for Table 1.
Fields on Table 1:
1.Asset Type
2.Count
For a particular asset type in table 1 I have assets in Table 2.The count field stores the number of assets in Table 2 for that asset type.Now to store this count I am querying Table 2 and finding the number of assets for that asset type and update the count field with that value.The count field is a calculated field.I have written above query in calculation field of the count field.Whenever any new asset is created in Table 2,the count field for that asset type in Table 1 will be updated automatically.
I have few questions regarding the approach I am using.
Is this a correct approach to update the count field?
Will this approach hamper the performance?
Is there any alternative?
- Labels:
-
Enterprise Asset Management
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-06-2010 12:27 PM
Calculated fields are inherently flawed in that you cannot query the calculated value. When you do a query on a calculated field, you are querying on the value stored in the database (as of the last update) which may be different from the current calculation.
A better approach might be to have an "after" business rule on insert/update/delete of records in table2 that updates the appropriate record in table1 with the correct Count value.