vLookup – Update Child when Parent is Updated

Forums Classic DFFS vLookup – Update Child when Parent is Updated

Viewing 21 reply threads
  • 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 5 years, 9 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 4 years, 12 months ago by Therman.
      • This reply was modified 4 years, 12 months 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

    • #30749
      Paul Lynch
      Participant

      Trying to implement this fix and struggling.

      Parent List
      Parent List Name (List) – TRLRPTS
      vLookup fieldname – vLookup_Children
      Internal fieldname – Status

      Child List
      Child List Name (Document Library) – TrialReports
      Child list GUID – {ADA5EC4A-7D6D-4AFC-9AFA-21F47C2E1480}
      Internal fieldname – status

      Both above fields are single choice columns..

      Here is my code:

      
      
      function callMeFromChangeRule(){
          var arrOfIDs = [];
          jQuery.each(spjs.vLookup.dataObj.vLookup_Children[spjs.dffs.data.thisItemID].items,function(id,vLookupListItem){
          	arrOfIDs.push(id);
          });
          spjs.utility.updateItem({
              "listName":"{ADA5EC4A-7D6D-4AFC-9AFA-21F47C2E1480}",
              "id":arrOfIDs,
              "data":{
                  "status":getFieldValue("Status"),
              }
          });
      }

      I’ve attached the console error..

      • This reply was modified 3 years, 8 months ago by Paul Lynch.
      • #30757
        Alexander Bautz
        Keymaster

        Are you in NewForm or DispForm / EditForm?

        You can paste this in the console and hit Enter to look at the variable:

        spjs.vLookup.dataObj.vLookup_Children

        Keep in mind that the vLookup table must have rendered before you can access this date object.

        Alexander

    • #30767
      Paul Lynch
      Participant

      NewForm

      Second tab has the vlookup. The person clicks uploads a record to child library, fills in metadata and then the vlookup table renders. That’s all good.

      They then click third tab, which has a Js button, which should submit form and run the function but nothing happens (hence I checked console).

      Actually want to run two functions, send an email AND update the status of the child form, but left out email function until I get this to work.

      Attachments:
    • #30778
      Alexander Bautz
      Keymaster

      When using it in NewForm, the item id is not available yet, and the identifier used is not the ID, but the _vLookupID – change your code from this:

      spjs.vLookup.dataObj.vLookup_Children[spjs.dffs.data.thisItemID]

      to this:

      spjs.vLookup.dataObj.vLookup_Children[getFieldValue("_vLookupID")]

      Alexander

    • #30823
      Paul Lynch
      Participant

      Thanks this all works brilliantly.

      Just one more question!

      Is it possible to have this work in reverse?

      So updating status in a vlookup child item, will update the vlookup parent item?

      Trying to maintain an identical status same in both child/parent

    • #30841
      Paul Lynch
      Participant

      Been looking at options and wondering if “special configuration” option in the vlookup table could call the function to perform this..

      {"function":"functionName"}

      So if I take my current item (status) field in vlookup, set the special configuration to be:

      {"function":"updatestatusfunction"}

      Then the in my parent custom JS area I use

      
      
      function updatestatusfunction() {
          setFieldValue("STATUS","xxxxstatusfromvlookupxxxxxx");
      }

      How can I populate (SP.listItem will be passed as an argument to the function) xxxxstatusfromvlookupxxxxxx by passing current field value (vlookup child)?

      STATUS is the internal field name of both parent and child (with identical values in a single choice column)

      • This reply was modified 3 years, 8 months ago by Paul Lynch.
      • This reply was modified 3 years, 8 months ago by Paul Lynch.
    • #30848
      Alexander Bautz
      Keymaster

      Hi,
      You should be able to do it like this.

      function updatestatusfunction(val, item) {
          // Set field value in current item
          setFieldValue("STATUS",val);
          // Return value to the vLookup table
          return val
      }

      Alexander

    • #30852
      Paul Lynch
      Participant

      Thanks Alexander, this works.

      It only works for the first item added to vlookup, but as vlookups are a “one to many” relationship, not sure where I was going with this, unless i could filter out certain child items, with certain statuses as form loads (in vlookup query) so that only one relevant result appears.

    • #30861
      Alexander Bautz
      Keymaster

      I’m not sure exactly what functionality you are looking for, but the function runs on all items as they are drawn in the table. You can access all values that you display in the table like this:

      var x = item.get_item("FieldInternalNameOfYourField");

      Using this method you can set the status field based on your own logic – just remember to always let the function return the value to the field table – if not, the table cell will be empty.

      Alexander

Viewing 21 reply threads
  • You must be logged in to reply to this topic.