Home › Forums › vLooup for SharePoint › Updating parent list fields based on vLookUp list activities
- This topic has 6 replies, 2 voices, and was last updated 1 year, 3 months ago by Alexander Bautz.
-
AuthorPosts
-
-
August 17, 2023 at 03:24 #37043
Hi Alex – I have three questions –
I am attaching a mock set up of two tables/ lists –
1. Building Requirements (the main parent list)
2. Supplier Sizing (the vlookup list)For every requirement record in “Building Requirements” list, there needs to be sizing from 4 entities in the “Supplier Sizing” list.
Status of the parent record stays “Incomplete” till all 4 effort sizes have been populated.Question 1 ~~ As the sizes come in from each supplier, I would like to update the “Total Rec Size” field in the parent record in “Building Requirements” list with the total of all the sizes received.
Question 2 ~~ Upon receipt of all 4 sizes, I’d like to mark the status of the parent record “Complete”.Is this achievable with DFFS (Version is v4.4.5.34.)?
And if yes, would you provide the rough directions to achieve this please?And final question 3 ~~ How does conflict of two users trying to update the form showing the parent list play out in this scenario?
Many Thanks!
Attachments:
-
August 17, 2023 at 18:46 #37045
Yes, you can use the vLookup plugin to create the four “Supplier sizing items” and assign these to the correct “Sizing team”. You must use a FLOW or Workflow on the “Supplier sizing” list to send the assigned person an email with a link to the list item. When they edit the item, the below code will write back to the parent list item – add it to the EditForm Custom JS in the “Supplier sizing” list.
Please note that the code assumes that you have set up the vLookup connection as described in the setup example in my website (using _vLookupID > _vLookupParentID). You must also edit the code to set the correct list names and field names (internal name of fields).
function dffs_PreSaveAction() { var parentItemRes = spjs.utility.queryItems({ "listName": "BuildingRequirements", "query": "<Where><Eq><FieldRef Name='_vLookupID' /><Value Type='Text'>" + getFieldValue("_vLookupParentID") + "</Value></Eq></Where>", "viewFields": ["ID"] }); if (parentItemRes.count > 0) { var parentItem = parentItemRes.items[0]; var allSiblings = spjs.utility.queryItems({ "listName": _spPageContextInfo.pageListId, "query": "<Where><Eq><FieldRef Name='_vLookupParentID' /><Value Type='Text'>" + getFieldValue("_vLookupParentID") + "</Value></Eq></Where>", "viewFields": ["ID", "EffortSize"] }); // The EffortSize field in this list must be set as required in EditForm var sum = Number(getFieldValue("EffortSize")); var allSizingDone = true; allSiblings.items.forEach(item => { // Only count the other child items and not the one that is being edited if (item.ID !== spjs.dffs.data.thisItemID) { if (item.EffortSize === null) { allSizingDone = false; } else { sum += Number(item.EffortSize); } } }); // Update parent var parentData = { "TotalRecSize": sum }; if (allSizingDone) { parentData.SizingStatus = "Complete"; } var update = spjs.utility.updateItem({ "listName": "BuildingRequirements", "id": parentItem.ID, "data": parentData }); if (update.success) { return true; // Save item } else { console.log(update.errorText); alert("An error occurred, try hitting save again."); } } else { alert("Parent item not found."); } }
If two users try to save at the exact same time, the users will get an alert message and must try saving again
Let me know if you have any questions.
Alexander
- This reply was modified 1 year, 3 months ago by Alexander Bautz.
- This reply was modified 1 year, 3 months ago by Alexander Bautz. Reason: Updated code snippet
- This reply was modified 1 year, 3 months ago by Alexander Bautz. Reason: Fixed query because < and > was removed by the browser
-
August 18, 2023 at 16:23 #37048
Thank you very much for the detailed response. I’ll try this today.
-
August 22, 2023 at 00:11 #37052
Hey Alex – I replaced the field names and used the vLookup set up instructions as instructed and then tried the code you provided but am having the following issues:
1: I get an alert stating:
DFFS: Configuration error in “Run these functions / evaluate these rules”. Ensure you use the correct function name. The rule “SaveRule” tried to invoke the function: “dffs_PreSaveAction();” Error: TypeError: window(f) is not a function. (I confirmed all spellings and syntax are correct)And hence the total size value for each parent record (Building Requirement) is not populated.
2.Console logged the
sum
value to debug the code and i find that it adds all the estimates in the child table as opposed to giving me a sum of just the grouped items for a given vLookUpId.How do I go about resolving this please? Thanks! Kash.
-
August 22, 2023 at 15:13 #37053
You are not supposed to call this function from a rule – just drop it in your Custom JS and DFFS will automatically run it when saving.
Alexander
-
August 23, 2023 at 16:00 #37058
Thanks!
That worked (at least in terms of getting rid of the call error :)). Trying to work out the code to do the actual job now.Btw is there a place where I can find the latest DFFS functions for CRUD (and other functions like DFFS pre save) and how they work. For instance is there a place where I would have seen that a DFFS presaveaction function needs to be used in a certain way?
Thank you!
-
August 24, 2023 at 09:17 #37065
You can find some CRUD examples here: https://spjsblog.com/forums/topic/query-update-delete-an-item-in-javascript/
dffs_PreSaveAction is not described anywhere, but it basically runs after you hit the save button and before the save is actually done. Please note that it can only use synchronous code – if you add any asynchronous code, the save will happen before the code has finished.
If you want to stop the save just return false like this:
return false;
Alexander
-
-
AuthorPosts
- You must be logged in to reply to this topic.