Engaging, Forging, and Enjoying relationship between Business, Technology, Idea, Love, Microsoft, and CRM in Our Real Life
Friday, 30 January 2015
How to See Campaign Responses from Related Contact/Account/Lead
SQL Query in CRM 2011/2013/2015 Connection Entity
Now what if you want to run SQL Query in your SSRS Report, it might be not as easy as you did through Advanced Find because CRM does not store the Contact fields in the Connection table.
Here is the sample of SQL Query to retrieve records from Connection Entity.
select record2idname as ConnectedTo, record2roleidname as RoleTo, record2objecttypecodename as EntityRoleTo, --Account record1idname as ConnectedFrom, record1roleidname as RoleFrom, record1objecttypecodename as EntityRoleFrom, --Contact --Account fields (Connected To) faccount.address1_composite as OrganizationAddress1, faccount.address2_composite as OrganizationAddress2, faccount.fax as OrganizationFax, faccount.telephone1 as OrganizationPhone, --Contact fields (Connected From) fcontact.address1_composite as ContactAddress1, fcontact.address2_composite as ContactAddress2, fcontact.fax as ContactFax, fcontact.telephone1 as ContactPhone from filteredConnection fconnection --make sure that only show Connection connect from Contact inner join FilteredContact fcontact on fconnection.record1id = fcontact.contactid and fcontact.statecode = 0 --make sure that only show Connection connect to Account inner join FilteredAccount faccount on fconnection.record2id = faccount.accountid --on fconnection.record2id = faccount.accountid --this is the criteria, I have no individual sub type field for querying GP --so I leave this one for you I just filter by active status where fconnection.statecode = 0 -- If you want to filter only show connection -- between Account and Contact please add following line, -- but you need to join to other table to show all -- and fconnection.record1objecttypecode = 2 --and fconnection.record2idobjecttypecode = 1
*This example is to explain the concept only, in order to retrieve Connection records between Account and Contact, you might try to tweak the query based on your requirement or feel free to ask me through the Comments.
Hope this helps!
Thanks.
Wednesday, 28 January 2015
CRM Advanced Find to Show Customer that I Have Tried to ‘Send Email To’, but ‘Still Pending’ or ‘Failed’.
I have a requirement and also found in the Dynamics Community Form, how to see the list of Customer + their email address that have been sent email to them, but they still have not received yet.
This can be done using SQL Query, but this time I’d show you using Advanced Find, and you cannot use Email entity + Regarding field as reference because it is possible that the Email regarding field can be other that Contact/Account, but can be related Case, but you send the Email to the Customers, not to the Case, right
And here is the Advanced Find Query to List all Customer + Email Address that in the list of attempted sending Email, but still Failed or Pending.
*Luckily, we have the relationship, so-called Activities Parties that we can use to do query.
*In the Participation Type, you can also include those who you are CC-ed or even the Sender, not only to list those you tried to Send to.
*You can change also the Status that you want to see.
And here is the result..
You can see that one of the Contact you attempted to send, even does not have email address.
Or you can see the Nancy Anderson email address is invalid, someone_c@example.com, how come you can send email to this dummy email ? , please ask your AM or Salesperson to update this
And you also can use this Advanced Find method to query about Phone Call (Call with whom), Appointment, and another Type, just change the ‘Activity (Email)’ to any Activity Type you want.
Hope this can help you!
Thank you and good night!
Sunday, 18 January 2015
Choose Your Best Rounding Policy in Microsoft Dynamics CRM
None
None – Will give the same amount without any rounding with defined decimal supported
What if I set to Up or Down?
Up
Ends in
I set the amount to $15, $10, and $99
Rounding Policy | Rounding Amount | Amount (before) | Amount (after) |
None | - | 748.75 | 748.75 |
Up | 0 | 748.75 | 749.00 |
Up | 0.1 | 748.75 | 749.10 |
Up | 0.5 | 748.75 | 749.50 |
Up | 0.75 | 748.75 | 748.75 |
Up | 0.85 | 748.75 | 748.85 |
Up | 0.99 | 748.75 | 748.99 |
Up | 1 | 748.75 | 751.00 |
Up | 2 | 748.75 | 752.00 |
Up | 3 | 748.75 | 753.00 |
Up | 4 | 748.75 | 754.00 |
Up | 5 | 748.75 | 755.00 |
Up | 10 | 748.75 | 810.00 |
Up | 15 | 748.75 | 815.00 |
Up | 25 | 748.75 | 825.00 |
Up | 50 | 748.75 | 750.00 |
Up | 89 | 748.75 | 789.00 |
Up | 99 | 748.75 | 799.00 |
Up | 100 | 748.75 | 1100.00 |
Up | 200 | 748.75 | 1200.00 |
Up | 500 | 748.75 | 1500.00 |
Up | 800 | 748.75 | 800.00 |
Up | 900 | 748.75 | 900.00 |
Up | 1000 | 748.75 | 1000.00 |
Up | 10000 | 748.75 | 10000.00 |
What pattern can you notice from this profound example?
Up – Ends in Will get the next (higher) nearest rounded up amount with the same ‘ends in’ amount set in the Rounding Amount setting defined in the Price List, for example the nearest amount from 748.75 that ends in with 99 is 799, while the next nearest amount that ends in with 0.99 is 748.99 and if set to ends in 0.75, the nearest higher point is 748.75 itself, and so on.CRM will find the next nearest amount ends in with amount defined in the Rounding Amount before..
Tips: Select this option to have the price end in the amount set in the Rounding Amount field.If you don’t want to get trouble with cent (after comma), just put 0 as the rounding amount or 99 to ‘attract’ your customer
Multiple Of
Same as the previous “Ends In”, let’s we make the same steps, now move on to the table directly.
Rounding Policy | Rounding Amount | Amount (before) | Amount (after) |
None | - | 748.75 | 748.75 |
Up | 0 | 748.75 | N/A |
Up | 0.01 | 748.75 | 748.75 |
Up | 0.1 | 748.75 | 748.80 |
Up | 0.5 | 748.75 | 749.00 |
Up | 0.75 | 748.75 | 749.25 |
Up | 0.85 | 748.75 | 748.85 |
Up | 0.99 | 748.75 | 749.43 |
Up | 1 | 748.75 | 749.00 |
Up | 2 | 748.75 | 750.00 |
Up | 3 | 748.75 | 750.00 |
Up | 4 | 748.75 | 752.00 |
Up | 5 | 748.75 | 750.00 |
Up | 10 | 748.75 | 750.00 |
Up | 15 | 748.75 | 750.00 |
Up | 25 | 748.75 | 750.00 |
Up | 50 | 748.75 | 750.00 |
Up | 89 | 748.75 | 801.00 |
Up | 99 | 748.75 | 792.00 |
Up | 100 | 748.75 | 800.00 |
Up | 200 | 748.75 | 800.00 |
Up | 500 | 748.75 | 1000.00 |
Up | 800 | 748.75 | 800.00 |
Up | 900 | 748.75 | 900.00 |
Up | 1000 | 748.75 | 1000.00 |
Up | 10000 | 748.75 | 10000.00 |
Up – Multiple of Will get the next (higher) nearest rounded up amount with the most possible ‘multiple of’ amount set in the Rounding Amount setting defined in the Price List, for example the nearest amount from 748.75 that is multiple of 1 is 749, because 748.75 cannot be evenly divided by 1 without any left number and the nearest rounded up amount that is perfectly divisible by 1 is 749.
Other example, while the next nearest amount that is multiple of with 0.99 is 748.99 and if set to ends in 0.75, the nearest higher point is 748.75 itself, and so on.CRM will find the next nearest amount that is multiple of amount defined in the Rounding Amount before..
Tips: Select this option to have the price determined as a multiple of the amount set in the Rounding Amount field.If you don’t want to get trouble with cent (after comma), and want to get the exact amount, just put 1 as the rounding amount because only exact number that is exactly and absolutely divisible by 1.If you want to sell the similar products with similar price and you want to put branding in your store, for example All is 999, then just put 999 as the Rounding Amount setting. So, whether the current cost after markup is 599, 749, 750, 899, or 999, the rounded up price will be 999.
Down
Ends In
Rounding Policy | Rounding Amount | Amount (before) | Amount (after) |
None | - | 748.75 | 748.75 |
Up | 0 | 748.75 | 748.00 |
Up | 0.1 | 748.75 | 748.10 |
Up | 0.5 | 748.75 | 748.50 |
Up | 0.75 | 748.75 | 748.75 |
Up | 0.85 | 748.75 | 747.85 |
Up | 0.99 | 748.75 | 747.99 |
Up | 1 | 748.75 | 741.00 |
Up | 2 | 748.75 | 742.00 |
Up | 3 | 748.75 | 743.00 |
Up | 4 | 748.75 | 744.00 |
Up | 5 | 748.75 | 745.00 |
Up | 10 | 748.75 | 710.00 |
Up | 15 | 748.75 | 715.00 |
Up | 25 | 748.75 | 725.00 |
Up | 50 | 748.75 | 650.00 |
Up | 89 | 748.75 | 689.00 |
Up | 99 | 748.75 | 699.00 |
Up | 100 | 748.75 | 100.00 |
Up | 200 | 748.75 | 200.00 |
Up | 500 | 748.75 | 500.00 |
Up | 800 | 748.75 | 800.00 |
Up | 900 | 748.75 | 900.00 |
Up | 1000 | 748.75 | 1000.00 |
Up | 10000 | 748.75 | 10000.00 |
As we can see, the final price will be the amount with the nearest rounded down amount defined as the Rounding Amount, same as Rounding Up method, but this will be finding the lower amount, previous amount prior to the calculated amount before any rounding.
Down – Ends in Will get the previous (lower) nearest rounded down amount with the same ‘ends in’ amount set in the Rounding Amount setting defined in the Price List, for example the nearest lower amount from 748.75 that ends in with 99 is 699, while the nearest amount that ends in with 0.99 is 747.99 and if set to ends in 0.75, the nearest higher point is 748.75 itself, and so on.
CRM will find the nearest preceding amount that ends in with amount defined in the Rounding Amount before..
Tips: Select this option to have the price end in the amount set in the Rounding Amount field.If you don’t want to get trouble with cent (after comma), just put 0 as the rounding amount or 99 to ‘attract’ your customer
Base Amount | Rounding Amount | Rounded Amount (Up) | Rounded Amount (Down) |
748.75 | 0 | 749.00 | 748.00 |
748.75 | 0.1 | 749.10 | 748.10 |
748.75 | 0.5 | 749.50 | 748.50 |
748.75 | 0.75 | 748.75 | 748.75 |
748.75 | 0.85 | 748.85 | 747.85 |
748.75 | 0.99 | 748.99 | 747.99 |
748.75 | 1 | 751.00 | 741.00 |
748.75 | 2 | 752.00 | 742.00 |
748.75 | 3 | 753.00 | 743.00 |
748.75 | 4 | 754.00 | 744.00 |
748.75 | 5 | 755.00 | 745.00 |
748.75 | 10 | 810.00 | 710.00 |
748.75 | 15 | 815.00 | 715.00 |
748.75 | 25 | 825.00 | 725.00 |
748.75 | 50 | 750.00 | 650.00 |
748.75 | 89 | 789.00 | 689.00 |
748.75 | 99 | 799.00 | 699.00 |
748.75 | 100 | 1100.00 | 100.00 |
748.75 | 200 | 1200.00 | 200.00 |
748.75 | 500 | 1500.00 | 500.00 |
748.75 | 800 | 800.00 | 800.00 |
748.75 | 900 | 900.00 | 900.00 |
748.75 | 1000 | 1000.00 | 1000.00 |
748.75 | 10000 | 10000.00 | 10000.00 |
In Common practice, rounding down method is very seldom, especially if choose from Costing, to get better result, it is highly recommended to use Round Down using the List Price or increase the markup or margin of current cost/standard cost is better. Using List Price as the base for rounding down is better because at least no penny will be lost here.
Multiple Of
Rounding Policy | Rounding Amount | Amount (before) | Amount (after) |
None | - | 748.75 | 748.75 |
Up | 0 | 748.75 | N/A |
Up | 0.01 | 748.75 | 748.75 |
Up | 0.1 | 748.75 | 748.70 |
Up | 0.5 | 748.75 | 748.50 |
Up | 0.75 | 748.75 | 748.50 |
Up | 0.85 | 748.75 | 748.00 |
Up | 0.99 | 748.75 | 748.44 |
Up | 1 | 748.75 | 748.00 |
Up | 2 | 748.75 | 748.00 |
Up | 3 | 748.75 | 747.00 |
Up | 4 | 748.75 | 748.00 |
Up | 5 | 748.75 | 745.00 |
Up | 10 | 748.75 | 740.00 |
Up | 15 | 748.75 | 735.00 |
Up | 25 | 748.75 | 725.00 |
Up | 50 | 748.75 | 700.00 |
Up | 89 | 748.75 | 712.00 |
Up | 99 | 748.75 | 693.00 |
Up | 100 | 748.75 | 700.00 |
Up | 200 | 748.75 | 600.00 |
Up | 500 | 748.75 | 500.00 |
Up | 800 | 748.75 | 800.00 |
Up | 900 | 748.75 | 900.00 |
Up | 1000 | 748.75 | 1000.00 |
Up | 10000 | 748.75 | 10000.00 |
Down – Multiple of Will get the previous (lower) nearest rounded down amount with the most possible ‘multiple of’ amount set in the Rounding Amount setting defined in the Price List, for example the nearest amount from 748.75 that is multiple of 1 is 748, because 748.75 cannot be evenly divided by 1 without any left number and the nearest rounded down amount that is perfectly divisible by 1 is 748.
Other example, while the next nearest amount that is multiple of with 0.99 is 748.99 and if set to ends in 0.75, the nearest higher point is 748.75 itself, and so on.CRM will find the nearest preceding amount that is multiple of amount defined in the Rounding Amount before..
Tips: Select this option to have the price determined as a multiple of the amount set in the Rounding Amount field.If you don’t want to get trouble with cent (after comma), and want to get the exact amount, just put 1 as the rounding amount because only exact number that is exactly and absolutely divisible by 1.If you want to sell the similar products with similar price and you want to put branding in your store, for example All is 999, then just put 999 as the Rounding Amount setting. So, whether the current cost after markup is 599, 749, 750, 899, or 999, the rounded up price will be 999.
Base Amount | Rounding Amount | Rounded Amount (Up) | Rounded Amount (Down) |
748.75 | 0 | N/A | N/A |
748.75 | 0.01 | 748.75 | 748.75 |
748.75 | 0.1 | 748.80 | 748.70 |
748.75 | 0.5 | 749.00 | 748.50 |
748.75 | 0.75 | 749.25 | 748.50 |
748.75 | 0.85 | 748.85 | 748.00 |
748.75 | 0.99 | 749.43 | 748.44 |
748.75 | 1 | 749.00 | 748.00 |
748.75 | 2 | 750.00 | 748.00 |
748.75 | 3 | 750.00 | 747.00 |
748.75 | 4 | 752.00 | 748.00 |
748.75 | 5 | 750.00 | 745.00 |
748.75 | 10 | 750.00 | 740.00 |
748.75 | 15 | 750.00 | 735.00 |
748.75 | 25 | 750.00 | 725.00 |
748.75 | 50 | 750.00 | 700.00 |
748.75 | 89 | 801.00 | 712.00 |
748.75 | 99 | 792.00 | 693.00 |
748.75 | 100 | 800.00 | 700.00 |
748.75 | 200 | 800.00 | 600.00 |
748.75 | 500 | 1000.00 | 500.00 |
748.75 | 800 | 800.00 | 800.00 |
748.75 | 900 | 900.00 | 900.00 |
748.75 | 1000 | 1000.00 | 1000.00 |
748.75 | 10000 | 10000.00 | 10000.00 |
Thank you.