Set field values in List A from List B

Forums Classic DFFS Set field values in List A from List B

Viewing 13 reply threads
  • Author
    Posts
    • #22798
      MikeS
      Participant

      I’ve one last piece for my DFFS email to figure out. List A is the main list from which the email is generated. The email requires two fields (multi-value) from List B. List A and List B have a common field (Field 1, which may have more than one value) by which they are related.

      SharePoint look-ups and associated fields (which I use extensively) won’t work for this due to various reasons. I’ve explored the various “set field value” forum posts but not sure how to approach it with JS. Any help appreciated.

      Mike

    • #22824
      Alexander Bautz
      Keymaster

      I’m not sure I understand exactly what you mean – is it a problem finding the correct item in List B, or is it setting the values in List A based on the values pulled back from List B?

      Alexander

    • #22826
      MikeS
      Participant

      The latter, i.e., I need to set values in List A based on values pulled back from List B.

      Use case description:
      Values in List B need to be copied to List A based on the field (multi-value) in common between the two lists. For example, the common Field 1 could have one or more values in List A. Find that set of matching values in List B and pull back to List A from List B:

      Field 2 (could have one or more values based on Field 1),
      Field 3 (could have one or more values based on Field 1)

      This is similar to what SharePoint does with a multi-value lookup using Field 1 from List A to List B that also brings over multiple associated fields from List B to List A.

      Mike

    • #22849
      Alexander Bautz
      Keymaster

      What kind of field is it in list a that matches list b – is it a multichoice or a multilookup? Also, if the user have selected three values in the field in list a: should it find the exact same combination of selected values in one list item in list b, or find three items in list b that matches the individual selected values in list a?

      Alexander

    • #22853
      MikeS
      Participant

      Here are additional descriptions to help you understand this better.

      Overall description:
      In SharePoint the user performs a single lookup (one-to-one) from List A (List_A_Field_1) to List B Title (List_B_Title). List_B_Title then brings over to List A one or more values that are associated with the List_B_title (from a previous lookup from List B to List C). So what I need is for List A to look back to List B and retrieve additional fields to List A based on the exact same combination of multiple associated fields that match between List A and List B. In essence, I’m matching a string with one or more values between two lists, and then requesting additional fields for List A from List B.

      More detail:
      List A has an Internal Field (List_B_Title_x003_aField1) that is populated with one or more Associated Field values from a SharePoint Lookup to a Title field (List_B_Title) in List B. I need to match the values in this field to the exact same combination in List B and retrieve List_B_Field2 and List_B_Field3 to List A. Both of these fields may have multiple values based on whether List_B_Title_x003_aField1 has multiple values.

      The internal field names in List B for retrieval to List A are:

      ListC_x003a_Date
      ListC_x003a_Decision

      because they resulted from a previous lookup from List B to List C.

      Summary:

        List B connects to List C (one-to-many)
        List A connects to List B (one-to-one) but brings across the multiple values reflected in the List B to List C connection as an associated field
        Retrieve additional fields to List A from List B based on an exact match to one or more values in the associated lookup fields.

      Let me know if you need a diagram or additional explanation.
      Mike

    • #22871
      Alexander Bautz
      Keymaster

      To be able to write the code I need to recreate the three lists with the fields involved. I need screenshots of all three lists with the fields the connection is supposed to use to match between the lists and also the fields you want to pull in as associated fields in the lookups.

      Alexander

    • #22883
      MikeS
      Participant

      The attached screenshots of the three lists and an overall schematic of how they interact should be what you need.

      Thank you for your help.

      Mike

    • #22892
      Alexander Bautz
      Keymaster

      Looking at your sketch I think maybe this could be better solved by using some custom js. Is you plan to run a query from backlog to change request and pull back some fields from the change request where the one or more of the tickets (in TNLooup) is not OK?

      Alexander

    • #22895
      MikeS
      Participant

      I agree, custom JS is the way to go.

      Will the custom JS be able to be run independently of the edit or add form? Is that what you mean by ‘run a query?’ Being able to add this custom JS as a CEWP in the data sheet view could be helpful, but that is strcitly a ‘nice to have.’ Otherwise I assume it will be placed in the custom JS section of DFFS and it will be triggered by a rule, e.g., on Edit or Add form Save.

      When the Edit or Add form is saved then the fields should be pulled over. If possible pull them over to the Backlog list ONLY if the previously pulled over field differs from the field in the originating list. In other words, only pull over if there is an update in the originating CR list. That prevents needless updates in the Backlog list when other fields are updated (as would be the case if they were pulled over after every Save and simply copied over the same information).

      Mike

    • #22913
      Alexander Bautz
      Keymaster

      The custom js would be for use in DFFS only.

      I’m still a bit confused about how you plan to update the backlog list, and what kind of data you want to bring over from the CR list. Are the backlog items added manually, or do you want to automatically add backlog items when a CR item is created?

      My suggestion would be to use a functionality like in vLookup where the data from CR is only shown in the Backlog list based on a live query building the fields with the CR data live in the form when viewing it.

      Please note that I don’t fully understand how the dynamic between the three lists is intended to work.

      Alexander

    • #22915
      MikeS
      Participant

      Are the backlog items added manually, or do you want to automatically add backlog items when a CR item is created?

      A: The backlog items are added manually. After adding a backlog record the user selects a CR from a single value lookup in the Backlog list.

      . . . the dynamic between the three lists . . .

      1. The Ticket list is auto-populated from another database outside SharePoint.
      2. The CR list is initially auto-populated from another database outside SharePoint. The user interacts with the list by associating a single CR with multiple Ticket Numbers from a multi-value lookup in the CR list. This lookup also brings over two associated fields from the Ticket list: TNDecision and TNDate.
      3. The Backlog list is completely manual. The user enters a BLNo manually and then associates a single CR with a Backlog Number (BLNo) from a single-value lookup in the Backlog list. This lookup cannot bring forward the TNDecision and TNDate fields from the CR list however, so I need another solution.
      4. A custom JS for use in DFFS would work fine. That way I could have it fire using very specific rules (e.g., permission group).

        Mike

    • #22924
      Alexander Bautz
      Keymaster

      Try adding a HTML section in one of your tabs in the Backlog list with this placeholder:

      <div id="changeRequestPlaceholder"></div>

      Then add this to your Custom JS:

      function exampleFunction_getCR(){
          jQuery("#changeRequestPlaceholder").html("<img src='"+_spPageContextInfo.webServerRelativeUrl + "/_layouts/15/images/gears_anv4.gif' style='margin:10px;height:16px;'>");
          spjs.utility.getListByUrlName(_spPageContextInfo.webServerRelativeUrl,"LIST_NAME_FROM_URL").done(function(list){
              var cr, crRes, ticketArr = [], allOK = true, b = [];
              cr = getFieldValue("CRLookup");
              crRes = spjs.utility.queryItems({
                  "listName":list.id,
                  "query":"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>"+cr+"</Value></Eq></Where>",
                  "viewFields":[
                      "ID",
                      "Title",
                      "TNLookup_x003a_TNDecision",
                      "TNLookup_x003a_TNDate"
                  ]
              });
              jQuery.each(crRes.items,function(i,item){
                  // Check to see if the TNDecicion field contains anything other than OK
                  jQuery.each(item.TNLookup_x003a_TNDecision.split(";#"),function(j,v){
                      if(j %2 !== 0 && v !== "OK"){
                          allOK = false;
                      } 
                  });
                  if(!allOK){
                      b.push("<table cellpadding='3' cellspacing='0'>");
                      b.push("<tr>");
                      b.push("<td>ID</td>");
                      b.push("<td>" + item.ID + "</td>");
                      b.push("</tr>");
                      b.push("<tr>");
                      b.push("<td>TNDecisions</td>");
                      // Find text values from TNDecision field
                      var tnDecisions = [];
                      jQuery.each(item.TNLookup_x003a_TNDecision.split(";#"),function(j,v){
                          if(j%2 !== 0){
                              tnDecisions.push(v);
                          }
                      });
                      b.push("<td>" + tnDecisions.join("; ") + "</td>");
                      b.push("</tr>");
                      b.push("<tr>");
                      b.push("<td>TNDates</td>");
                      // Find text values from TNDate field
                      var tnDates = [];
                      jQuery.each(item.TNLookup_x003a_TNDate.split(";#"),function(j,v){
                          if(j%2 !== 0){
                              tnDates.push(v);
                          }
                      });
                      b.push("<td>" + tnDates.join("; ") + "</td>");
                      b.push("</tr>");
                      b.push("</table>");
                  }else{
                      b.push("<div style='padding:10px;'>All change requests on " + item.Title + " are OK.</div>");
                  }
              });
              jQuery("#changeRequestPlaceholder").html(b.join(""));
          });
      }
      // Call function
      exampleFunction_getCR();

      Change “LIST_NAME_FROM_URL” with the URL name of your list and look over the field names to ensure they are correct. This should be a starting point, but please note that it will not write the data to the Backlog list item – it only shows the data when viewing the form. If you like to write it to the current item you must add code like this in the above function:

      // Write tnDecisions to a field.
      setFieldValue("Name_of_field",tnDecisions.join("; "));

      Hope this gets you started.

      Alexander

    • #22926
      MikeS
      Participant

      A great solution Alexander. I was able to modify it a bit in order to 1) write the pulled back fields to the Backlog list so I can place them in the DFFS email and 2) add additional pulled back fields. I also set up a DFFS rule so that the pulled back fields dynamically change in the Edit form as the CR Lookup field is changed by the user.

      Just one item:

      The TNDateBL field in the Backlog list has a format of:
      2018-11-01 00:00:00
      after being pulled back from the CR list.

      while the originating field in the CR list (TNDate) is displayed as:
      11/1/2018.

      Any suggestions to match that format in the Backlog list? I don’t need a time stamp, just date.

      Beers on the way!

      Thanks,
      Mike

    • #22928
      Alexander Bautz
      Keymaster

      I’m glad it worked. To format the date you must change the code like this:

      // Find text values from TNDate field
      var tnDates = [];
      jQuery.each(item.TNLookup_x003a_TNDate.split(";#"),function(j,v){
          if(j%2 !== 0){
              tnDates.push(new Date(v.split(" ").join("T")).toLocaleDateString());
          }
      });

      Alexander

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