How to query on work notes

[shibinmonjoseph]

Hi all,

I need to delete custom table records after 1 year from closure if the work note does not contain few keywords such as 'abc', 'xyz' etc.

I am unable to query on work_notes field as its a journal input field.

How to add OR conditions in work_notes field?

[Ankur Bawiskar]

Hi,

journal data is stored in sys_journal_field table

you can query your custom table and then check in sys_journal_field for work_notes column

Regards
Ankur

[shibinmonjoseph]

Thanks @Ankur Bawiskar for the reply, this is helpful.

My requirement is to delete the closed records after 1 year if the work notes, description or short description does not contains the words 'abc' or 'xyz'.

I think a scheduled job is the best approach but I am confused on the script, can you help?

[Ankur Bawiskar]

Hi,

yes monthly or weekly scheduled job should be fine

Steps

1) Query table with description or short description does not contain abc or xyz and created 1 year ago

2) Iterate and query in sys_journal_field for this record sys_id against work_notes and check if it contains abc or xyz

3) if yes then delete

You can also use flow designer with no scripting involved

Regards
Ankur

[Ankur Bawiskar]

@shibinmonjoseph 

Hope you are doing good.

Did my reply answer your question?

If my response helped you please mark it correct to close the question so that it benefits future readers as well.

Regards
Ankur