Microsoft Flow – Report CRM Cases to Email

 

In the following example I am generating a report of CRM Dynamic Cases and sending it via email.

During my investigation I discovered that you cannot send reports directly from Dynamics, so using flows is a workaround. Not an easy one, but lets see.

 

First, we need connection to Exchange Online and Dynamics CRM.

We are going to get a list of cases, and then do a “foreach” loop to capture the Customer Name, which is stored in a different table, and populate an Array with this data.

Sorry about the localization in the example, I am working form an Spanish computer.





First we are going to start with a Recurrence of once per month, but you can choice wherever you want.

Then we are going to add “Common Data Service” – List records.

You can change the name, and add some filters if you want. In this example I am filtering cases of Statuscode 1 and from specific customer id.



Next step,. I am going to Initialize an empty array. Later I will fill this array with results.

You should seek for “Initialize variable” and set the type to “Array”.

In value, select formulas and write “null”.



Add a Control of “for each one”. This will allow you to travel from each result value and fill the array.



In the “Value” it belongs to the result of “List of Cases”, in the previous step.

Then you will get the Common Data Service “Get record”. This will get only 1 record, because we need to gather the customer name from another place, the Accounts entity. You should use this same example for different kind of data you need for your report.

Last, you will fill the array. Use the “append to array variable”. Select the name of the array you created before, and then you should write the code for the entry. In this example I am creating with Case Title, a customer Case ID, and our Customer Name.

{"Case Title":CASE_TITLE},"CustomerID":CASE_ID,"Customer":ACCOUNT_NAME}

Remember, this is an example, you must try using your own data, but keep the array syntaxis correct.

Next step, you should go away from the “foreach control”. And populate an HTML Table with the array results. This will help you for the email.



In last step, lets create our email. I recommend to use this html syntaxis and put the table in the body. Should do the work.



Code below:

<html>

<head>

  <style>

table, th {

    border:1px solid black;        

}

td {

    border:1px solid black;

    text-align:left;

}

  </style>

</head>

<body>

TABLE_OUTPUT

</body>

</html>

 

If everything went fine, you should try and receive a beautiful message


Here you can find the complete flow, as a picture for guidance.



Comments

Post a Comment

Popular posts from this blog

Update DNS static servers in your local Network

Image
 lets say you need to update your DNS servers, maybe you installed a new Domain Controller in your network. So probably the last step is update in each computer the primary and secondary DNS server. It's sad, but in GPO, you will not find an effective way of doing this for windows10 Instead, you can use this script. $inet1=Get-DnsClient  | Get-DnsClientServerAddress | where{$_.ServerAddresses -contains "192.168.0.245" -or $_.ServerAddresses -contains "192.168.0.207"} foreach($nic in $inet1){     Set-DnsClientServerAddress -InterfaceIndex $nic.InterfaceIndex -ServerAddresses ("192.168.0.245","192.168.0.114") } You can change the old IPs (192.168.0.245 and 192.168.0.207) for new IPs. then, save the file as ChangeDNS.ps1 file in your network Share. Create a New GPO and configure the Schedule Tasks for the Computer node. Configure the task as Immediate Task. Set the user account as SYSTEM. It will automatically convert into NT Authority\system. S...
Read more

LIst all VMs Disk in Hyper-V (VMM)

 Here an easy way to list all your VMs disk.  This will be useful if need to check what disk has every machine.  you can then copy the output to excel  $vms=Get-vm -All $file="listvms.txt" for($i=0;$i -lt $vms.count;$i++){foreach($disk in $vms[$i].VirtualHardDisks.sharepath){ $s=$vms[$i].name+";"+$disk; Write-Output $s | Out-File $file -append utf8}} notepad $file Be aware that second time, you shuld use a different file, otherwise you will be adding the results to the same file
Read more

Powershell Scritp for Inventory VMS in Hyper-V and VMware

Image
 Today I have to create a list of VMS, but with specific information. Usually you will like some basic info from your virtual machines, including, name, amoutn of CPU, RAM and Disk Usage. Seems simple, but the true is a nightmare. The issue is about the Disk usage. When your VMs has only one disk is easy, but what about a machine with 4 disks? What if these disk are thin provisioning and you need to sum the space? Well... here you will learn how, and using simple lines. Get a List of VMS from Virtual Machine Manager If using VMM you can use this method, this will allow a complete list of all hosts and VMS in your environment. #This get the list of all VMs $vms = Get-VM -All #this generate a report including, name, host, status, cpu, ram and disk usage $Report = $vms | select name, vmhost, status, CPUcount, MemoryAssignedMB, @{Name="DiskAllocated";Expression={[math]::round($_.TotalSize/1024/1024/1024,2)}} #this export the report as CSV. Now you can take this to Excel. $Report ...
Read more