How to handle dynamic timezone-shifting on String/Choice fields for Heatmap reporting?
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
an hour ago
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.
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:
- 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').
- 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).
- The business explicitly wants to use these specific custom fields to group data on a standard platform Heatmap report.
- 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?
0 REPLIES 0
