Microsoft Dynamics CRM On-Premises | Find Security Role That Has a Certain Privilege

Introduction

Sometimes you will need to show all security roles that are using some privilege. I will show you how to do that using SQL for Microsoft Dynamics CRM On-Premises

Example

Select all security roles that have delete privilege

Code:

SELECT privilege . NAME [Privilege Name] ,
       role . NAME      [Security Role]
FROM   roleprivileges
       JOIN  privilege
         ON privilege . privilegeid =  roleprivileges . privilegeid
       LEFT JOIN  role
              ON role . roleid =  roleprivileges . roleid
WHERE  privilege . NAME LIKE '%delete%'   

That's useful if you're reviewing a big project and need to know for example who has the delete privilege for the account entity, then you can add this condition privilege.NAME LIKE '%account%' so the final SQL query will be as following

Code :

SELECT privilege . NAME [Privilege Name] ,
       role . NAME       [Security Role]
FROM   roleprivileges
       JOIN privilege
         ON  privilege . privilegeid = roleprivileges . privilegeid
       LEFT JOIN role
              ON role . roleid =  roleprivileges . roleid
WHERE  privilege . NAME LIKE  '%delete%'
       AND privilege . NAME LIKE  '%account%' 

How to Convert Numbers To Arabic Word ( VB )

Introduction

In this article, I will talk about converting numbers to written words in Arabic (Tafqeet as pronounced in Arabic).

Background

After googling for while on 'number to arabic words' to find a code to use in SSRS reports, I found a code that was written in SQL that does the converting purpose but unfortunately this code is not supported by CRM because we will manipulate the database.

Then, I googled again to find a VB code to use it with SSRS itself, but with no luck.

Finally, the following code was written to support VB to use it in SSRS directly without manipulating the database, also you can you change the currency that will be used as shown below.

Use The Code

Invoke 'ToArabicLetter' function with the value that you want to convert for example:

Code:

ToArabicLetter('1234.12345'); //الف ومئتان واربعة وثلاثون جنيه مصري واثنى عشر قرشا فقط لا غير

Change Currency

Locate 'NumberAsCurrency' & 'FractionAsCurrency'  function and change what currency you want.

Code:

    Public Function NumberAsCurrency(ByVal givenNumber As Double) As String
        Dim Number, Currency As String

        Number = SFormatNumber(givenNumber)


        Select Case CDbl(givenNumber)
            Case Is = Nothing : Currency = ""
            Case Is = 2 : Currency = " جنيهان مصريان"
            Case 3 To 10 : Currency = " جنيهات مصرية"
            Case Else : Currency = " جنيه مصري"
        End Select

        NumberAsCurrency = Currency

    End Function

    Public Function FractionAsCurrency(ByVal givenNumber As Double) As String
        Dim Fractions, Currency As String

        Fractions = SFormatNumber(givenNumber)

        Select Case CDbl(givenNumber)
            Case Is = Nothing : Currency = ""
            Case Is = 2 : Currency = " قرشان"
            Case 3 To 10 : Currency = " قروش"
            Case Else : Currency = " قرشا"
        End Select

        FractionAsCurrency = Currency

    End Function

To Use it with SSRS

Open SSRS>Right click> Report Proprieties> Locate 'Code' tab> then paste the code

Source Code

    Public Function ToArabicLetter(ByVal givenNumber As Double) As String
        Dim FinalOutput, Number, NumberCurrency, Fractions, FractionsCurrency As String
        Dim Tafkeet = " فقط لا غير"

        Dim WholeNumber() As String = Split(givenNumber, ".")

        NumberCurrency = NumberAsCurrency(WholeNumber(0))
        FinalOutput = NumberCurrency

        If WholeNumber.Length >= 2 Then
            If WholeNumber(1).Length.Equals(1) Then
                WholeNumber(1) = WholeNumber(1) + "0"
            ElseIf WholeNumber(1).Length > 2 Then
                WholeNumber(1) = WholeNumber(1).Substring(0, 2)
            End If

            FractionsCurrency = FractionAsCurrency(WholeNumber(1))
            FinalOutput = FinalOutput + " و" + FractionsCurrency
        End If

            If FinalOutput <> Nothing And FinalOutput <> "" Then
                FinalOutput = FinalOutput + Tafkeet
            End If

            ToArabicLetter = FinalOutput
    End Function

    Public Function SFormatNumber(ByVal X As Double) As String

        Dim Letter1, Letter2, Letter3, Letter4, Letter5, Letter6 As String
        Dim c As String = Format(Math.Floor(X), "000000000000")
        Dim C1 As Double = Val(Mid(c, 12, 1))
        Select Case C1
            Case Is = 1 : Letter1 = "واحد"
            Case Is = 2 : Letter1 = "اثنان"
            Case Is = 3 : Letter1 = "ثلاثة"
            Case Is = 4 : Letter1 = "اربعة"
            Case Is = 5 : Letter1 = "خمسة"
            Case Is = 6 : Letter1 = "ستة"
            Case Is = 7 : Letter1 = "سبعة"
            Case Is = 8 : Letter1 = "ثمانية"
            Case Is = 9 : Letter1 = "تسعة"
        End Select


        Dim C2 As Double = Val(Mid(c, 11, 1))
        Select Case C2
            Case Is = 1 : Letter2 = "عشر"
            Case Is = 2 : Letter2 = "عشرون"
            Case Is = 3 : Letter2 = "ثلاثون"
            Case Is = 4 : Letter2 = "اربعون"
            Case Is = 5 : Letter2 = "خمسون"
            Case Is = 6 : Letter2 = "ستون"
            Case Is = 7 : Letter2 = "سبعون"
            Case Is = 8 : Letter2 = "ثمانون"
            Case Is = 9 : Letter2 = "تسعون"
        End Select


        If Letter1 <> "" And C2 > 1 Then Letter2 = Letter1 + " و" + Letter2
        If Letter2 = "" Or Letter2 Is Nothing Then
            Letter2 = Letter1
        End If
        If C1 = 0 And C2 = 1 Then Letter2 = Letter2 + "ة"
        If C1 = 1 And C2 = 1 Then Letter2 = "احدى عشر"
        If C1 = 2 And C2 = 1 Then Letter2 = "اثنى عشر"
        If C1 > 2 And C2 = 1 Then Letter2 = Letter1 + " " + Letter2
        Dim C3 As Double = Val(Mid(c, 10, 1))
        Select Case C3
            Case Is = 1 : Letter3 = "مائة"
            Case Is = 2 : Letter3 = "مائتان"
            Case Is > 2 : Letter3 = Microsoft.VisualBasic.Left(SFormatNumber(C3), Len(SFormatNumber(C3)) - 1) + "مائة"
        End Select
        If Letter3 <> "" And Letter2 <> "" Then Letter3 = Letter3 + " و" + Letter2
        If Letter3 = "" Then Letter3 = Letter2


        Dim C4 As Double = Val(Mid(c, 7, 3))
        Select Case C4
            Case Is = 1 : Letter4 = "الف"
            Case Is = 2 : Letter4 = "الفان"
            Case 3 To 10 : Letter4 = SFormatNumber(C4) + " آلاف"
            Case Is > 10 : Letter4 = SFormatNumber(C4) + " الف"
        End Select
        If Letter4 <> "" And Letter3 <> "" Then Letter4 = Letter4 + " و" + Letter3
        If Letter4 = "" Then Letter4 = Letter3
        Dim C5 As Double = Val(Mid(c, 4, 3))
        Select Case C5
            Case Is = 1 : Letter5 = "مليون"
            Case Is = 2 : Letter5 = "مليونان"
            Case 3 To 10 : Letter5 = SFormatNumber(C5) + " ملايين"
            Case Is > 10 : Letter5 = SFormatNumber(C5) + " مليون"
        End Select
        If Letter5 <> "" And Letter4 <> "" Then Letter5 = Letter5 + " و" + Letter4
        If Letter5 = "" Then Letter5 = Letter4


        Dim C6 As Double = Val(Mid(c, 1, 3))
        Select Case C6
            Case Is = 1 : Letter6 = "مليار"
            Case Is = 2 : Letter6 = "ملياران"
            Case Is > 2 : Letter6 = SFormatNumber(C6) + " مليار"
        End Select
        If Letter6 <> "" And Letter5 <> "" Then Letter6 = Letter6 + " و" + Letter5
        If Letter6 = "" Then Letter6 = Letter5

        SFormatNumber = Letter6

    End Function

    Public Function NumberAsCurrency(ByVal givenNumber As Double) As String
        Dim Number, Currency As String

        Number = SFormatNumber(givenNumber)

        If Number <> "" And Number <> Nothing And givenNumber <= 2 Then
            If Number.StartsWith("واحد") Then
                Number = Number.Substring(4)
            ElseIf Number.StartsWith("اثنان") Then
                Number = Number.Substring(5)
            End If
        End If

        Select Case CDbl(givenNumber)
            Case Is = Nothing : Currency = ""
            Case Is = 2 : Currency = " جنيهان مصريان"
            Case 3 To 10 : Currency = " جنيهات مصرية"
            Case Else : Currency = " جنيه مصري"
        End Select

        NumberAsCurrency = Number + " " + Currency

    End Function

    Public Function FractionAsCurrency(ByVal givenNumber As Double) As String
        Dim Fractions, Currency As String

        Fractions = SFormatNumber(givenNumber)

        If Fractions <> "" And Fractions <> Nothing And givenNumber <= 2 Then
            If Fractions.StartsWith("واحد") Then
                Fractions = Fractions.Substring(4)
            ElseIf Fractions.StartsWith("اثنان") Then
                Fractions = Fractions.Substring(5)
            End If
        End If

        Select Case CDbl(givenNumber)
            Case Is = Nothing : Currency = ""
            Case Is = 2 : Currency = " قرشان"
            Case 3 To 10 : Currency = " قروش"
            Case Else : Currency = " قرشا"
        End Select

        FractionAsCurrency = Fractions + " " + Currency

    End Function

Infinite loop at CRM

Definition of infinite loop:-

An infinite loop (or endless loop) is a sequence of instructions in a computer program which loops endlessly, either due to the loop having no terminating condition, having one that can never be met, or one that causes the loop to start over.

How it happens in CRM:-

CRM detects an infinite loop with two variables 'depth' and 'time', the theory says that if some code (Plugins or Workflows) had been called in one transaction for 8 times (default value of max depth) in 1 hour (default value of time), the system would detect that as infinite loop.

The Depth is passed to plugins or to custom steps as follows:-

• For plugins:

// Obtain the execution context from the service provider.
IPluginExecutionContext context =
(IPluginExecutionContext) serviceProvider.GetService(typeof(IPluginExecutionContext));
 // Obtain the depth
var depth = Context.Depth;

• And for Custom Steps:

var workflowContext = context.GetExtension<IWorkflowContext>();
 // Obtain the depth
var depth = workflowContext.Depth;

If plugin had been called from the user directly (for example you created a record) the depth would be 1,

But if it had been called from workflow or another plugin (for example a workflow updated a field that fires the plugin) then the depth would be 2.

So, it's not guaranteed to do the following check to prevent the infinite loop

Code:

If(depth > 1)
return;

And it's better to solve the problem instead of working around it.

There are some cases when a plugin might need to update itself but for the majority of the time avoid doing because it’s confusing/complex and there is usually a better way.

e.g.

 scheduled process

I had tried to use Recursive or Scheduled Workflows approach

For every 15 minutes it calls itself, according to CRM infinite loop definition above it will not countered this as an error, because if we reached the 8th time then, 1 hour and 45 minutes would be passed. For example if we started the workflow at 12:00 then when it would reach 8th at 1:45.

But unfortunately the CRM will detect this as an infinite loop error

So, we can rephrase the definition above 

"If some code (Plugins or Workflows) had been called in one transaction for 8 times (default value of max depth) in 1 hour (default value of time), the system would detect that as infinite loop".

Finally, you can make external tool that runs for every X of time and check what's failed from the infinite loop and re-run it again.

Tip:

You can find the maximum depth and the default value of time by running this query on MSCRM_CONFIG database

SELECT messageprocessormaximumdepth, messageprocessorminimuminactiveseconds
FROM   deploymentproperties 

Quick way to know what's dirty on CRM form

Here is a quick way to know what fields are dirty on CRM form by a single click.

Put this line of code into step 5

javascript: var form = $("iframe").filter(function() {    return $(this).css("visibility") == "visible"})[0].contentWindow;try {    form.Mscrm.InlineEditDataService.get_dataService().validateAndFireSaveEvents = function() {        return new MscrmSaveResponse(5, "")    }} catch (e) {}var attrs = form.Xrm.Page.data.entity.attributes.get();for (var i in attrs) {  if(attrs[i].getIsDirty())   console.log(attrs[i].getName());}

Finally, to use it