Set field values in List A from List B

[MikeS]

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

[Alexander Bautz]

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

[MikeS]

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

[Alexander Bautz]

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

[MikeS]

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

[Alexander Bautz]

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

[MikeS]

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

Attachments:

1. 

   DFFS_Pull_Forward_Fields_Schematic.png
   

2. 

   1-TicketList.png
   

3. 

   2-ChangeRequestList.png
   

4. 

   3-BacklogList.png
   

[Alexander Bautz]

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

[MikeS]

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

[Alexander Bautz]

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

[MikeS]

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.

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

[Alexander Bautz]

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

[MikeS]

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

[Alexander Bautz]

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

[Author]

Posts