vLookup – Update Child when Parent is Updated

Forums Dynamic Forms for SharePoint vLookup – Update Child when Parent is Updated

This topic contains 16 replies, has 7 voices, and was last updated by  Alexander Bautz 2 months, 2 weeks ago.

  • Author
    Posts
  • #19680

    Jeffery McElroy
    Participant

    I have a parent list (A) with a child list (B) connected through vLookup. They both have a field named “Display ID”. Is there a way to update the “Display ID” field in all of the child items if the value of this field is changed in the parent item?

  • #19695

    Keith Hudson
    Participant

    It seems to me that a simple way to accomplish that goal would be to build a workflow on list A that checks for a change in value in the Display ID field, and if found, updates all the matching records on list B.

    Using DFFS to update the child values may fail if someone updates the DisplayID field on the parent list in a view, rather than through the edit form, since DFFS functionality operates in the edit form only.

    If I were doing it, I would add another field to list A to record the value of the DisplayID field for comparison purposes, since I would want my workflow to run every time an item is edited, and I would need a way to know if the value of DisplayID had changed.

    I hope this helps.

    • #19697

      Jon Mortimer
      Participant

      Keith — thanks for the response and sorry for the delay! Yes, I was attempting to the do the same with the ID field but as you stated it is assigned at Save so within DFFS I could not find a reliable way to do this. Only thing possible would be to save the data then to display an alternate tab that displays the Ticket # or something.

      I have not figured out a good solution to the SLA. I created a table that contains two fields, one is concatenated value (i.e. “REPORT ISSUE-MEDIUM”) which is request type and priority. The other field is the SLA in days. In the NEW FORM I added a similar field that combines request type/priority. Unfortunately when I add the cascading dropdown, this field breaks and the dropdowns are blank. Back to the drawing board 🙂

  • #19696

    Jeffery McElroy
    Participant

    I have attempted the workflow approach, but it only finds the first matching record in list B and then stops.

  • #19698

    Jeffery McElroy
    Participant

    Just to clarify, when I reference the “Display ID” field in the parent, it is not the “ID” field that SP automatically assigns, I created a single line of text field, where I enter a value. Does this change things as to whether or not I can update the child items when this field in the parent item changes?

  • #19728

    Alexander Bautz
    Keymaster

    Hi,
    You can use a custom function like the one shown here: https://spjsblog.com/forums/topic/load-list-data-into-vlookup-tab/#post-18580

    I have modified the function slightly to do one batch update on all children (limits the number of children to 100 – if you can have more, the function must be changed slightly):

    function callMeFromChangeRule(){
        var arrOfIDs = [];
        jQuery.each(spjs.vLookup.dataObj.vLookupTasks[spjs.dffs.data.thisItemID].items,function(id,vLookupListItem){
        	arrOfIDs.push(id);
        });
        spjs.utility.updateItem({
            "listName":"GUID_OF_YOUR_CHILD_LIST",
            "id":arrOfIDs,
            "data":{"FIELD_INTERNAL_NAME_IN_CHILD_LIST":"NEW_VALUE"}
        });
    }

    Changed “vLookupTasks” to match your vLookup field name, “GUID_OF_YOUR_CHILD_LIST”, “FIELD_INTERNAL_NAME_IN_CHILD_LIST” and “NEW_VALUE” to match your settings.

    You can for example set up a rule that triggers on change of the id-column, and this rule can trigger the function to update all child records.

    Let me know how this works out.

    Alexander

  • #19773

    Jeffery McElroy
    Participant

    This works partially; however, instead of bringing in the text I replace “NEW_VALUE” with, I would like to bring in the value of a field in the parent form and carry that over to the field with the same name in the child form. Here is what I have:

    function callMeFromChangeRule(){
    var arrOfIDs = [];
    jQuery.each(spjs.vLookup.dataObj.vLookupRACI[spjs.dffs.data.thisItemID].items,function(id,vLookupListItem){
    arrOfIDs.push(id);
    });
    spjs.utility.updateItem({
    “listName”:”{e9392598-2d1a-4437-9ef1-456d35d50577}”,
    “id”:arrOfIDs,
    “data”:{“Display_x0020_ID”:”Display_x0020_ID”}
    });
    }

    The result is it puts “Display_x0020_ID” in the child field named Display ID instead of the actual value of the parent field “Display_x0020_ID”.

  • #19781

    Alexander Bautz
    Keymaster

    Just change it to:

    ...
    ...
    "data":{"Display_x0020_ID":getFieldValue("Display_x0020_ID")}
    ...
    ...

    Alexander

  • #19801

    Jeffery McElroy
    Participant

    Works perfectly. Thanks so much.

  • #20110

    Jeffery McElroy
    Participant

    Now receiving the following error:
    TypeError: Unable to get property ‘3’ of undefined or null reference

    Here is my script:
    function UpdateIRWs(){
    var arrOfIDs = [];
    jQuery.each(spjs.vLookup.dataObj.vLookupIRWs[spjs.dffs.data.thisItemID].items,function(id,vLookupListItem){
    arrOfIDs.push(id);
    });
    spjs.utility.updateItem({
    “listName”:”{2a4f5f2c-990e-4743-b605-1167eb89e4c3}”,
    “id”:arrOfIDs,
    “data”:{“Display_x0020_ID”:getFieldValue(“Display_x0020_ID”)}
    });
    }

    Any ideas on what is causing this?

  • #20170

    Alexander Bautz
    Keymaster

    I suspect you are running the code without having loaded the vLookup data. The vLookup’s are “lazy-loaded” and you must have it visible in a tab before you can use the spjs.vLookup.dataObj

    Alexander

  • #21242

    David Jeremias
    Participant

    Hey All,

    I too struggled with this issue for a couple months. However, I was finally able to get a SharePoint Designer 2013 workflow to update all of the child items related to a specific parent item. Now, whenever a change is made to an item in the parent list, the SPD2013 workflow searches the child list for all the matching IDs and updates only those matching items. The workflow is surprisingly quick and when needed, I can run the workflow on 100 items at a time, with no performance issues. All 100 instances of the workflow complete before I can even get to the workflow status screen. This is a great way to keep 2 or more SharePoint lists in sync. If anyone is interested let me know and I would be happy to share the specs of my workflow.

    – David

    • This reply was modified 12 months ago by  David Jeremias. Reason: typo
  • #22181

    Dana Al Ali
    Participant

    Hi David , can you please share the WF you have created

  • #24416

    Therman
    Participant

    I had this need as well. What I did was put this code on the Edit form of my Parent list in the customJs tab. Is that correct? My parent field is titled “canceled” (Boolean) and I need the child field “EventStatus” (single line) to update when I update when “canceled” is updated.

    Here’s my code:

    
    
    function callMeFromChangeRule(){
        var arrOfIDs = [];
        jQuery.each(spjs.vLookup.dataObj.vLookupTasks[spjs.dffs.data.thisItemID].items,function(id,vLookupListItem){
        	arrOfIDs.push(id);
        });
        spjs.utility.updateItem({
            "listName":"{611AE134-3BE2-4FE2-8FED-3D9FB0905E65}",
            "id":arrOfIDs,
            "data":{"EventStatus":getFieldValue("Canceled")}
        });
    }
    • This reply was modified 2 months, 3 weeks ago by  Therman.
    • This reply was modified 2 months, 3 weeks ago by  Therman.
    • #24431

      Alexander Bautz
      Keymaster

      If you call this function from a rule triggering on the change of the “cancelled” field in your form, it should update all child items. From your code snippet i guess you must update “vLookupTasks” to match your field name – if your vLookup column isn’t actually named “vLookupTasks”.

      When running the code you should have the developer tools (hit F12 > Console) to see if there are any errors showing up.

      Alexander

  • #24569

    Therman
    Participant

    Ok, so let me see if I got this correct (I’m terrible at JavaScript. Still trying to learn).

    First, what I need to do is change this function name from “callMeFromChangeRule” to something like “updateChildrenEvents”.
    Then, I need to add it to my customJS section on the parent Edit form.
    Then, on the parent Edit form, I need to create a rule that calls “updateChildrenEvents”. If that’s correct, I actually have a few fields that match the children. The parent needs to update any of those same children fields when saved. Are there any examples of how to do this?

    • #24575

      Alexander Bautz
      Keymaster

      It sounds like you have understood it correctly. The code I originally posted in the top of this thread should do what you want, but you must change the data-object passed to the spjs.utility.updateItem function like the example below:

      function callMeFromChangeRule(){
          var arrOfIDs = [];
          jQuery.each(spjs.vLookup.dataObj.vLookupTasks[spjs.dffs.data.thisItemID].items,function(id,vLookupListItem){
          	arrOfIDs.push(id);
          });
          spjs.utility.updateItem({
              "listName":"GUID_OF_YOUR_CHILD_LIST",
              "id":arrOfIDs,
              "data":{
                  "ChildListField1":getFieldValue("ParentFormField1"),
                  "ChildListField2":getFieldValue("ParentFormField2"),
                  "ChildListField3":getFieldValue("ParentFormField3")
              }
          });
      }

      Replace ChildListFieldX and ParentFormFieldX with the field name in the child and in the parent (the current form). Please note that if the fields are anything other than text or single choice you will have to have the correct format for the value part of the key:value pairs in the data object. Let me know what field types they are and I’ll do my best to guide you.

      Alexander

You must be logged in to reply to this topic.