Skip to main content


Showing posts from April, 2019

Formats for dealing with dates and datetimes in Salesforce

The correct way to format dates (not datetimes!) for bulk importing CSVs into Salesforce looks like year-month-day, so today would look like 2019-06-17.  These default to the timezone set for the user importing them, so there's no need to convert to UTC.  Set this in Excel by

Select the column/s containing datesRight click on them and select "Format Cells" from the menuSelect the format "2012-03-14"Save as CSV (not the UTF-8 type of CSV)

For programmatic imports, the correct .NET Framework DateTime format string to use for a Salesforce Date/time is:
You can get the same thing in SQL using CONVERT(VARCHAR, datefield, 127) + 'Z'. This also has the added advantage that Excel doesn't interpret it as a date, so you don't have to do the steps above.

Regardless of where you're getting the datetime value from, make sure it's in UTC! If the time represents the current moment, use DateTime.UtcNow

You might be tempted to use…

DIY Salesforce partial copy sandbox using SandboxBerry

Salesforce partial copy sandboxes are expensive, but it's possible to approximate one if you're willing to spend a little time moving the data around yourself with a fantastic little tool called SandboxBerry, which you can get from here. Big thanks to Ian Finch for all his work on it!

Below are the steps I had to go through to get all my data imported. Please let me know if you have any suggestions that could make this easier or more effective!

Make a new development sandbox and log into itDelete all outbound messages and workflows that relate to integrations with external systemsSave a copy of /ExampleInstructionFiles/ExampleInstructionFile.xml, henceforth called InstructionFile.xmlDecide which object types you want to migrate and add them as SbbObjects in InstructionFile.xmlDeactivate validation rules, mandatory fields, lookup filters and autonumber fields for those objects (TODO: write a script to do this by exporting metadata, disabling them all programmatically, then re…

Useful Azure AD Powershell snippets

I've been doing a lot of Azure AD stuff lately, so here are some powershell snippets that have been coming in handy.

I always forget to Connect-AzureAD first, so don't do that

Get extension properties of a user (e.g. created date)
Get-AzureADUser -SearchString 'username or email addy' | select -ExpandProperty ExtensionProperty

Get guest users that are not members of a specified group
$allGuests = Get-AzureADUser -Filter "usertype eq 'guest'" -All $true
$groupMembers = Get-AzureADGroup -SearchString 'group-name' | Get-AzureADGroupMember -All $true
$allGuests | where {$groupMembers -notcontains $_ }

Guest users that have not accepted their invitations to join Azure AD
Get-AzureADUser -Filter "usertype eq 'guest'" -All $true | where UserState -eq PendingAcceptance

Add a big list of users to a group Assuming all the usernames are in a text-file, one line each:

$group = get-azureadgroup -SearchString "group name"

Get details and creators of all Salesforce reports that reference a specified field

If you're ever modifying a Salesforce field and you want to contact everyone who has created reports using the field, here's how you do that using Powershell and the CLI (which you can also install via chocolatey):

set-location C:\wherever
force export
$reportFiles = get-childitem -Path .\src\reports\ -Recurse | sls "field_to_search_for"

# I've included some extra fields here, they might come in useful
$reportData = force query --format=json "select id,,, developername, name, foldername, isdeleted, lastrundate, lastvieweddate from report" | convertfrom-json
$matchingReports = $reportData | where { $reportFiles.Filename -eq $_.DeveloperName + ".report" }

$matchingReports | % { $ + ' created report "' + $ +'"' }
# send an email to these folks: $ | select -Unique

Salesforce bulk data load job fails with message 'InvalidBatch : Field name not found: Id'

After creating a bulk / batch job, I'll sometimes see that it failed before even starting (failed batches:1, but with 0 records failed), with the following error:

InvalidBatch : Field name not found: Id

Annoyingly, this is caused by the inability of the Salesforce bulk import process to handle anything but ASCII.  Try saving your CSV again, making sure it's in ASCII (called "CSV (Comma delimited)" in Excel) and not using UTF-8 or some other encoding.  No idea how you're supposed to insert that new contact named José, though...