locked
Custom Workflow to Calculate Child Records' Amount RRS feed

  • Question

  • Hi All,

    Need an advise for a custom workflow to calculate child records amount and put it on parent record's amount. This is the case.

    I have 2 custom entities, let's call it Invoice and Child Invoice . The relationship between these 2 entities is 1:N with Invoice as the parent (1) and Child Invoice as the child (N).
    Child Invoice has a field named Amount, while Invoice has a field named Total Amount.

    What I want to do is when I add a Child Invoice relating to an Invoice, the Invoice will calculate all Child Invoices' Amount, then put the value on Invoice's Total Amount.

    I tried this by using a custom workflow for Entity Child Invoice with below steps:
    - We get the id of the Child Invoice
    - Retrieve the id of the parent (the Invoice)
    - Retrieve all Child Invoices relating to the Invoice.
    - Calculate all the Amount, then update the Invoice's Total Amount.

    This works for Child Invoice creation/edit but doesn't work for Child Invoice deletion. The workflow becomes error if it run on Child Invoice deletion. Maybe it couldn't get the parent's Id.

    Do you have any suggestion for this? Or maybe a better approach that can be implemented to calculate the Invoice's Total Amount when one or more of its children deleted?

    Thank you.


    Regards, Astri Kusumawardani
    Thursday, February 25, 2010 4:03 AM

Answers

  • Hi, the reason for it not working on "Delte" event because workflows run after the job is completed and there is no pre-post images in workflows that you can work with. by then the record is already deleted and if you try to reterive the entity which doesn't exits.

    What you have to do is to write a Plugin(Synhronous) on the "Delete Event" ( i think post) and get the "ParentId". you have to enable "ParentId" in the image of the entity plugin while doing the registration.

    tempEntity = (DynamicEntity)context.PreEntityImages.Properties["ChildPreImageOnDelete"];
    Guid parentId= ((Lookup)tempEntity.Properties["parentId"]).Value;



    Muhammad Ali Khan
    My MS CRM blog
    Thursday, February 25, 2010 4:42 AM

All replies

  • Hi, the reason for it not working on "Delte" event because workflows run after the job is completed and there is no pre-post images in workflows that you can work with. by then the record is already deleted and if you try to reterive the entity which doesn't exits.

    What you have to do is to write a Plugin(Synhronous) on the "Delete Event" ( i think post) and get the "ParentId". you have to enable "ParentId" in the image of the entity plugin while doing the registration.

    tempEntity = (DynamicEntity)context.PreEntityImages.Properties["ChildPreImageOnDelete"];
    Guid parentId= ((Lookup)tempEntity.Properties["parentId"]).Value;



    Muhammad Ali Khan
    My MS CRM blog
    Thursday, February 25, 2010 4:42 AM
  • You can use a fetchxml function on the onload of your invoice and in this fetchxml you can state the query where you use a count (child invoices) statement. And this value must be put in the field on your invoice.

    Maybe this will help you to a certain direction.
    The mind i just like a parachute, it only works when open.
    • Proposed as answer by Shahid_Hameed Friday, February 26, 2010 10:42 AM
    Friday, February 26, 2010 10:36 AM
  • Astri, you can also place a button on Invoice "Recalculate Invoice" from ISV. Which will calculate invoice total from all existing child invoice using  fetchxml, Javascript or by using a custom aspx page. 
    http://www.allaboutdynamics.com
    Friday, February 26, 2010 10:46 AM
  • Hi Muhammad Ali,
    Thanks for your suggestion. I will try this and let you know the result asap.

    Hi Huub,
    thanks for your suggestion. it will work on the OnLoad event of the form, but this might not fulfill the requirement. Because even though Invoice form is not opened, the amount of the Chilc Invoice must be calculated.
    Thanks for a nice suggestion :)

    Hi Shahid,
    thanks for your alternative suggestion :) but this functionality to calculate amount must work eventhough i didn't open the Invoice form. maybe on the other case, i would implement your suggestion.

    Thanks all
    Regards, Astri Kusumawardani
    Monday, March 1, 2010 3:30 AM
  • Hi Muhammad Ali,
    Actually I'm a beginner in plugin coding. From your suggestion, there are several things I would like to ask:

    1. Will context.PreEntityImages return the entity before the operation executed? In this case, will it return a record of Child Invoice before the record is deleted?

    2. Not sure what should I fill in the "[] bracket" in context.PreEntityImages.Properties["what's here?"]. Is it the name of the entity or the id or else? I'm googling for context.PreEntityImages but cannot get a clear explanation about what is context.PreEntityImages actually.

    Thank you for your kind help.
    Regards, Astri Kusumawardani
    Monday, March 1, 2010 11:35 AM
  • Hi Astri,
    1) First you have to register the message on teh Delete Event ( I think post)
    2) Second Register the Image (Pre-Image on the same,) and select the attribute parentInvoiceId
    Give the image some name = YourImageEntityNameHere

    3) and then in your code do this.

    DynamicEntity entity = (DynamicEntity)context.PreEntityImages.Properties["YourImageEntityNameHere"]; // see from the above.

    Guid id = (Guid)entity.Properties["parentinvoiceid"]; // the same attribute you selected for the image.

    Now do  your stuff.


    Muhammad Ali Khan
    My MS CRM blog
    Monday, March 1, 2010 12:25 PM
  • Hi Ali,
    Thanks it works :) The only different thing is the step is "Pre", because the "Post" step cannot retrieve the id.
    Regards, Astri Kusumawardani
    Wednesday, March 3, 2010 1:07 AM
  • Hi Astri,

    Could you please post the code of ur Plugin as I'm new to CRM and need the same functionality for my Order & Order Product Form.

    Thanks in advance

    Wednesday, April 21, 2010 9:13 AM
  • Hi,

    This code below from Chinmay Patel is exactly what we are looking for, using FetchXML.

    However, we are getting a Login request which refuses our log in (with the correct passowrds).

    I have a few Qu's
    1. Is there an error in the code that we have adpated?
    2. Is this technique compatible with IFD (Internet Facing Deployments) - We are running on a Hosted / Online solution?
    3. Can the code be assigned to a "Recalculate" Button like on the Opportunity Menu Bar?
    4. Can you put a filter in to the code, to only total records where new_field = "xxx". If so, how and where.

    Any assitance would be appreciated.

    Thanks.

    Daniel


     

    Code Snippet:
    1:
    2:
    3:
    4:
    5:
    6:
    7:
    8:
    9:
    10:
    11:
    12:
    13:
    14:
    15:
    16:
    17:
    18:
    19:
    20:
    21:
    22:
    23:
    24:
    25:
    26:
    27:
    28:
    29:
    30:
    31:
    32:
    33:
    34:
    35:
    36:
    37:
    38:
    39:
    40:
    41:
    42:
    43:
    44:
    45:
    46:
    47:
    48:
    49:
    50:
    51:
    52:
    53:
    54:
    55:
    56:
    57:
    58:
    59:
    60:
    61:
    62:
    63:
    64:
    65:
    66:
    67:
    68:
    
    // This is code based on what was given by Chinmay Patel, a Master on the Experts Exchange Forum
    // All credits and acknowledgements go to him.
    // It is sitting in the OnLoad of the Opportunity form
    
    if(crmForm.FormType > 1)
    {
    
    var orgName = "CUSTOMER_ORG_NAME";
    var UserCallerId = "80DE9992-B4AE-DE11-B4DC-00155D1E1005"
    var xml = "" + 
    "<?xml version=\"1.0\" encoding=\"utf-8\"?>" + 
    "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" + 
    " <soap:Header>" + 
    "  <CrmAuthenticationToken xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" + 
    "   <AuthenticationType xmlns=\"http://schemas.microsoft.com/crm/2007/CoreTypes\">0</AuthenticationType>" + 
    "   <CrmTicket xmlns=\"http://schemas.microsoft.com/crm/2007/CoreTypes\"></CrmTicket>" +
    "   <OrganizationName xmlns=\"http://schemas.microsoft.com/crm/2007/CoreTypes\"> + orgName + </OrganizationName>" + 
    "   <CallerId xmlns=\"http://schemas.microsoft.com/crm/2007/CoreTypes\">+ UserCallerId + </CallerId>" + 
    "  </CrmAuthenticationToken>" + 
    " </soap:Header>" + 
    " <soap:Body>" + 
    "  <Execute xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" + 
    "   <Request xsi:type=\"RetrieveMultipleRequest\" ReturnDynamicEntities=\"true\">" + 
    "    <Query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" + 
    "     <q1:EntityName>new_costingline</q1:EntityName>" + 
    "     <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" + 
    "      <q1:Attributes>" + 
    "       <q1:Attribute>new_clamount</q1:Attribute>" + 
    "      </q1:Attributes>" + 
    "     </q1:ColumnSet>" + 
    "     <q1:Distinct>false</q1:Distinct>" + 
    "     <q1:Criteria>" + 
    "      <q1:FilterOperator>And</q1:FilterOperator>" + 
    "      <q1:Conditions>" + 
    "       <q1:Condition>" + 
    "        <q1:AttributeName>new_costinglineid</q1:AttributeName>" + 
    "        <q1:Operator>Equal</q1:Operator>" + 
    "        <q1:Values>" + 
    "         <q1:Value xsi:type=\"xsd:string\">"+ crmForm.ObjectId +"</q1:Value>" + 
    "        </q1:Values>" + 
    "       </q1:Condition>" + 
    "      </q1:Conditions>" + 
    "     </q1:Criteria>" + 
    "    </Query>" + 
    "   </Request>" + 
    "  </Execute>" + 
    " </soap:Body>" + 
    "</soap:Envelope>" + 
    "";
    
    var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    
    xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Execute");
    xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
    xmlHttpRequest.send(xml);
    
    var resultXml = xmlHttpRequest.responseXML;
    
    var amountNodes = xmlHttpRequest.responseXML.selectNodes("//Property[@Name='new_oppmanhourtotal']");
    var amount = parseFloat("0");
    for(var i = 0; i < amountNodes.length; i++)
    {
    amount += parseFloat(amountNodes[i].text);
    }
    crmForm.all.new_oppmanhourtotal.DataValue = amount;
    }
    
    Tuesday, August 24, 2010 8:25 PM
  • The sample code above is currently specific to a single user. It can be improved by removing lines 8 & 9 and replacing lines 13 to 20 with:

    GenerateAuthenticationHeader() + 
    Friday, August 27, 2010 2:54 PM