New Data based on 2 sharepoint lists

Forums General discussion New Data based on 2 sharepoint lists

This topic contains 18 replies, has 2 voices, and was last updated by  Alexander Bautz 8 months, 2 weeks ago.

  • Author
    Posts
  • #19097

    Iain Munro
    Participant

    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 9 months ago by  Iain Munro.
  • #19119

    Alexander Bautz
    Keymaster

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

    Alexander

  • #19121

    Iain Munro
    Participant

    Hi Alexander

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

    Iain

  • #19125

    Iain Munro
    Participant

    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:
  • #19163

    Iain Munro
    Participant

    Hi Alexander

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

    Iain

  • #19165

    Alexander Bautz
    Keymaster

    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

  • #19171

    Iain Munro
    Participant

    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

  • #19176

    Alexander Bautz
    Keymaster

    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

  • #19179

    Iain Munro
    Participant

    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

  • #19186

    Alexander Bautz
    Keymaster

    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);
        }
    }
  • #19198

    Iain Munro
    Participant

    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

  • #19201

    Alexander Bautz
    Keymaster

    You must add all fields like this:

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

    Alexander

  • #19203

    Iain Munro
    Participant

    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

  • #19205

    Alexander Bautz
    Keymaster

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

    Alexander

  • #19207

    Iain Munro
    Participant

    Perfect – many thanks

    Iain

  • #19250

    Iain Munro
    Participant

    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

  • #19255

    Alexander Bautz
    Keymaster

    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

  • #19275

    Iain Munro
    Participant

    Hi Alexander

    Nothing happens when I add in data.

    I would have expected an error or something.

    Iain

  • #19281

    Alexander Bautz
    Keymaster

    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

You must be logged in to reply to this topic.