Calculated Fields should not call GlideRecord or GlideAggregate, Why ?

[Bindhu1]

Hi Team,

We have received recommendation from servicenow that Calculated Fields should not call GlideRecord or GlideAggregate

Steps to resolve issue: Consider using a business rule to replace the calculation. The business rule can perform the calculation when the record is updated.

Is it good to consider using a business rule to replace the calculation for the below table?

How do I resolve this ?

Thanks 

[Shane41]

Hi Bindhu,

It is not recommended to use gliderecord or glideaggregate in a calculated field as the query will need to be called everytime that record is called in a form or list. If your query contains a large number of records this can lead to slow loading times as the query will run for all records in a list

I would recommend creating an update 'after' business rule on the u_fund_transaction table

When: After

Table: u_fund_transaction

Advanced: true

Update: true

Filter conditions: u_amount changes & u_transaction_type = 7 & u_transaction_category=[SELECTED CATEGORY]

Whenever the fund transaction's amount field is updated you should do a query on the con_demand table to update all associated records

Something like this:

var cd = new GlideRecord('u_con_demand');

cd.addQuery('u_fund_transaction',current.sys_id);

cd.query();

while(cd.next()){

cd.u_total_expenses = current.u_amount;

cd.update();

}

The line in red may change based on the name of your fund transaction reference field on the con demand table

Hope this helps,

Shane

[Allen Andreas]

Hi,

To clarify, calculated field values are stored in the database and are not recalculated when the record is called in a form or list.

The above is why a lot of people disliked calculated fields in the first place, because it only updated itself if the record was updated, otherwise, the value was static.

Function fields...on the other hand, are not stored in the database, but are calculated at the time of retrieval. Function field values are always up to date.

Maybe that's what you were thinking?

In any case...

I believe the point here is that the calculated fields are re-calculated every time the record is updated, so the GlideRecord or GlideAggregate call is also done every single time, whereas, if you're using other APIs, as part of that calculation, and can live with it only being updated in specific scenarios, a business rule controls that calculation and only does it then for that field if the condition applies.

Otherwise, calculated fields are fine by design.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Alessandro D_]

Sorry but the first assumption is wrong. You can easily test it by adding a new field to a table and set it as a calculated value. In the script use a date generation like gs.nowDateTime(). You will see that the value is refreshed every time you refresh the form, or even worse, if you add it in the column list, it will be recalculated every time you refresh the list. Of course, you won't see changes if the value doesn't refresh frequently, like a date time, but unfortunately the script is executed at every refresh. So, if you have a glideRecord query, the query will also be executed every time.