Friday 30 January 2015

How to See Campaign Responses from Related Contact/Account/Lead

Overview

CRM has Campaign Response with related Customer data (can be Lead as well), but once you try to see from Contact, you cannot insert Campaign Response in the related entity section or subgrid.
So what if I want to see the Campaign Responses, for example from a Contact?
This blog will explain how to see the Campaign Response from Customer
By Default Campaign Response has a lookup field to Customer that can be Contact/Account/Lead.

campaignresponse

Can be done through Subgrid?

So it will be easy if you want to have subgrid in the Contact?

The answer is ‘No’

You cannot insert subgrid Campaign Response in the Contact Form, because Campaign Response is entity that included as Activity.

campaignresponse3

And also the lookup field here is Activity Party type, you didn’t link the Campaign Response with Contact as Regarding field right? And CRM has limitation in the subgrid, you can only insert Activity Subgrid and will see the list of Activity that related to the Contact if only the Activity regarding field is the Contact itself.

In the Subgrid, we can only select Activity that linked by Regarding Field (there is no Campaign Response in the available list)

image

So how do I see the Campaign Responses from my Customer.. Is that impossible?
The answer is also ‘No’.

Associated View is my Savior here…

Basically CRM has Associated View to show all Activities, including regarding field or as activity party.

So, choose the Contact and click the Chevron and choose the Activities

campaignresponse10

And it will open Associated View, now choose the All Activities.

campaignresponse13

As you can see you will see all of the Activities and you can see also the Campaign Response (see the Activity Type = Campaign Response), you can also see that the Regarding field is filled by Parent Campaign, not the Contact, so that, again, you cannot use the OOB Subgrid here.

Now, you can either use this ‘All Activities’ view or you can create a new View from the Activity.

1. Go to Activity entity and Views –> Create New

campaignresponse9

2. Give the proper name

campaignresponse5

3. Edit the Filter Criteria

campaignresponse6

Make sure you filter only to show Activity Type = Campaign Response

And you can also Add and Edit Display Columns
.
Now see the result…

Back to the Activity Associated View and choose the view from the selector to the newly created view.

campaignresponse12

That’s all..Is that so simple to see the Campaign Response from a Contact/Account/Lead?

*This post was made in particular to help answering this community query..



Hope this helps!
Thanks.

SQL Query in CRM 2011/2013/2015 Connection Entity

Using Advanced Find, you can get the Connection records easily

image

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 Smile

adv find contact email2

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.

adv find contact email

*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 ? Smile, please ask your AM or Salesperson to update this Smile

adv find contact email3

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

In my previous post, I have explained about pricing method in Microsoft Dynamics CRM, now in this post, we will see how the Rounding Policy will affect your Pricing in Opportunity, Quote, or whatever Product Pricing needs to make.

In CRM, there are 3 types of Rounding Policy:

1. None
2. Up
3. Down

Rounding Policy option will be available if only you use the Pricing Method other than Currency Amount, because, other methods are using percentage, not an exact amount like in the Currency Amount.

Let’s get started.

Rounding Policy will not be available to choose if the Pricing Method is using Currency Amount

image

None

This is the easiest option, you will give only the price with the percentage calculation like in my previous post, then it will give the same price, no rounding at all.

Example:
Here is my pricing

image

I have set Current Cost to $599.00

I set the pricing Method to the Percent Markup – Current Cost with percentage = 25%
Rounding Policy to None so there will be no Rounding Option and Round Amount enable

So I expect I will get the 125/100 * 599 = 748.75

Then this is in the Opportunity Product Line:

image

I get the amount without any rounding and since I get good price number because I put 2 decimal supported.
None – Will give the same amount without any rounding with defined decimal supported

What if I set to Up or Down?

Once I select the Rounding Policy to Up/Down, I need to provide the value from this option list:

image

Ends in or Multiple of, what is the difference?

Up

Let’s we choose the Up as the Rounding Policy.

Ends in

image

I set:

Rounding Policy to Up
Rounding Option to Ends In,
Rounding Amount to $15

Now here is the result:
image

Why $815?

Let’s get another settings:
I set the amount to $15, $10, and $99

And see the result

$10
image

$99
image

Now, do you notice the Pattern?
Actually, if you notice, the pricing was coming from the rounding and find the nearest point.

Let’s examine the pattern together:
I make some research and put the data into this table?

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?
UpEnds 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..
See the above table and notice the bold ones to get more understanding..
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 Smile

Multiple Of


Same as the previous “Ends In”, let’s we make the same steps, now move on to the table directly.

*But, you cannot set to 0, you will receive this error message.

SNAGHTML56af2e6

So, I add the 0.01 as the lowest one.

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
UpMultiple 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..
See the above table and notice the bold ones to get more understanding..
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.
Now, let’s talking about the ‘Down’ with same concept of the Up.

Down

Let’s we decipher by each Rounding Option again.

Ends In

Same as the statements for the ‘Up’, let’s go to the table directly.

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.

This is also useful if you want to sell item to the bottom price, for example I set to $800, no matter it costs. Less than 800 also will give 800 because the most possible rounded down to get 800 if the amount is less than 800 is 800 itself.
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..
See the above table and notice the bold ones to get more understanding..
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 Smile
See this summary table of the Ends In Method.

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

Let’s 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.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
DownMultiple 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..
See the above table and notice the bold ones to get more understanding..
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.
See this summary table of the Multiple Of Method.

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

Note:

*For the common practice of using ends in as the rounding option, if you hate the non-exact amount with decimal point or odd cent, just put 0 to get the next nearest rounded amount without number after comma. Most of the seller also somehow put 99 behind to attract more sales, it is a human nature behavior, you will see 799 or 799.99 is better than 800 right albeit narrowly it was because of the first number illusion effect.

*Usually the multiple of is not too popular as the rounding option, but it can be useful for those stores or companies who want to have branding in pricing, for example all is 888, all price is in multiple of 5 dollar, can be $5, $10, $15, and so on.

So, no matter your current cost is $3, $4.5, or $4.9, all is in 5 dollar for the rounded up price.
Instead of you manipulate the pricing amount using the currency amount, I think this rounding option provided by CRM is more useful makes more efficiency in your pricing setting, either using ends in or multiple of concepts.

Hope this can help us understanding the pricing method in CRM and make us choose the best rounding policy.

Happy Monday all..
Thank you.