New Data based on 2 sharepoint lists

Forums General discussion New Data based on 2 sharepoint lists

Viewing 18 reply threads
  • 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 3 years, 11 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

Viewing 18 reply threads
  • You must be logged in to reply to this topic.