Tech tools

Links to other tools that may be useful when dealing with Salesforce

Excel that convert SF datetimes to Excel format and vice versa

Public Function ConvertSFDateTime(cel As Range) As Variant

Dim strSFDateTime As String, strSFDatePortion As String, strSFTimePortion As String, strCel As String

Dim sglCharT As Single, sglCharZ As Single, sglTimeLen As Single

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False

strCel = CStr(cel.Value2)

If strCel <> vbNullString Then

  ' +1 added, so that it returns the rightmost part of the string starting at the first character after "T"
  sglCharT = InStr(1, strCel, "T") + 1
  sglCharZ = InStr(1, strCel, "Z")
  sglTimeLen = sglCharZ - sglCharT - 1
  
  strSFDatePortion = Left(strCel, 10)

  strSFTimePortion = Mid(strCel, sglCharT, sglTimeLen)
  
  ConvertSFDateTime = Format(DateValue(strSFDatePortion) + TimeValue(Replace(strSFTimePortion, ".00", "")), _
    "yyyy-mm-dd hh:mm:ss")
  
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
  
End If

End Function

Public Function ConvertToSFDateTime(cel As Range) As String

Dim strCel As String, strDatePart As String, strTimePart As String

Dim dateCel As Date

Dim sglFractionalLocation As Single

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False

' VBA won't handle fractional seconds, finds the character number in the string where a period occurs
sglFractionalLocation = InStr(1, cel, ".")

' Strips out the fractional second
If sglFractionalLocation > 0 Then
        strCel = Left(cel, sglFractionalLocation - 1)
    Else
        strCel = cel
End If

If IsDate(strCel) Then
    strDatePart = DateValue(strCel)
    ' Determines if time component exists in the value of cel
    If DateValue(strCel) = CDate(strCel) Then
            ConvertToSFDateTime = Format(strCel, "yyyy-mm-dd")
        Else
            ConvertToSFDateTime = Format(strCel, "yyyy-mm-ddThh:mm:ssZ")
    End If
End If

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

End Function


Public Function ConcatenateMult(rngConcatenateCells As Range, bAddSingleSpace As Boolean, _
Optional strDelimiter As String, Optional strWrapCellValue As String)

Dim rngCel As Range, rngLastCel As Range

Dim bCompareCells As Boolean, bWrapCellValue As Boolean

Dim lngLastRow As Long, lngLastCol As Long

Dim strWrapTrue As String, strWrapFalse As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False


' Sets the bounds for the last row and column from the rngConcatenateCells argument
lngLastRow = rngConcatenateCells.Row + rngConcatenateCells.Rows.Count - 1
lngLastCol = rngConcatenateCells.Column + rngConcatenateCells.Columns.Count - 1

Set rngLastCel = Cells(lngLastRow, lngLastCol)

' Loops through each cell in rngConcatenateCells
' If bAddSingleSpace is True, adds a single space in-between concatenated cells
' If optional strDelimiter argument is provided, adds delimiter between each concatenated value
' If optional strWrapCellValue argument is provided, wraps each concatenated cell with that delmiiter
For Each rngCel In rngConcatenateCells
    bCompareCells = (rngCel.Address = rngLastCel.Address)
    
    ' Sets bWrapCellValue to true and applies strWrapCellValue,
    ' only if the length of the argument is greater than 0 characters
    '
    ' NOTE: [Uses CStr(Replace(rngCel, "'", "\'")) to add the escape "\" character
    ' before all apostrophes, so errors don't occur with SOQL queries
    '
    ' Change this back to CStr(rngCel) if you need to concatenate values without the escape]
    If Len(strWrapCellValue) > 0 Then
        bWrapCellValue = True
        strWrapTrue = strWrapCellValue & CStr(Replace(rngCel, "'", "\'")) & strWrapCellValue
    Else
        bWrapCellValue = False
        strWrapFalse = CStr(Replace(rngCel, "'", "\'"))
    End If

    ' bCompareCells determines if the cell's address is equal to the last cell's address in the range
    Select Case bCompareCells
        ' If true, the strDelimiter and strWrapCellValue arguments are not applied; function ends
        Case Is = True
            If bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapTrue & " "
            If Not bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapFalse & " "
        
        ' If false, continues to concatenate cells
        Case Is = False
            Select Case Len(strDelimiter)
                Case Is = 0
                    
                    ' Adds a single space in between values
                    If bAddSingleSpace Then
                        If bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapTrue & " "
                        If Not bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapFalse & " "
                    Else
                        If bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapTrue
                        If Not bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapFalse
                    End If
                Case Else
                    If bAddSingleSpace Then
                        If bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapTrue & strDelimiter & " "
                        If Not bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapFalse & strDelimiter & " "
                    Else
                        If bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapTrue & strDelimiter
                        If Not bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapFalse & strDelimiter
                    End If
            End Select
    End Select
Next rngCel

' Hardcoded parentheses added for SFDC conversions -
' Apex Data Loader, Force.com Explorer, and other SOQL query tools require format of values
' in an IN clause as:
'
' ('[value1]', '[value2]', '[value3]',...,'[value(n)]')
'
' (e.g. Select Id, Name FROM Contact WHERE Account.Name IN ('Kimberly-Clark', 'IBM'))
ConcatenateMult = "(" & Trim(ConcatenateMult) & ")"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

End Function

PSCSV

https://github.com/SFXD/PSCSV

A powershell script to help Salesforce admins and consultant to save time and do data load operations without having to rely on Excel.

Current list of stuff the script can do:

@tsalb's "getting started with VSCODE" list

1) To get started on VSCode + SFDX CLI:

2) Look at the VSCode / Environment / SFDX CLI modules (Ignore anything about LWC and JS):

3) VSCode + ForceCode is also a possibility (you need to set up VSCode first, but you can ignore SFDX CLI / Salesforce Extensions)

4) This is a tool to help you construct a package.xml if you decide to go with SFDX CLI + Salesforce Extensions

5) I found this vscode extension which can generate you a raw package.xml. I use this with my SFDX org development.

Getting the Case Reference Id

# in email {!Case.Thread_Id}
​

in apex/other

After Winter'16: Your org still has the broken ref ID, but we no longer parse them, because there is no expectation that we would, and the org ID is incorrect. And we no longer parse it due to a change on our end where we tightened the org ID requirement.

For your reference you can look at below example on how reference it is generated - WE DON'T SUPPORT THE FORMULA BELOW AND FORMAT CAN CHANGE AT ANY TIME:

Organization ID Format: 00DXXYYYYYYYYYY

Case ID Format: 500AABBBBBBBBBB

Acceptable Thread ID Formats: (1) ref:_00DXXyyyyyyyyyy._500AAbbbbbbbbbb:ref (2) ref:00DXyyyyyyyyyy.500Abbbbbbbbbb:ref

For example (example org id): Organization ID = 00D50000000IQwR Case ID = 5005000000PQo5L

Thread ID = ref:_00D50IQwR._50050PQo5L:ref

Custom Formula:

"ref:_"&LEFT( $Organization.ID,5)&SUBSTITUTE(RIGHT($Organization.ID,10),"0","")&"._"&LEFT(Id,5)&SUBSTITUTE(LEFT(RIGHT(Id,10),5),"0","")&RIGHT(Id,5)&":ref"

NOTE: An important disclaimer here is that any formula you use for this purpose could one day begin failing if the format of the Case ID or Thread/Reference ID changes.

Useful Regex

Profile Permissions

Use this regex to massively replace profile permissions in your editor.
Note that for the .profile to contain the field permissions, you will have to query both at the same time. This is because the metadata API only gives permissions tied to what you are currently requesting - which also explains some changeset behaviour when deploying profiles.

Replace YOUROBJECTNAMEHERE byt the name of the object you want to set the permissions for. This regex finds permissions for all Custom fields on that object and sets them to TRUE/TRUE (editable, writable).
If you wish to replace permissions accross all custom objects for example, simply replace YOUROBJECTNAMEHERE by .*__c

Find:

(<fieldPermissions>\s*<editable>).*(</editable>\s*<field>YOUROBJECTNAMEHERE.*__c</field>\s*<readable>).*(</readable>\s*</fieldPermissions>)

Replace:

$1TRUE$2TRUE$3

Reset Passwords Using Dev Console

1. Open Developer Console

2. Click the Debug dropdown menu and select the Open Execute Anonymous Window

3. Enter the following to manually set a new password for a user:

User usr = [select Id from User where username='ENTER_USERNAME_HERE'];
System.setPassword(usr.Id,'ENTER_NEW_PASSWORD_HERE');