Home › Forums › Classic DFFS › vLookup – Update Child when Parent is Updated
- This topic has 25 replies, 8 voices, and was last updated 4 years, 5 months ago by Alexander Bautz.
-
AuthorPosts
-
-
February 8, 2018 at 21:00 #19680
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?
-
February 9, 2018 at 15:14 #19695
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.
-
February 9, 2018 at 20:25 #19697
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 🙂
-
-
February 9, 2018 at 17:49 #19696
I have attempted the workflow approach, but it only finds the first matching record in list B and then stops.
-
February 9, 2018 at 23:18 #19698
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?
-
February 10, 2018 at 12:38 #19728
Hi,
You can use a custom function like the one shown here: https://spjsblog.com/forums/topic/load-list-data-into-vlookup-tab/#post-18580I 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
-
February 12, 2018 at 22:21 #19773
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”.
-
February 12, 2018 at 22:55 #19781
Just change it to:
... ... "data":{"Display_x0020_ID":getFieldValue("Display_x0020_ID")} ... ...
Alexander
-
February 13, 2018 at 16:14 #19801
Works perfectly. Thanks so much.
-
March 9, 2018 at 17:33 #20110
Now receiving the following error:
TypeError: Unable to get property ‘3’ of undefined or null referenceHere 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?
-
March 12, 2018 at 18:59 #20170
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
-
June 20, 2018 at 19:55 #21242
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, 6 months ago by David Jeremias. Reason: typo
-
September 23, 2018 at 11:39 #22181
Hi David , can you please share the WF you have created
-
March 25, 2019 at 14:27 #24416
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")} }); }
-
March 25, 2019 at 19:47 #24431
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
-
-
April 1, 2019 at 19:33 #24569
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?-
April 2, 2019 at 15:20 #24575
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
-
-
June 24, 2020 at 13:57 #30749
Trying to implement this fix and struggling.
Parent List
Parent List Name (List) – TRLRPTS
vLookup fieldname – vLookup_Children
Internal fieldname – StatusChild List
Child List Name (Document Library) – TrialReports
Child list GUID – {ADA5EC4A-7D6D-4AFC-9AFA-21F47C2E1480}
Internal fieldname – statusBoth 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, 6 months ago by Paul Lynch.
Attachments:
-
June 24, 2020 at 17:49 #30757
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
-
June 25, 2020 at 08:01 #30767
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:
-
June 25, 2020 at 22:46 #30778
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
-
July 2, 2020 at 09:59 #30823
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
-
July 5, 2020 at 10:03 #30841
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, 5 months ago by Paul Lynch.
- This reply was modified 4 years, 5 months ago by Paul Lynch.
-
July 5, 2020 at 11:21 #30848
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
-
July 6, 2020 at 11:47 #30852
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.
-
July 7, 2020 at 15:47 #30861
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
-
-
AuthorPosts
- You must be logged in to reply to this topic.