filter is not working on calculated field

[xuan]

Hi guys,

I've created a checkbox filed with calculated value.

It's working fine, so i have some true values and false values.

When i filter on true, it gives me no record.

When i filter on false, it gives me all the records includes the true ones.

Why? I don't understand.

Cheers,

Xuan

[Chuck Tomasi]

Hi Xuan,

Did you create the field with the calculated value after the table was already in production (i.e. did records exist before they had this field available?)

If so, then they are calculating the value for list display only and don't have anything stored in the database (where the filter is querying from.)

You'll need to run a script in scripts-background to update all existing records to save the calculated field in the database for each record (and be sure to use autoSysFields(false) and setWorkflow(false) and setForceUpdate(true)) to go in stealth mode.

[paramveer]

Hi Xuan,

It seems that you have recently updated the field to calculated value.

• Calculated value behave like default value. So if you creates a new record in table, it will automatically set that value as par calculated value.
• If records were created before calculated value implementation, in that case they will not have calculated value stored in system and filter condition(as u described in your question) will not work.
• when you open the form , value will display by calculation but in back-end(database) that value will not store until you will not save the form. If you save the form then it will save the value in database and filter condition will also work.
• So in order to set all the values in database, you need to run background script and update all the check-box value once. In future your calculated filed will work but for previously created record you need to run background query once.

~Paramveer Singh

~Senior Developer

Please mark Helpful, Like, or Correct Answer if applicable.

[Chuck Tomasi · Accepted solution]

Navigate to Scripts - Background...

Your script will look something like this (substitute incident for the table your created the field on)

var r = new GlideRecord('incident');
r.query();

while (r.next()) {
         r.setForceUpdate(true);
         r.autoSysFields(false);
         r.setWorkflow(false);
         r.update();
}

[xuan]

Thanks a lot.

The filter works after I ran that background script.