How to query out the record which has incorrect reference value fields?

[Jinye Gao]

hi there,

there is a table with a reference field called u_company in my application.

someone use scheduled job inserted 20K records into the table.

the value of u_company should be company's sys_id but was set to some random words like description by a miss.

now i want to delete all of 20K records. how should i code with GlideQuery or GlideRecord to query out the record which has incorrect reference value fields?

i have tried addNullQuery(), but it does not work.

thanks.

[Aman Kumar S]

you can use below query, it checks for garbage value, basically there is a value in reference field but no display value is empty

u_company.name=^u_companyISNOTEMPTY

[Jinye Gao]

u_company.name=^u_companyISNOTEMPTY

is that gonna  query out the record with correct reference value also?

[Aman Kumar S · Accepted solution]

No, it will not, that is the way to identify a broken reference value, you can try this as well in your list layout, just to double sure, if your broken reference values are captured.

Since in my user table, there are no garbage value in my reference field, I am getting empty records list.

Now, using script I have injected wrong values in company record, now with the same query, I am getting results.

So the logic is intact, you can use it for fixing your issue

[Jinye Gao]

does anyone has other way?