New Data based on 2 sharepoint lists

[Iain Munro]

Afternoon and Merry Xmas.

I have 2 SharePoint lists with data – all on the same site.

In a 3rd list, I want to show a value of the 1st list value divided by the 2nd list value

What options do I have to do this – I have DFFS installed of if that is a possibility.

Iain

• This topic was modified 6 years, 3 months ago by Iain Munro.

[Alexander Bautz]

Yes, this is possible using some custom code, but what’s the primary key that links the list items together?

Alexander

[Iain Munro]

Hi Alexander

The key that links them together is the Welder’s name. Would that work or should I specifically add something in ?

Iain

[Iain Munro]

That would be great if that can be done as I tried using a workflow – while I managed it get it to work for one item, it would mean creating a lot – so not really feasible approach.

Here is a screenshot

What I need is another View showing the Performance divided by the hours.

Iain

Attachments:

1. 

   Capture.png
   

[Iain Munro]

Hi Alexander

Did you think you would be able to help me with this ?

Iain

[Alexander Bautz]

Hi,
I looked at your screenshot, but need some more information before I can write you a code example.

If this is a normal list view, do you actually have one input field for each day of the year? If so, do you want to store the new number in a third list of the same type as the two in the screenshot? – like 250 / 8 = 31.25 > add 31.25 to the list item with “Mark” in “Jan 1”?

Alexander

[Iain Munro]

Hi Alexander

Appreciate your help.

These are just custom lists created with regular columns. I will break it out by months, so yes, there is an entry for each day of the month through the year – perhaps there is a better way to do it, but for me this works. I am open to changes if it suits the code better.

Basically, there 2 main fields, the welder’s name and the day of the month (each has its own field).

Each day, we would record the number of inches welded that day. On the day in the other list, I can capture the welder’s name plus how many hours he / she worked.

The 3rd list is just a combination of the two – the name and the calculation for the particular day

I do have a weekly total which is a calculated field

You have understood – yes, 250 / 8 = 31.25, that would go in Mark in Jan1 or the field that it came from.

If there is anything you need, please let me know.

Iain

[Alexander Bautz]

One more question: How do you want this copy to be preformed?
Each time you edit one of the items in list 1 or 2 the calculated values are updated in list 3?

Alexander

[Iain Munro]

Hi Alexander

The calculation should be performed when either the value in list 1 or list 2 is changed.

Typically, the values are entered once, so not much editing after the fact.

Iain

[Alexander Bautz]

Here is two code snippets. Use the first one in the Custom JS in your “Performance” list and the second one in your “Hours” list. The code must be added both in NewForm and in EditForm

You must change “PERFORMANCE_LIST_DISPLAY_NAME”, “HOURS_LIST_DISPLAY_NAME” and “THIRD_LIST_DISPLAY_NAME” with the display name (or the GUID) of your lists.

You must also provide the full list of fields to update (internal name – see Fields tab in DFFS backend) in the “fields” array in both functions.

Let me know how this works out.

Performance list

// Code for performance list
function dffs_PreSaveAction(){
    var welderName, welderHours, welderHoursCalc, calcItemID = null, item, fields = ["January_1"], fVal, data = {}, updRes;
    welderName = getFieldValue("Title");
    data.Title = welderName;
    welderHours = spjs.utility.queryItems({
        "listName":"HOURS_LIST_DISPLAY_NAME",
        "query":"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>"+welderName+"</Value></Eq></Where>",
        "viewFields":["ID","Title","January_1"]
    });
    welderHoursCalc = spjs.utility.queryItems({
        "listName":"THIRD_LIST_DISPLAY_NAME",
        "query":"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>"+welderName+"</Value></Eq></Where>",
        "viewFields":["ID"]
    });
    if(welderHoursCalc.count > 0){
        calcItemID = welderHoursCalc.items[0].ID;
    }
    if(welderHours.count > 0){
        item = welderHours.items[0];
        // Loop over all fields
        jQuery.each(fields,function(i,fin){
            if(item[fin] !== null){
                fVal = getFieldValue(fin);
                data[fin] = fVal / item[fin];
            }
        });
    }
    // Update item in third list
    if(calcItemID === null){
        updRes = spjs.utility.addItem({"listName":"THIRD_LIST_DISPLAY_NAME", "data":data});
    }else{
        updRes = spjs.utility.updateItem({"listName":"THIRD_LIST_DISPLAY_NAME","id":calcItemID, "data":data});
    }
    if(updRes.success){
        // successfully updated in third list
    }else{
        alert(updRes.errorText);
    }
}

Hours list

// Code for Hours list
function dffs_PreSaveAction(){
    var welderName, welderPerformance, welderHoursCalc, calcItemID = null, item, fields = ["January_1"], fVal, data = {}, updRes;
    welderName = getFieldValue("Title");
    data.Title = welderName;
    welderPerformance = spjs.utility.queryItems({
        "listName":"PERFORMANCE_LIST_DISPLAY_NAME",
        "query":"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>"+welderName+"</Value></Eq></Where>",
        "viewFields":["ID","Title","January_1"]
    });
    welderHoursCalc = spjs.utility.queryItems({
        "listName":"THIRD_LIST_DISPLAY_NAME",
        "query":"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>"+welderName+"</Value></Eq></Where>",
        "viewFields":["ID"]
    });
    if(welderHoursCalc.count > 0){
        calcItemID = welderHoursCalc.items[0].ID;
    }
    if(welderPerformance.count > 0){
        item = welderPerformance.items[0];
        // Loop over all fields
        jQuery.each(fields,function(i,fin){
            if(item[fin] !== null){
                fVal = getFieldValue(fin);
                data[fin] = item[fin] / fVal;
            }
        });
    }
    // Update item in third list
    if(calcItemID === null){
        updRes = spjs.utility.addItem({"listName":"THIRD_LIST_DISPLAY_NAME", "data":data});
    }else{
        updRes = spjs.utility.updateItem({"listName":"THIRD_LIST_DISPLAY_NAME","id":calcItemID, "data":data});
    }
    if(updRes.success){
        // successfully updated in third list
    }else{
        alert(updRes.errorText);
    }
}

[Iain Munro]

Many Thanks Alaexander

I will give it a whirl over the weekend.

Just a question

On the lines “viewFields”:[“ID”,”Title”,”January_1″]

Do I just add additional items like

“viewFields”:[“ID”,”Title”,”January_2″]

for each of the additional days of the month ?

Iain

[Alexander Bautz]

You must add all fields like this:

"viewFields":["ID","Title","January_1", "January_2", "January_3", and so on...]

Alexander

[Iain Munro]

Thanks

That is what I thought.

Just a clarification, does all the code go into one CEWP or Script Editor ?

and assuming that I start and end with and

Iain

[Alexander Bautz]

The code is designed to run in the Custom JS section of DFFS.

Alexander

[Iain Munro]

Perfect – many thanks

Iain

[Iain Munro]

Just a quick check – do you have the code the wrong way around ?

The performance list show welders hours and the welders hours list shows performance list.

Iain

[Alexander Bautz]

That might very well be the case. I meant to read the values from the two first lists and write the “product” to the last list. My test lists was of course not set up like your master lists so I might have gotten it backwards. What is it doing when you have it in place in your lists?

Alexander

[Iain Munro]

Hi Alexander

Nothing happens when I add in data.

I would have expected an error or something.

Iain

[Alexander Bautz]

Did you update the list name and field names in the code examples?

Try adding either an alert or a console.log (you must have the F12 > Console open) in the code to see that it is actually running when you save an item.

Please note that the code added to DFFS Custom JS only works when using the forms to edit and not when you for example use datasheet view.

Alexander

[Author]

Posts