Updating parent list fields based on vLookUp list activities

[Saikia Kashmiri]

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:

1. 

   vLookup-Scenario.jpg
   

[Alexander Bautz]

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 8 months, 2 weeks ago by Alexander Bautz.
• This reply was modified 8 months, 2 weeks ago by Alexander Bautz. Reason: Updated code snippet
• This reply was modified 8 months ago by Alexander Bautz. Reason: Fixed query because < and > was removed by the browser

[Saikia Kashmiri]

Thank you very much for the detailed response. I’ll try this today.

[Saikia Kashmiri]

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.

[Alexander Bautz]

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

[Saikia Kashmiri]

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!

[Alexander Bautz]

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

[Author]

Posts