locked
basic JScript - call in other entity field to perform calculation RRS feed

  • Question

  • Here's the situation:

    In my Project entity, I've added a field named "Total Allocated Hrs".

    My Task entity contains a "Time Allocated" field. I must be able to add several Tasks in a Project, that's why it's a 1:N relationship.

    Now in the Details panel (on the left) of Project, I've got a Project Resource tab and I can add several Tasks.

    What I want to do is that each time I add a Task (with Time Allocated) for that Project , the "Total Allocated Hrs" field needs to get updated with the sum of all the "Time Allocated" for all Tasks in that Project.

    The problem is that I don't know how to update this "Total Allocated Hrs" with field values which are not from the same form ie. multiple Task forms.

    Is there any way to do it with a JScript or a workflow that goes through every Task and add its "Time Allocated" value to the "Total Allocated Hrs" ?

    Greatly appreciated!

    Thank you


    Sharjeel B.


    • Edited by SharjeelB Wednesday, September 25, 2013 6:06 PM
    Wednesday, September 25, 2013 5:57 PM

Answers

  • I thought I'd throw in an alternate solution to Jason's.  If you want to use javascript, it would be better to create an HTML web resource on your project form.  Then have this HTML web resource query for all tasks' with a projectID equal to the GUID for the current project.  Them add these task hours up and display them within your web resource.  It ensures that the data is always up to date because it re-queries each time that the count is displayed, and eliminates the need for a plugin (which can still have an issue with being out of sync, though less likely).

    The disadvage of my approach (And any javascript approach) is that the value will be unavailable for advanced find, views, etc...

    There are lots of good tutorials available on how to query, but here is one that I think does a good job.

    http://crmscape.blogspot.com/2011/03/crm-2011-odata-json-and-crm-forms.html


    Troy

    • Marked as answer by SharjeelB Thursday, September 26, 2013 7:00 PM
    Wednesday, September 25, 2013 8:51 PM
  • Sure, in this example I added an additional cost field for ongoing costs to my opportunityproducts. The code below calculates the "ongoing cost" total of each line item in the Opportunity Subgrid and puts that total in the "new_ongoingcost" field on my opportunity. All you should really need to change is what I have highlighted to match your fields. Let me know if you have any other questions.

    function calcCostoTotal() {
    var grid = document.getElementById('opportunityproductsGrid'); 
    var ids = grid.control.get_allRecordIds();
    var sum = 0.00;
    var cellValue;
    for(i = 0; i < ids.length; i++) {
      var cellValue = grid.control.getCellValue('new_ongoing',ids[i]);
      var number = Number(cellValue.replace(/[^0-9\.]+/g,""));
      sum = sum + number;
    }
    Xrm.Page.data.entity.attributes.get("new_ongoingcost").setValue(sum);
    Xrm.Page.data.entity.aggributes.get("new_ongoingcost").setSubmitMode("always");
    }

    • Marked as answer by SharjeelB Thursday, September 26, 2013 6:59 PM
    Thursday, September 26, 2013 1:45 PM

All replies

  • I wouldn't consider this "basic" JavaScript - really this is better served with a plugin (using .NET code).

    Here is a blog post with an example: 

    How to update the parent record when children records are created or updated

    The problem with using JavaScript is that the sum might not always be accurate because you can interact with the Task record without actually opening the form. Meaning if you delete the task by checking the box next to it in a view- the form never opens and JavaScript is not run. When you delete one of these Tasks I would think you want the sum to reflect accordingly. With a plugin you can register code on all the relevant messages in the system and code can be executed on the server to handle this.


    Jason Lattimer
    My Blog -  Follow me on Twitter -  LinkedIn

    Wednesday, September 25, 2013 6:16 PM
    Moderator
  • I thought I'd throw in an alternate solution to Jason's.  If you want to use javascript, it would be better to create an HTML web resource on your project form.  Then have this HTML web resource query for all tasks' with a projectID equal to the GUID for the current project.  Them add these task hours up and display them within your web resource.  It ensures that the data is always up to date because it re-queries each time that the count is displayed, and eliminates the need for a plugin (which can still have an issue with being out of sync, though less likely).

    The disadvage of my approach (And any javascript approach) is that the value will be unavailable for advanced find, views, etc...

    There are lots of good tutorials available on how to query, but here is one that I think does a good job.

    http://crmscape.blogspot.com/2011/03/crm-2011-odata-json-and-crm-forms.html


    Troy

    • Marked as answer by SharjeelB Thursday, September 26, 2013 7:00 PM
    Wednesday, September 25, 2013 8:51 PM
  • Another option that would be simple and possibly achieve what you are looking for is to create a subgrid of all tasks relating the Project on your project form. Then include the hours allocated field from the task record in the subgrid view. Then you can just run some pretty simple javascript to calculate all allocated hours of each task in the subgrid. I use a solution like this to accomplish something similar to what you are talking about and just run the code on save event.
    Wednesday, September 25, 2013 9:48 PM
  • @JLattimer and InterdynBMI: Thanks much for your response. Because they are interesting work arounds, I will look further into them and drill n grill the concepts as they are very thought provoking.

    @KawasakiRider03: Thanks much for the simplistic approach. I am very keen on trying it out. At the moment, I have included everything up until displaying the "Allocated Hours" in the subgrid default view. The thing is what code do I enter for the Project save button to display the sum? You stated you have accomplished something similar. Can you kindly share the code so that I can rectify my issue. Your help is greatly appreciated!


    Sharjeel B.

    Thursday, September 26, 2013 4:14 AM
  • Sure, in this example I added an additional cost field for ongoing costs to my opportunityproducts. The code below calculates the "ongoing cost" total of each line item in the Opportunity Subgrid and puts that total in the "new_ongoingcost" field on my opportunity. All you should really need to change is what I have highlighted to match your fields. Let me know if you have any other questions.

    function calcCostoTotal() {
    var grid = document.getElementById('opportunityproductsGrid'); 
    var ids = grid.control.get_allRecordIds();
    var sum = 0.00;
    var cellValue;
    for(i = 0; i < ids.length; i++) {
      var cellValue = grid.control.getCellValue('new_ongoing',ids[i]);
      var number = Number(cellValue.replace(/[^0-9\.]+/g,""));
      sum = sum + number;
    }
    Xrm.Page.data.entity.attributes.get("new_ongoingcost").setValue(sum);
    Xrm.Page.data.entity.aggributes.get("new_ongoingcost").setSubmitMode("always");
    }

    • Marked as answer by SharjeelB Thursday, September 26, 2013 6:59 PM
    Thursday, September 26, 2013 1:45 PM
  • Hey Kawasaki,

    Thank you for sharing. Upon implementing the code, my ribbon freezes. Everything on the ribbon is active but when I try to click on say Save, it doesn't get clicked.

    Here's the situation in a little more detail:

    I modified my Task entity and am using that to manage the Consultant Name and his/her hours. I have created a relationship between the Project Name field and Company field from the Task entity to the Project Name field and Company in the Project entity. I created a Task subgrid in the Project form.

    The code I replaced:

    function calcCostofTotal()
    {
    var grid = document.getElementById('ProjectWorkSubGrid');
    var ids = grid.control.get_allRecordIds();
    var sum = 0.00;
    var cellValue;
    for(i = 0; i < ids.length; i++) {
      var cellValue = grid.control.getCellValue('new_timeallocated',ids[i]);
      var number = Number(cellValue.replace(/[^0-9\.]+/g,""));
      sum = sum + number;
    }
    Xrm.Page.data.entity.attributes.get("new_totalallocatedhrs").setValue(sum);
    Xrm.Page.data.entity.aggributes.get("new_totalallocatedhrs").setSubmitMode("always");
    }

    new_timeallocated is the Time Allocated field on my Task form

    new_totalallocatedhrs is the Total Hours on my Project form.

    I'm inserting this entire code in the Project OnSave. Do I have to specify dependencies or anything else other than just inserting the code?

    Your thoughts on all this?

    Thanks much!


    Sharjeel B.

    Thursday, September 26, 2013 2:55 PM
  • ...and the subgrid is in a tab on the Project form

    Sharjeel B.

    Thursday, September 26, 2013 3:02 PM
  • Before you invest a lot of time in this approach, wanted to make sure you were aware that it only fires on the on-save of the project.  I can see several scenarios that could bypass this and cause your total to be incorrect.

    1.  Delete a task.

    2.  Change the hours on a task.

    3.  Create a task after you create the project.

    The only time this code will work is if you add all the tasks to a project, save the project, and then never add, delete, modify the tasks again (Unless you re-save the project after each of these modifications).

    This might work for you, but you really need to be aware of the limitations.

    If you want to get help on getting any code implemented you really need to debug the code and tell us which line it is throwing an exception on and what the exception is.  No one will be able to help much if all the detail we have is that it is freezing.


    Troy


    • Edited by InterdynBMI - Troy Thursday, September 26, 2013 3:36 PM Requested more detail on errors.
    Thursday, September 26, 2013 3:24 PM
  • First, Troy is correct you will always have to save your project to make sure the data is up to date. However, I use this solution for several things and have never had issues. At least for me all the fields we add from subgrids are child records and we are always working from the parent record so if a task got changed we'd have the project record open working from it and when done we save it. It is a limitation but I don't think it's that big of one especially if you aren't a hardcore coder like me it's a perfect work around.

    As far as your issues, first make sure your name of the subgrid matches I believe it is case sensitive. You mention you entered the entire code in Project Save? Did you create a web resource as a jscript enter the code in there? Then all you should have to do on the project form is add the web resource and the function name under the form properties? There aren't any dependencies you should have to  specify.

    Thursday, September 26, 2013 3:49 PM
  • Thank you everyone for pointing out the limitations. I really appreciate the help!

    Considering I'm not a hardcore coder, this workaround should do it for now. I will keep in mind about having to save the Project form every time the Task is modified. As I improve with coding and the concepts around it, I will indeed look into the other two mentioned procedures.

    Attached are the screenshots of what it currently looks like. I have to remind you that my subgrid is inside a Section which is a subset of a Tab. I'm not sure if that matters. So after excluding it from the OnSave event, everything is back to normal but the Total Allocated does not appear on the Project form after I enter in it Time Allocated. My naming convention is right.

    .


    Sharjeel B.

    Thursday, September 26, 2013 5:25 PM

  • Sharjeel B.

    Thursday, September 26, 2013 5:25 PM

  • Sharjeel B.

    Thursday, September 26, 2013 5:26 PM

  • Sharjeel B.

    Thursday, September 26, 2013 5:26 PM

  • From the screenshots you provided it looks like you didn't add the Event Handler. Under where you see Control: Form and Event: Onsave you need to add what I show you below. So click add and for you Library should be new_esi_Project_Library and Function should be calcCostofTotal. That should hopefully solve your issue.


    Thursday, September 26, 2013 5:41 PM
  • Good news or bad news?

    Good news: The numbers are appearing now.

    Bad news: It gives me the attached error every time I save the Project form.


    Sharjeel B.

    Thursday, September 26, 2013 6:22 PM
  • better news: Bug found. the last line of code: Xrm.Page.data.entity.aggributes.get("new_ongoingcost").setSubmitMode("always");
    I clearly copy/pasted it, hence AGGRIBUTES got carried over :-)

    It works now!

    Thanks a lot everyone for your input! I really appreciate it.


    Sharjeel B.

    Thursday, September 26, 2013 6:58 PM
  • Awesome! Glad I could be of help. I get tons of help on here myself so it's nice to give back.
    Thursday, September 26, 2013 7:44 PM
  • This place has been full of help and I'm waiting to give back :-)

    There's an abundance of knowledge and experience on here. I'm looking forward to utilizing this place more and more.


    Sharjeel B.

    Thursday, September 26, 2013 7:49 PM