question Re: Comparing updated_by and opened_by fields from task table in Developer forum

Comparing updated_by and opened_by fields from task table

Anthony Okoth — Fri, 27 Sep 2024 10:10:37 GMT

I have a custom incident table that is extending the task table. I would like implement a logic that:

Re-opens incidents from this table that are in state "Solution Proposed" if the person who opened the incident updates it within 14 days after the incident changes to state "Solution Proposed". So updated_by == opened_by.
Auto-closes incidents that have been in the state "Solution Proposed" for more than 14 days.

I am seeing that the ootb task table field updated_by is a String field containing the email of the user that updated the ticket last. The opened_by field however, with which i would like to make the comparison on, is a reference field. Is dot-walking on the opened_by field to find the email and then transforming it to a String the only way to make a comparison between the two fields? And why is the updated_by field a String and not a Reference?

Re: Comparing updated_by and opened_by fields from task table

HIROSHI SATOH — Fri, 27 Sep 2024 13:11:47 GMT

I think it's a good idea to use dot walking to get the email address.

Why updated_by is a String and not a Reference:

The updated_by field is likely designed as a string for efficiency, as storing user information directly as a string might reduce database lookups for frequently updated fields. Since it's often a field that changes regularly, keeping it as a simple data type could be advantageous in terms of performance, even though it sacrifices the flexibility of reference fields.

It's also possible that they're simply holding onto an older design.

Re: Comparing updated_by and opened_by fields from task table

Rajesh Chopade1 — Fri, 27 Sep 2024 13:23:49 GMT

hi @Anthony Okoth

you can achieve this requirement by BR or Scheduled job. Following sample script will help you:

Why updated_by is a String?

The updated_by field is a string field because it is intended to hold the email address (or a similar identifier) directly, making it easier to reference or log without needing to perform additional lookups. This allows for quick access to the last updater’s identifier without needing a database join.

i hope my answer helps you to resolve your issue, if yes please mark my answer helpful and correct.

thank you

rajesh