I've actually figured this one out, but wanted to share the problem and solution for others to maybe benefit from!
I have a few custom objects I've set up to track contracts for broadcast purchases (buying air time on stations around the world) and have built a Flow that takes information from the Contract details and builds out a series of records that represent each actual broadcast. There's a bunch of reasons for this, but mainly so we can query something to figure out what show is showing on what channel and when!
The problem I was having is that the Contract collects information on show times in a numerical entry (since there's no Time datatype and having a Datetime datatype field for each broadcast would be tedious). So when running through the Flow to create the broadcast records, I was assembling the records broadcast dates and times using Formulas.
Unfortunately, what I discovered is that doing this causes Salesforce to store the records in UTC. Which threw off the actual recording since our users are mostly EST... So recording an 8am show was showing up as a 4am show (since it was stored in the DB at 8am UTC, which is 4 hours earlier than EST... at the moment... more on Daylight savings time later!),
This was also the time I realized that these contracts will come in with Timezones not in EST and I can't ask our data entry folks to do the conversions in their head!
So, I needed to update my solution with two things:
1. The ability to set the timezone of the contract
2. Allow for the fact that the formula will save the string literal into UTC
So, here's how I did it!
@System Administrators
@Automation and Workflow (ARCHIVED)
@Configuration & Customization (ARCHIVED)
I had been desperately trying to avoid Apex code, but ultimately had to use some for one simple reason: Salesforce doesn't have good time calculation capabilities outside of Apex!
The main logic I needed was a way for a user to select a timezone and then have a formula use it.
Well, formulas can't use timezones in their calculations, but Apex can!
So, after a bit (a lot) of digging around I found a few pointers, but ended up assembling something from the Apex docs.
For anyone who wants to just get to the Apex code and not read through my explanation (guiilty!) here you go:
<code>
global class TimeZoneConverter
{
@InvocableMethod(label='Convert Timezone DateTime' description='Converts a date time timezone string to a DateTime value')
public static List<Datetime> UTCdateValue(List<TimezoneConvertRequest> ConvertMe)
{
Timezone tz = Timezone.getTimeZone(ConvertMe[0].tzRequest); //now I have an official Timezone datatype variable tz
Datetime dt = Datetime.parse(ConvertMe[0].dtRequest.format()); //now I have a UTC Datetime string
Integer offsetMS = tz.getOffset(dt); //now I have the Milliseconds difference between the timezone selected and the UTC datetime string
Integer offsetHrs = (((offsetMS / 1000)/60)/60)*-1; //now I have the offset in hours
Datetime adjdt = dt.addHours(offsetHrs); //should be the adjusted value.
List<Datetime> returnMe = new List<Datetime>();
returnMe.add(adjdt);
return returnMe;
}
global class TimezoneConvertRequest {
@InvocableVariable(required=true)
public String tzRequest;
@InvocableVariable(required=true)
public Datetime dtRequest;
}
</code>
To start with I created a Global Picklist with entries for each of the timezones in Java Server format (you can find this list out there with a quick googling - I'm happy to provide it too if you need it!)
Then I created a simple Apex global class using the @InvokableMethod designation (so you can pass it values and get values back from a Flow) and added a pair of @InvokableVariables at the end to accept information from to the Flow.
In this class, I added a Timezone variable that I passed the value from the Global Picklist using the getTimeZone method.
Then I converted my string (see original explanation) representing the datetime for the actual broadcast to a Datetime variable using the parse method.
Now here's the magic bit... I then calculated the integer offset between the datetime variable and the timezone variable using the TimeZone.getOffset method. This gave me the offset back in milliseconds. Quick conversion and I had hours.
Finally, I calculated the correct UTC value for the entry to be stored in by adding the inverse (*-1) value to the datetime variable using the addHours method.
Now, when I store that corrected UTC value, all displays of that date time will be appropriate to the user's timezone!
A long explanation I know, but it took me several days to ponder through this and I wanted to share! Happy to answer any questions if I can!
