Email Template to populate dynamic values from any entity

Email Templates of Dynamics 365/ Dataverse is very useful yet its one big limitation is it only can dynamically populate values of a one Entity type. Word Document templates don't have that limitation but it is actually for generation of a document not to use in Email content. Alternatively, Word Document Templates can be used to generate a document and attach to email which is widely done.

Today we will see how to programmatically enhance the usual Email Template to have any value. Below are the steps we do.

1) When creating Email Template, introduce special tags for dynamics values we plan to populate from other entities

2) Create email with Template in Draft status.

3) Replace special tags we introduce (Template will have many other dynamic fields as usual from specific Entity type it designed to fetch data from)

4) Send the Email

Please refer Programmatically create a draft Email using Email Template (C#) for steps 1 and 3.

Lets check how code would flow with the newly introduced method. Obviously values you need to populate should be query first to replace the tags.

// Step 2: Create Draft Email
// email object is defined here

// Step 3: Replace special tags

ReplaceEmailContent(email, "{{Coordinator}}", <Value need to populate>);
ReplaceEmailContent(email, "{{ContractId}}", <Value need to populate>);

// Step 4: Send Email

// Method to call
public Entity ReplaceEmailContent(Entity email, string tagName, string tagValue)
{
    var secondValue = email.Attributes.Values.ElementAt(1);
    if (secondValue is string strValue)
    {
        var replaced = strValue.Replace(tagName, tagValue);
        var key = email.Attributes.Keys.ElementAt(1);
        email.Attributes[key] = replaced;
    }
    return email;
}

Same way, if we need to add dynamics tags to Subject of the email, that's also possible. For that, use below method.

public Entity ReplaceEmailSubject(Entity email, string tagName, string tagValue)
{
    var secondValue = email.Attributes.Values.ElementAt(0);
    if (secondValue is string strValue)
    {
        var replaced = strValue.Replace(tagName, tagValue);
        var key = email.Attributes.Keys.ElementAt(0);
        email.Attributes[key] = replaced;
    }
    return email;
}

Restore deleted Dataverse records

Microsoft has now introduced (at this writing its in Preview), a way to restore deleted record. It is mimicking  the recycle bin of windows.

Enable the Feature

First you need to enable the feature. For that got to the Settings of the environment, browse to Features and simply enabled the Recycle Bin. Here you are also allowed to enable the number of days deleted records will be kept before deleting permanently. 

In a short while feature will be ready to use.

Use Recycle Bin to restore deleted records

Now browse to Data Management and notice you got link to View Deleted Records.

Select the record(s) from resulting view and click restore to reverse the deletion.

Send Email with dynamic excel attachment

Sending an email with associated excel sheet with details of child records is a generic requirement and also great way to pass child record details to a customer. For example, you may need to send an email to a customer with quote details and associate quote products as a excel attachment. 

In my example I am sending email to a Account and Sender is a Queue (you may obviously use a System user). I am sending Opportunity details and associated excel will carry Opportunity Products. This Email will be shown in timeline of Opportunity hence regarding Object would be the Opportunity. 

I have two separate code snippets here.

1) Sending the Email with Template and Excel

What we need to understand is we should create the Email first and then send as two steps.. This way, we get a room prior to sending, to attach the excel. Note how we pass different Ids like Sender, Template, Receiver and Regarding Object here. Also notice that we pass the excel as the attachment body.

Public void SendEmailWithPaymentEvaluationDetails(Guid templateId, Guid toAccountId, Guid fromQueueId, Guid regOpportunityId)
{
    // Initiate Email
    InstantiateTemplateRequest request = new InstantiateTemplateRequest()
    {
        TemplateId = new Guid(templateId),
        ObjectId = invoiceRequest.Id,
        ObjectType = invoiceRequest.LogicalName
    };
    InstantiateTemplateResponse response = (InstantiateTemplateResponse)OrgService.Execute(request);
    Entity email = response.EntityCollection[0];
    Entity Fromparty = new Entity("activityparty");
    Entity Toparty = new Entity("activityparty");
    Toparty["partyid"] = new EntityReference("account", toAccountId);
    Fromparty["partyid"] = new EntityReference("queue", fromQueueId);
    email["from"] = new Entity[] { Fromparty };
    email["to"] = new Entity[] { Toparty };
    email["directioncode"] = true;
    email["regardingobjectid"] = new EntityReference("opportunity", regOpportunityId);
    Guid emailId = OrgService.Create(email);

    // Link the Attachment
    Entity attachment = new Entity("activitymimeattachment");
    attachment["subject"] = "OpportunityId Product List";
    attachment["filename"] = "OpportunityId Product List.xlsx";
    attachment["body"] = Convert.ToBase64String(CompileExcelFile(regOpportunityId));
    attachment["mimetype"] = "application/vnd.ms-excel";
    attachment["attachmentnumber"] = 1;
    attachment["objectid"] = new EntityReference(email.LogicalName, emailId);
    attachment["objecttypecode"] = email.LogicalName;
    OrgService.Create(attachment);

    // Send Email
    SendEmailRequest sendEmailRequest = new SendEmailRequest
    {
        EmailId = emailId,
        TrackingToken = string.Empty,
        IssueSend = true
    };
    SendEmailResponse sendEmailResponse = (SendEmailResponse)OrgService.Execute(sendEmailRequest);
}

2) Compilation of Excel

Here we compile the excel which is called in above method when preparing the attachment. 

In this technique, we need to have a view created in Opportunity Product and retrieve its Id to be used here. That's the Id you see in SavedQuery. Interestingly, though we assign an id of a saved view, we are defining dynamically what we need in the excel via our own Fetch query. Under grid section, we adjust the column widths etc.

 

public byte[] CompileExcelFile(Guid regOpportunityId)
{
    var exportToExcelRequest = new OrganizationRequest("ExportToExcel");
    exportToExcelRequest.Parameters = new ParameterCollection();
    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("View", new EntityReference("savedquery", new Guid("{4c523f5b-e8c5-4cb5-bc83-bf4ef934342d}"))));
    string stringFetchXml = @"<fetch distinct='false' no-lock='false' mapping='logical' returntotalrecordcount='true'>
                                <entity name='opportunityproduct'>
                                    <attribute name='lineitemnumber' />
                                    <attribute name='productname' />
                                    <attribute name='description' />
                                    <attribute name='baseamount' />
                                    <filter>
                                           <condition attribute='opportunityid' operator='eq' value='{0}' />
                                    </filter>
                                   </entity>
                            </fetch>";
    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("FetchXml", String.Format(stringFetchXml, regOpportunityId.ToString())));
    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("LayoutXml", @"
            <grid name='resultset' object='2' jump='lineitemnumber' select='1' icon='1' preview='1'>
                <row name='result' id='opportunityproductid'>
                    <cell name='lineitemnumber' width='100' />
                    <cell name='productname' width='200' />
                    <cell name='description' width='300' />
                    <cell name='baseamount' width='125' />
                </row>
            </grid>"));
    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("QueryApi", ""));
    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("QueryParameters", new InputArgumentCollection()));
    var exportToExcelResponse = OrgService.Execute(exportToExcelRequest);
    if (exportToExcelResponse.Results.Any())
        return exportToExcelResponse.Results["ExcelFile"] as byte[];
    else
        return null;
}

Hope this helps!

Filtered Lookup for Customer type fields

I shared a code snipped for a filtered lookup here. When it comes to Customer Type lookup, it is bit more complex than a usual Lookup. Let's have a look;

Consider parentcustomerid field of Contact. Though it is a lookup, it is a Customer type lookup. Usually a lookup refers another Entity Type; Customer Type refers two types, namely Account and Contact Entities. If you Open this field’s customisation, you will see below details on two different relationships.

Now suppose I need to filter this loop to show only Accounts of only one Business Types (say BusinessTypecode = 1 for example). We may think of implementing below code.

AccountFilter: function () {
var accFilter = 
"<filter type='and'>
   <condition attribute='businesstypecode' operator='eq' value='1' />
</filter>";
Xrm.Page.getControl("parentcustomerid").addCustomFilter(accFilter,"accdount");
}

//Calling
Xrm.Page.getControl("parentcustomerid").addPreSearch(AccountFilter);

Then you will realise, this is half the solution. Though this filters Accounts correctly, Contacts are Not filtered at all. In fact, we need to filter-out all the contacts as below.

ContactFilter: function () {
var conFilter = 
"<filter type='and'>
   <condition attribute='lastname' operator='eq' value='' />
</filter>";
Xrm.Page.getControl("parentcustomerid").addCustomFilter(conFilter, "contact");
}

//Calling
Xrm.Page.getControl("parentcustomerid").addPreSearch(ContactFilter);

Now we have tried to filter Contacts with null last name. Since Last name is mandatory there are no contacts with null Last name, resulting None of the Contacts are shown for Lookup.

This solves the problem. When you need to filter RegardingObject of Activities, you may need to implement many filtering criteria just like this.

One other thing to keep in mind is, same way we use addPreSearch(), we also can removePreSearch() extension as necessary.

Configure Generic Email (i.e. Info@ABC.com) for Dynamics 365

In most cases, we need to have an Email address for general inquiries apart from user mailboxes. Let us see how we can do this with Dynamics 365 using Exchange Online.

1) Connecting to Exchange Online

To know how Exchange Online connects to Dynamics 365, please refer this post first.

2) Create Shared Mailbox

Now go to Office 365 Admin center and create a Shared Mailbox.

Actually shared Mailbox can be shared among users. When someone sends mail to this address, all the members would get the mail to a separate Mailbox. Any member can obviously reply from that. Interestingly, received mail will see it as it has come from Info@ address.

Though this is pretty cool, our intention here is not to use this sharing scenario, but to receive all the mails of Info@ address to a separate queue, so that any queue member can take action. In fact, we don’t add any member to this shared mailbox.

Anyway, in a while after we configure the shared mailbox, we should see the relevant entry in Exchange Admin Center as below.

3) Configure Queue in Dynamics 365

Now browse D365 Settings > Business Administration > Queue and open the Queue we are interested in and set Incoming Email with our shared Email. Also note that Convert Incoming Emails to Activities field is also important.

4) Configure Queue Mailbox in Dynamics 365

Now browse to D365 Settings > Email Configuration > Mailboxes and Open the Shared Mailbox and configure the Incoming and Outgoing emails for Server side sync as below. (Alternatively click Apply Default Email Settings if you have set Default by now. Click this for more information)
Now click Approve Email, Test and Enable Mailbox.

5) Check behaviour

a) Send an Email to Info@ address from outside. Then you will see this email in the Queue as below;

b) Now any member of the Queue can open the mail for replying.

Note;
Anyway when open the reply mail, by default, From field is getting populated with login user, but you need to select the Info@ account if the reply too should go with generic email address.

c) Here, we get the Reply mail too from Generic Info@ email

Plug-in concerns: synchronous or asynchronous, transactional or not …

When developing plug-in we have to make few main decisions based on the requirement. Most fundamental thought comes to our mind is whether we need to do in Pre stage or Post stage, which is usually not hard.

Synchronous or Asynchronous

Then we need to decide, whether it is a synchronous or asynchronous. In my opinion, unless there is very specific reason, it’s good to work synchronous, which is real time. Meaning is once synchronous process is started, user can’t proceed to next step till it finishes. Best fruit is you know if there is any error sooner than later. You will simply see an exception thrown if something is wrong.

Yet, there are specific scenarios you will be forced to go for an asynchronous ones. Best example I encountered is some batch processes that needs a long time to complete. Asynchronous process works in the background, allowing user to proceed to next step. I have done very successful batch processes in asynchronous mode that took 5 to 10 hours to complete.

Problem with asynchronous execution is, you don’t know if an error occur because it’s happening in the background. You may need to check system jobs to see if there are errors.

Transactional or Not

When we discuss about the synchronous and asynchronous modes, we are also forced to discuss about transactions. All asynchronous plug-ins are none-transactional, which means.. if plug-in fails in some point, all the operations happened till that failure are valid. Suppose my batch process needs to process 100 records in one go. If it fails in 61st record due to an data issue, first 60 records have already processed correctly.. Only remaining 40 are un-processed. That’s because asynchronous plug-ins are none-transactional.

In fact, my golden rule for asynchronous ones is use a proper tracing mechanism inside the plugin code. So you are able to see what went wrong after the execution, in case of a failure. Otherwise, how do you know what processed and what not? Knowing Point of failure is important here.

It is best to work synchronous plug-ins for important calculations of payment & etc. for example. Since its best to have nothing as the outcome than having half of the steps… (Ex: Paying the commission for a sale and not doing the sale actually could be horrible than happening nothing.) Fruit of synchronous plug-ins is (with one exception…a special case) they are transactional. That means, if it fails in some point all the previous operations would be reversed. Outcome is 100% or nothing. No need to worry about the point of failure.

Importance of Pre-validation

Let’s talk about the “special case”.. if you register the synchronous plug-in in the pre-validation, it is none transactional. Please refer this article, which explains it really well. It sounds like, the transaction starts in some point and Pre-validation stage occurs before that.

Please note if you write a Pre-Validation Plugin for Create message you will not have the Id since record is not yet created, you need to deal with other fields those available in create.

http://mscrmtools.blogspot.com.au/2011/01/crm-2011-plugins-welcome-to.html

How to throw exception while keeping the changes

Depending on the situation, transactional operations could be annoying too. Recently I wanted to write a plug-in that needs to throw an error to show a message to user, but I didn’t want to roll back what I did so far. Since I need to throw the exception, I have to go for a synchronous plug-in definitely, but it rollbacks everything as soon as they through the exception. Only solution was to register it in pre-validation. (Synchronous in pre-validation is not transactional.. this is the special case we discussed)

So these are some concerns about the plug-in designing.

Resolution for issue of wrong totals in tables grouped with filters in SSRS

Previous post I explained the fruit you can have by using filters when grouping table columns. Still, it can be frustrating if you need subtotals for any numeric values in table. What really happens is totals ignore the filters. (Just behave like there is no filter!)

Suppose we add group by some field, but only needs values for given two countries. (i.e. France and Belgium). Filter will be seen as below;

So we also need to get sub totals for groups (suppose, field name as revenueVal). So what we usually do is put below method in the total row;

SUM(revenueVal)

As I explained, issue is this will sum up all the revenues of all the countries not just for filtered ones.

This is the resolution.
Define another calculated field in the DataSet.

And add the same filter criteria as an expression;

Expression will be looked like this… hope you understand the simple logic. We are replacing zero for all the rows which do not meet the criteria.

* Note: If you are going to use the totals in Matrix (instead of a table) please use “nothing” instead of “0” in above expression.

Now we are using our own calculated field for calculation; 

SUM(revenueVal_Filtered)

Now this subtotal adheres our criteria.

Dynamics CRM toolkit for CRM online

This is an amazing step by Microsoft to leverage the development effort especially in online department. From here you can download the needful stuff.

Also check this article for one of the clear explanations on how to get started;

http://mscrmshop.blogspot.com.au/2012/01/step-by-step-plugin-tutorial-using.html

Plugin for opportunity Win / Lose

It can be a common requirement to execute plug-ins when opportunities are closed as Win or Lose. In most cases you need to identify two cases separately. Ironically, I didn’t find many resourceful articles about it. In fact, I thought of sharing my experience.

These are the relevant statecode and statuscode combination for opportunity.

First one could think of having a State Change plug-in for this. But I learned it’s not successful.

Whether it is Win or Lose, opportunity will be closed. So it creates a record in OpportunityClose entity. Then I though, we could do a create plug-in for OpportunityClose entity. Now the problem is you are not able to catch whether it’s a Win or Lose. If your requirement is just to do something when opportunity is closed, this works.

Then only I decided to do two different plugin for Win message and Lose message which triggers the plugin in the correct action.

So correct plugin registration steps would be seen as below;

Then I checked the plug-in context which made me shocked again. It doesn’t have opportunity record but it does have an OpportunityClose.

OpportunityClose is of course an unfamiliar entity for me. For you too obviously! Then only I realised it should be called a “Black Sheep”. You know why? Could you guess the primary Key of this entity? Your obvious answer should be opportunitycloseid which is completely wrong! Check below picture. It is Activityid!

Anyway, strangeness of primary key was explained only for your knowledge. The good side is this entity contains opportunityid which is the gateway for all the attributes of our current record. So for both Win and Lose plugins I started coding as below, by passing the OpportunityClose instead of Opportunity, knowing that it contains opportunityid.

context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
service = factory.CreateOrganizationService(context.UserId);

if (context.InputParameters.Contains("OpportunityClose"))
{
   oppCloseEnt = (Entity)context.InputParameters["OpportunityClose"];
   .........
   .........
}

In summery I am passing opportunityclose, read opportunityid in it, retrieve opportunity fields I need using the service. In a way, it’s like asking something about your home from your neighbour! Anyway, it worked for me!

Calculate total from related entity values

This is a very common scenario. For example Quote entity contains fields to have total of all the relevant quote product values. Yet, we don’t need to bother about it since CRM is taking care of those totals. Suppose we need to do such calculation in our custom scenario it will not be handled by CRM.

Just like Quote and Quote product (or Opportunity and Opportunity product), suppose we got entity that has related records as child records or line items. Each child record could have a value for surcharge and primary entity should have the total surcharge. How you accomplish this? In other terms, when child values get changed total should get changed.

Now we will consider the events that child values could change. They are Create, Update and Delete. Obviously we need to write plug-ins for those events to adjust the total. Below would be our algorithm;

Total (Surcharge) = Sum of All Line Item (Surcharge)

Now consider different events;

Post Create – Execute above algorithm

Post Update – Execute above algorithm

What about the delete?

This is the tricky part. Since there is nothing called post-delete, obviously we need to run this in pre-delete. Still we don’t get expected value by executing the same algorithm in pre delete. Why? This uses pre values. In this case total will not be affected by the calculation. Total will not get deducted the value of deleted child record. How we solve this?

You will only have to update the surcharge value of the deleting record to zero in pre delete plug-in. When you do this, post-update plug-in will execute and total will be set except for the record which will be deleted soon. So this is the correct out come!

Sales process – Part 4 – Modifying calculation of quote

In previous posts we discussed how quote pricing is done in the native way and modifying them in item level (i.e. Quote Product) in Microsoft Dynamics CRM 2011. As the final stage, we will look in to the ways of modifying the grand total (i.e. Quote) if we happen to do so.

This requirement could occur mainly as below;

a) add a surcharge, administration cost or insurance
b) give discount comes within a loyalty program or discount determined by total
c) calculation of commissions/ loyalty points according to total

Consider the pricing pane of the quote form.

Three fields I put within the green squares are editable fields. Check them with their schema names and types.

Name	Schema Name	Type
Quote Discount %	discountpercentage	Decimal
Quote Discount	discountamount	Currency
Freight Amount	freightamount	Currency

These are the field that can’t be edited. For ease of explaining, I am dividing them to two different categories.

None editable – Category A -Fields derived from the quote products

Name	Schema Name	Type
Detail amount	totallineitemamount	Currency
Total Tax	totaltax	Currency

None editable – Category B – Calculated fields

Name	Schema Name	Type
Pre-Freight Amount	totalamountlessfreight	Currency
Total Amount	totalamount	Currency

Considerations for plug-in development

Obviously we need to develop our own plug-ins to enhance/modify the current calculations. Now consider my research result on plugin behaviour in this regard. When “creating” the quote, calculations are not applicable since quote product addition comes in a later stage. In fact, we are talking about update plug-in.

Also it is important to understand user actions that update plug-in would fire.  (Will call this action type for ease of referencing)

i) Opening the Quote
ii) Saving the Quote
iii) Pressing Recalculate button
iv) Adding/ Modifying/ Deleting  a quote product

Please check below table. I use word “available” to say it is available in plug-in context. When I say “retrieve” it means retrieve the current value from database using the web service.

	Update
	Pre stage	Post stage
Editable fields (of first table)	Available if changed, otherwise retrievable. Can be modified. *	Available if changed, otherwise retrievable. Can be modified.*
None editable (Both Category A and B)	Available only for Action type (iv).** Can be retrieved. Cannot modify.	Available only for Action type (iv). ** Can be retrieved. Cannot modify.

* If you modify the same entity in Update event, make sure you avoid infinite loops.
** Plug-in run by action of addition/modification/deletion of a quote product.

Revealed plug-in behaviour seems bit complex. None editable fields become available in plug-in context when update is executed by a change of a quote product.

Other exciting observation is, when I register the plug-in for pre-stage it get executed more than once depending on users actions (refer i, ii, iii, iv) . This is bit confusing and unexplained. However, this complexity keeps us away from writing codes for pre update stage.  I am happy if someone has figured out this and can share.

Good news is we don’t need to understand all these to do our modifications.  Most important behaviour is whenever we change the editable fields, calculations get updated accordingly, regardless of when you change.

As a summery we will stick in to some rules which will give us enough space for our work. Consider below facts;

Some points to think

1) Register your plug-in in post Update.

2) You can retrieve none editable category (A) fields and use for your logic. If you see carefully, you will realise these (sum of line item values and sum of taxes) are the important reference fields to implement any logic together with editable fields.  Anyway, never try to modify category (A) fields.

3) Never try to reference or modify category (B) fields. If you want to reference them determine the values by calculating. (Reference 2nd part of this article)

4) Out of three editable fields, quote discount and freight amount gives you the most needed space to enhance the functionality as you wish. One field is being added to the total while one is being subtracted. So whatever the adjustment you need to do can be done through these two fields. Think a bit.

5) Whatever the modifications you do, you have to be creative enough to use the native total fields and native functionalities. Our changes should be carefully pushed into the bult-in mechanism through given spaces.

1st part of this article: Sales process – Part 1 – Product Catalog
2nd part of this article: Sales process – Part 2 – Quoting
3rd part of this article: Sales process – Part 3 – Modifying calculation of quote product

Sales process – Part 2 – Quoting

(It is highly recommended to read the first part of this article before proceed.)

In Microsoft Dyanmics CRM, Quote products are the line items under the Quote. Each Quote Product represents quantity, price and etc. of a product which is of interest, in given quotation.

When creating a Quote there are few required fields such as Name, Potential Customer, Price List and Currency. Since I am going to explain the pricing of Quoting, I would urge the importance of assigning the Price List for a Quoting while all other fields are quite self-explainable.

Quote Product Units

Now we will pay attention to Quote Product and see how it works together with other components we learned in first Part of this article.

When creating Quote Products, it is required to populate Product, Unit and Quantity.

Now I am adding the Product Called “My Product 001” and Unit lookup pop-up shows all the Units under Unit Group of Primary Unit (Each). This is because, when we create this product in product entity we have specified that Unit Group. If we use a product which got Weight Unit Group, we might see all the Units associated to Weight such as 5kg Pack, 3Kg Pack and so on.

Quote Product Price Calculation

Now I save the Quote product with my values. Quantity is 7 and Unit is Each. Now we get auto calculated total for the Quote Product as below.

Price per Unit is the amount we mentioned in the Price List for “My Product 001” for “Each” unit. below is the calculation for Amount.

Amount = (Price per Unit – Volume Discount) x Quantity

In this case, Amount is also equal to Extended Amount too.

Now what is Volume Discount? If you read the first part of this article, it was explained about a lookup to set Discount List when creating Price List items. In this case, I have assigned a Discount List for the Pricelist Item of this product for Each Unit. Particular Discount List contains a Discount as below;

This simply tells to give $ 3 discount if quantity >= 6 and quantity <= 10.

Note: Since Discount List has to be assigned to Pricelist Items, it has to be assigned to all the Unit records for same product. In other terms, this same discount will not be applicable for the same product comes as Dozen or Cartoon unless it’s assigned for relevant PriceList Item records.

Apart from them, there are two more editable fields which I filling now. I put Manual Discount 50 and Tax 100 then save the record again. Below is the result.

In fact below is the equation of Extended Amount.

Extended Amount = Amount – Manual Discount + Tax

Dependencies you may note in Quote products

1) There are more products in the product entity, but I see fewer products in product lookup from the Quote Product form.

Explanation: You see only the products associated with the relevant Price List. In other terms, there should be at least one PriceList Item with the product to be seen here.

2) After populating a product to Quote Product, when try to set the Unit, I don’t see all the Units come under relevant Unit Group.

Explanation: Though this product is associated with relevant PriceList, it hasn’t got PriceList Item for all three Units, but only for the shown Units. In this example, PriceList got only one PriceList Item for this product and it is for Dozen Unit. (Need records for Each and Cartoon)

Quote Price Calculation

Once you complete play around with Quote Product, you are required to click the Recalculate button to get correct Quote totals. I got below sample totals for a Quote with a couple of Quote Products.

In fact, below are a couple of important formulas;

Detail Amount = Sum of Quote Products (Extended Amount – Tax)

Total Tax = Sum of Quote Products (Tax)

Quote total pane also got a couple of editable fields. I put my own values and recalculated to get new totals.

I have below formulas now;

Pre-Freight Amount = ( Detail Amount  x (100 - Quote Discount % ) / 100 ) – Quote Discount $

Total Amount = Pre-Freight Amount + Freight Amount + Total Tax

You will notice there are two kinds of Discount fields in the pane. One is percentage and other one is dollar amount. I have filled both fields to present a universal formula, but in practical world it’s advised to use one of those to keep it simple. These values are one time discounts that applied to the Quote total.

1st part of this article: Sales process – Part 1 – Product Catalog
3rd part of this article: Sales process – Part 3 – Modifying calculation of quote product
4th part of this article: Sales process – Part 4 – Modifying calculation of quote

Sales process – Part 1 – Product Catalog

Microsoft Dynamics CRM got flexible features to cater most of the pricing needs for products and services. Here I am explaining how they will fit in to your scenario. Here my starting point is Product Catalogue. Product Catalog is nothing more than few purposely designed entities. If you click settings > Product Catalog, you will see below screen with the entry points to four sections.

 



Unit groups and Units

First thing first! It is important to understand the relationship of Unit Groups and Units within the context of usability. This is fundamental physics! Any product should have a method of measuring.

Primarily most of the products can be count as integers (i.e. each). For example we can count Cars, Generators, computers, tables or etc. In day to day life, we have defined different packages of items for ease of usage such as cartoons, dozens and etc. Best example comes to my mind is beer. When we got o bar we can ask for one or two... but when we order them for a party we might order beer packs of 24 or 26 or etc. In CRM, these countable items can be defined as one Unit Group while, as same as the example mentioned, other “item packs” can be different Units under same Unit Group.

Now I am considering this Unit group as my Primary Unit group. CRM allows me to define many Units under this. Once you created one Unit Group, you can click the Unit link shown as an associate view, where you can add you’re Units. Suppose I define a dozen. Below is the simple form I need to fill. This explains dozen is created by multiplying Each by 12.



Also I can define a Cartoon which is 5 dozens.  This is the view I see all the relationships of Units under my Unit Group called “Default Unit (Each)”

Now I realize there can be different other products which can’t be measured with my primary unit group. Ex: Metal, Wheat, Cement and etc. Now I know they are measured by weight not the number of items. So I need another Unit Group for weight. Obviously, I can again define my Units under this Unit Group. Here I have added my second Unit Group;

 

Within Weight, I have defined different weights as Units. From this illustration you should realise that my primary unit for Weight Unit Group is 1kg and I have defined different packs of different weights based of 1kg.

Hope this explains that you can define whatever the measurement and units goes with your product or service. It can be length or time or etc.

Product and pricelist

Now only we can add records to products and pricelist.

When creating a new product you are required to populate both Unit Group and Default Unit. Always Default Unit look up shows you the values according to the Unit Group you selected.

Pricelist is the next important entity that keeps prices of products for their different units. Pricelist keeps all those information as PriceList Items. Please check a sample pricelist item as bellows;

This pricelist item says the price of the product for a Dozen. This implies, typically we need two more records for the same product for each and Cartoon.

Now we will consider this case. When I open my second product I see below error message in the beginning of the form. It says I haven’t set the default price list for the product.

In fact, now I am trying to open the lookup and set it, but I can’t see any pricelist in the pop-up lookup window!  This is because; price list doesn’t contain a pricelist item for this given product.
Now you are required to add a pricelist litem for this product before making any use of this product.

Discount Lists

In price list item, there is another special lookup called Discount List which obviously is to select a relevant Discount list for the record.

Discount List contains different Discounts under it. Discounts are simply discount amount /Percentage you get for buy different numbers. For example this Discount says that buyer gets $3 of discount if buy 4-10 of this. So user is free to define many Discounts under Discount List. Obviously user can have many Discount Lists to be attached to different pricelists.

When quoting, these discounts are being applied automatically.

2nd part of this article: Sales process – Part 2 – Quoting
3rd part of this article: Sales process – Part 3 – Modifying calculation of quote product
4th part of this article: Sales process – Part 4 – Modifying calculation of quote