vLookup – Update Child when Parent is Updated

[Jeffery McElroy]

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?

[Keith Hudson]

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.

[Jon Mortimer]

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 🙂

[Jeffery McElroy]

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

[Jeffery McElroy]

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?

[Alexander Bautz]

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

[Jeffery McElroy]

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”.

[Alexander Bautz]

Just change it to:

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

Alexander

[Jeffery McElroy]

Works perfectly. Thanks so much.

[Jeffery McElroy]

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?

[Alexander Bautz]

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

[David Jeremias]

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 6 years, 5 months ago by David Jeremias. Reason: typo

[Dana Al Ali]

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

[Therman]

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 5 years, 8 months ago by Therman.
• This reply was modified 5 years, 8 months ago by Therman.

[Alexander Bautz]

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

[Therman]

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?

[Alexander Bautz]

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

[Paul Lynch]

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 4 years, 5 months ago by Paul Lynch.

Attachments:

1. 

   parentchildupdate.png
   

[Alexander Bautz]

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

[Paul Lynch]

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:

1. 

   error-1.png
   

[Alexander Bautz]

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

[Paul Lynch]

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

[Paul Lynch]

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 4 years, 4 months ago by Paul Lynch.
• This reply was modified 4 years, 4 months ago by Paul Lynch.

[Alexander Bautz]

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

[Paul Lynch]

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.

[Alexander Bautz]

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

[Author]

Posts