Manipulating column items

[Saikia Kashmiri]

Hey Alex – Need help with the below please.

1. I have two vLookup tables. For each record in the parent table, there needs to be 4 records in the child table before the parent record can be marked “Completed” (say teams A, B, C and D need to come in and enter a record for their areas in child table. Until that happens, the parent record status is “In Progress”).

At any point, I’d also like to show which teams are still pending input.

Is it possible to have a column called “Pending Teams” in the parent table – with each parent record populated with A, B, C, D (like 4 choices in a choice field) at the start – and then every time a team adds a child record, remove that corresponding team name from the “Pending Teams” column for that parent record.

That way when all teams have been removed from the parent record, I can mark the parent as sizing completed. Hopefully this will also enable me to show the pending teams from that column at any point in time.

If there’s a more elegant solution, I am all ears : ).

Hope you see this soon as I am kind of “Deadlined” heh. Thank you!!

Attachments:

1. 

   DFFS-Account-For-Pending-Children.jpg
   

[Alexander Bautz]

Hi,
You have posted this in the Modern DFFS forum – are you using the Classic DFFS version or the Modern DFFS version – the code will be different

Did the code I wrote for you here work?

PS: Thank you for the donation!

Alexander

[Saikia Kashmiri]

THAT is a very important question I guess heh. I do not even know how to find that out. I just assumed I had Modern. The version I have is DFFS v4.4.5.34 – Oct 15, 2021 | 4.51 | spjs.utility version 1.354 | Loader version: v1… Does that tell anything?

How do I find out?

I can say that the query parameter you gave in your solution to my last question did not work –

I had to replace the getFieldValue below –

“query”: “” + getFieldValue(“_vLookupParentID”) + “”,

with the <Where><Eq> kind of parameters…

[Alexander Bautz]

That is the Classic DFFS version – the Modern one is found her and is completely rewritten to work with the Modern SharePoint: https://spjsworks.com

I didn’t notice that < and > was removed from my code snippet in the other post – I have fixed it now.

I’m not sure I get exactly what you are missing in the suggested solution – it will write back and update the parent item status when all child table items are completed.

If you mean to use a third list as the top level and show the BuildingRequirements table and the Supplier sizing items associated with each BuildingRequirement in the same table, this can be done using some custom js. Let me know if this is what you are looking for and I’ll give you an example.

Alexander

[Saikia Kashmiri]

Ah good to know! (about my version heh).
And right – the ask now is slightly different from earlier – as in in the parent, I now need to show who all are pending at any point in time.
So after the 3rd step in my image, the parent screen will show “Team D sizing is pending”.
I was hoping the multichoice column in the parent list would help me achieve both of these:
1. Tell at any point in time who are still pending sizing
2. Tell when everyone has provided sizes and mark sizing completed (Say it always has to be exactly 4 teams).

And thanks for fixing the other code!

• This reply was modified 8 months ago by Saikia Kashmiri.

[Alexander Bautz]

You can show the items pending sizing by adding this code to your parent form custom js:

(function() {
    let allTeams = ["Brick supplier", "Metal supplier", "Plumbing", "Electric"];
    let allSizing = spjs.utility.queryItems({
        "listName": "SupplierSizing",
        "query": "<Where><Eq><FieldRef Name='_vLookupParentID' /><Value Type='Text'>" + getFieldValue("_vLookupID") + "</Value></Eq></Where>",
        "viewFields": ["ID", "Title", "EffortSizing"]
    });
    if (allSizing.count < 0) {
        alert("ERROR\n" + allSizing.errorText);
        return;
    }
    // Loop over all items and find the ones not
    let completedTracker = {};
    allSizing.items.forEach(item => {
        completedTracker[item.Title] = item.EffortSizing !== null;
    });
    // Loop over allTeams and find the ones not completed.
    let stillPending = [];
    allTeams.forEach(team => {
        if (completedTracker[team] === undefined) {
            stillPending.push(team);
        }
    });
    // Write status to placeholder added in a HTML section in the form
    let statusText = "<div style='font-size:1.5rem;'>No pending sizing</div>";
    if (stillPending > 0) {
        statusText = "<div style='font-size:1.5rem;'>Pending sizing:</div><br>" + stillPending.join("<br>");
    }
    document.querySelector("#pending_sizing_placeholder").innerHTML = statusText;
})();

You must replace the listName to match. In this example, the team name of the child items are stored in the Title column. Change all occurrences of Title if you use another field.

Also change the allTeams array to list the team names used in the child items.

To show the status in your form you must add a HTML section (or a heading or table row) and add the placeholder div like this:

<div id="pending_sizing_placeholder"></div>

Marking the parent item status as “Completed” is done using the original “Write back to parent” code I provided here.

Alexander

[Saikia Kashmiri]

Thank You! Hopefully one last question – Would you help dissect this part for me please? I am new to JS so it’s not clear –

allTeams.forEach(team => {
if (completedTracker[team] === undefined) {
stillPending.push(team);
}
});

Is this code adding the train name whenever effortsize is present to the completedTracker array?

Also what do the curly braces here mean?
let completedTracker = {};

Attachments:

1. 

   Error-copy.jpg
   

[Alexander Bautz]

Yes, this code checks each team to see if it has any values in the “completedTracker” object. If it does not, it means the item corresponding with the team item.Digital_x0020_Train field is missing or the value in the Preliminary_x0020_Estimate_x0020 field was null (did not have any value).

If it is missing or is null, the team name is pushed into the array “stillMissing” – which is the values shown in the “pending_sizing_placeholder”.

The line let completedTracker = {}; is just creating an empty object.

Alexander

[Saikia Kashmiri]

Thank you! Appreciate the quick responses!

[Author]

Posts