How to handle dynamic timezone-shifting on String/Choice fields for Heatmap reporting?

[SS1510]

Hi @all,

I am facing a requirement conflict regarding capturing and reporting on the Hour of Day and Day of Week for Incidents. I need advice on how to handle this platform limitation, or if a viable workaround exists.

The Requirements:

1. We have two custom string fields on the Incident table: u_hour_of_day (e.g., '04') and u_day_of_week (e.g., 'Monday').
2. These fields need to display on List views and Forms matching the logged-in viewer's timezone (exactly like the native sys_created_on date/time field shifts on-the-fly depending on who looks at it).
   
3. The business explicitly wants to use these specific custom fields to group data on a standard platform Heatmap report.
4. We have over 26,000 historical records that need a backfill.

The Conflict:

• If we use a Before Business Rule or a Fix Script, it hardcodes static text strings into the database based on the session of the user saving/running the script. When a user from another timezone views the list, the string value does not shift.
• If we use Scripted Calculated Fields (Calculated Value in Dictionary), the text values shift beautifully on forms and list views based on gs.getSession().getTimeZoneName(). However, this completely breaks aggregate reporting. When building a Heatmap, the database groups the records at the table tier before the user session context is loaded, returning static or scrambled data groups.
  
  Does anyone has any suggestion or advice on this?

[joshuajacks]

@SS1510 

String fields don't have an inherent database value vs display value like date time fields. Date time fields store info in utc and display it depending on the user but I don't think there's a way to mimic that behavior for a custom string field.
Clarifying questions:
1. Is that data meant to be static or dynamic? You mentioned trying a fix script which makes me think it's being set once based on existing incident data.
2. What insight are you trying to get from the heatmap? There may be a better alternative or different fields and conditions that could help answer the same underlying question?
3. Is there any opposition to creating a new custom field if needed? If dynamic date display value is what you're looking for then using the oob date/time field may be a better alternative.