Send vLookup data in a workflow email

Home Forums vLooup for SharePoint Send vLookup data in a workflow email

Viewing 16 reply threads
  • Author
    Posts
    • #20731
      Alexander Bautz
      Keymaster

        I got a question from Michel Hayek regarding sending the vLookup table data in a workflow email.

        This is not possible without pulling the data from the vLookup data object and writing it to a multiline text field in your form. This is because the vLookup data only “lives” in the browser, and is not stored in the list item when you save the form.

        To write the data to a multiline field when the form is saved you can use this snippet in your Custom JS. Replace vLookupTasks with the vLookup field name in your form, and Multiline with the multiline field you want to store the data in.

        function dffs_PreSaveAction(){
            write_vLookup_to_multiline("vLookupTasks","Multiline");
            return true;
        }
        
        function write_vLookup_to_multiline(vLookuField,targetField){
            var b = [], val;
            b.push("<table cellpadding='4' cellspacing='0'>");
            // Header row
            b.push("<tr>");
            jQuery.each(spjs.vLookup.dataObj[vLookuField][spjs.dffs.data.thisItemID].fields,function(fin,disp){
                b.push("<th valign='top'>"+disp+"</th>");
            });
            b.push("</tr>");
            // Body
            jQuery.each(spjs.vLookup.dataObj[vLookuField][spjs.dffs.data.thisItemID].items,function(i,item){
                b.push("<tr>");
                jQuery.each(spjs.vLookup.dataObj[vLookuField][spjs.dffs.data.thisItemID].fields,function(fin,disp){
                    val = item[fin] !== null ? item[fin] : "";
                    val = val.split("<").join("<").split(">").join(">");
                    b.push("<td valign='top' style='"+(i%2===1?"background-color:#eaeaea;":"")+"'>"+val+"</td>");
                });
                b.push("</tr>");
            });
            b.push("</table>");
            setFieldValue(targetField,b.join(""));
        }

        Please note that the styling of the table must be done by adding inline style tags to the <tr> or <td> (like I have done to add color to alternating rows). The SharePoint stylesheets will not affect the layout when it is rendered in the email client of the user so you will have to experiment until you find your preferred style.

        Please note that the vLookup table must be made visible in a tab before saving the item for the vLookup dataObj to be available for this script.

        Hope someone finds this useful.

        Alexander

      • #20749
        HYM
        Participant

          Okay, thank you Alexander it worked for me.

        • #26285
          Notrega
          Participant

            I can’t seem to get this to work. I followed the directions and changed vLookupTask with my field name vLookupItem and Multiline with my field name Email_Text, but now it won’t let me save the record at all…

          • #26287
            Notrega
            Participant

              Changed field names and still no go, but here is what I got out of Console View –

            • #26293
              Alexander Bautz
              Keymaster

                The reason is that the vLookup data object is different in NewForm because the list item does not have any ID when it is not yet saved.

                Change the snippet from this:

                jQuery.each(spjs.vLookup.dataObj[vLookuField][spjs.dffs.data.thisItemID].fields,func...

                to this:

                jQuery.each(spjs.vLookup.dataObj[vLookuField][getFieldValue("_vLookupID")].fields,func...

                Please note that this must be done two places. It replaces spjs.dffs.data.thisItemID with getFieldValue(“_vLookupID”).

                Alexander

              • #26295
                Notrega
                Participant

                  You sir are a fricking genius – THANK YOU!!
                  Beers on me on payday

                • #26297
                  Notrega
                  Participant

                    Last question – is there way to sort or group the view?

                  • #26388
                    Alexander Bautz
                    Keymaster

                      I see – change the snippet like this:

                      function dffs_PreSaveAction() {
                          write_vLookup_to_multiline({
                              "fin": "vLookupTasks", // Name of vLookup column
                              "writeTo": "Multiline", // Name of field to write to
                              "groupBy": "Status" // Name of field to group by
                          });
                          return true;
                      }
                      
                      function write_vLookup_to_multiline(arg) {
                          var b = [], val, arr = [];
                          b.push("<table cellpadding='4' cellspacing='0'>");
                          // Header row
                          b.push("<tr>");
                          jQuery.each(spjs.vLookup.dataObj[arg.fin][spjs.dffs.data.thisItemID].fields, function (fin, disp) {
                              b.push("<th valign='top'>" + disp + "</th>");
                          });
                          b.push("</tr>");
                          // Body - make array of items
                          jQuery.each(spjs.vLookup.dataObj[arg.fin][spjs.dffs.data.thisItemID].items, function (i, item) {
                              arr.push(item);
                          });
                          // Sort by groupBy field
                          arr.sort(function (a, b) {
                              if (a[arg.groupBy] < b[arg.groupBy]) {
                                  return -1;
                              }
                              if (a[arg.groupBy] > b[arg.groupBy]) {
                                  return 1;
                              }
                              return 0;
                          });
                          var groupHeaderTracker = {};
                          jQuery.each(arr, function (i, item) {
                              if (groupHeaderTracker[item[arg.groupBy]] === undefined) {
                                  groupHeaderTracker[item[arg.groupBy]] = true;
                                  b.push("<tr>");
                                  b.push("<td colspan='99' valign='top' style='padding:5px;background-color:#c5c5c5;'>" + item[arg.groupBy] + "</td>");
                                  b.push("</tr>");
                              }
                              b.push("<tr>");
                              jQuery.each(spjs.vLookup.dataObj[arg.fin][spjs.dffs.data.thisItemID].fields, function (fin, disp) {
                                  val = item[fin] !== null ? item[fin] : "";
                                  val = val.split("<").join("<").split(">").join(">");
                                  b.push("<td valign='top' style='" + (i % 2 === 1 ? "background-color:#eaeaea;" : "") + "'>" + val + "</td>");
                              });
                              b.push("</tr>");
                          });
                          b.push("</table>");
                          setFieldValue(arg.writeTo, b.join(""));
                      }

                      Alexander

                    • #28060
                      Notrega
                      Participant

                        Hey Alex – hope your holiday season is going great.

                        We are successfully using the code above in several lists, but are having issues using multiple instances of the code at the same time. Basically we have a list that has 6 vLookups that we want to write them to multiline fields so that we can eventually archive the vLookup records and still maintain a snapshot of the data in the parent record.

                        The issue is that we can get them all to work, then some stop working. So we remove 5 and leave one, it works for maybe a few records and then prevents the users from saving the parent record.

                        Is there a better way to accomplish what we need or different code we can use?

                      • #28062
                        Alexander Bautz
                        Keymaster

                          What kind of error message do you get when the record cannot be saved? – maybe you must hit F12 and select Console to see the error.

                          Alexander

                        • #28067
                          Notrega
                          Participant

                            No error… We found the vlookup had to be on the active tab. We fixed the no save issue but still need help with the multiple calls.

                          • #28070
                            Alexander Bautz
                            Keymaster

                              Hi,
                              I’m not 100% sure I understand, but because the vLookup table for each field is only loaded when it is shown in a tab you must check to see if each of your vLookup columns have actually been rendered in this form before you try to save the state.

                              Wrap the code for each vLookup like this:

                              if(spjs.vLookup.dataObj.YOUR_vLookup_FIELD_NAME !== undefined){
                                // Run you code here
                              }

                              Alexander

                            • #28163
                              Notrega
                              Participant

                                So I figured it out… here is how to call multiple vLookups to multiple multi-line fields… I had been duplicating the whole code for each vLookup, but it only needs the information in the first function.

                                
                                
                                function dffs_PreSaveAction() {
                                    write_vLookup_to_multiline({
                                        "fin": "vLookupShiftAssignments", // Name of vLookup column #1
                                        "writeTo": "ShiftAssignments", // Name of field to write to
                                        "groupBy": "Category_" // Name of field to group by
                                    });
                                        write_vLookup_to_multiline({
                                        "fin": "vLookupSecurityInspection", // Name of vLookup column #2
                                        "writeTo": "SecurityInspection", // Name of field to write to
                                        "groupBy": "InspectionComplete" // Name of field to group by
                                    });
                                    write_vLookup_to_multiline({
                                        "fin": "vLookupContrabandInspection", // Name of vLookup column #3
                                        "writeTo": "ContrabandInspection", // Name of field to write to
                                        "groupBy": "Mod" // Name of field to group by
                                    });    
                                    return true;
                                
                                }
                                
                                function write_vLookup_to_multiline(arg) {
                                    var b = [], val, arr = [];
                                    b.push("<table cellpadding='4' cellspacing='0' class='vLookupText'>");
                                    // Header row
                                    b.push("<tr>");
                                    jQuery.each(spjs.vLookup.dataObj[arg.fin][spjs.dffs.data.thisItemID].fields, function (fin, disp) {
                                        b.push("<th valign='top' class='vLookupTextth'>" + disp + "</th>");
                                    });
                                    b.push("</tr>");
                                    // Body - make array of items
                                    jQuery.each(spjs.vLookup.dataObj[arg.fin][spjs.dffs.data.thisItemID].items, function (i, item) {
                                        arr.push(item);
                                    });
                                    // Sort by groupBy field
                                    arr.sort(function (a, b) {
                                        if (a[arg.groupBy] < b[arg.groupBy]) {
                                            return -1;
                                        }
                                        if (a[arg.groupBy] > b[arg.groupBy]) {
                                            return 1;
                                        }
                                        return 0;
                                    });
                                    var groupHeaderTracker = {};
                                    jQuery.each(arr, function (i, item) {
                                        if (groupHeaderTracker[item[arg.groupBy]] === undefined) {
                                            groupHeaderTracker[item[arg.groupBy]] = true;
                                            b.push("<tr>");
                                            b.push("<td style='padding:5px; valign:top; border:none;'>" + item[arg.groupBy] + "</td>");
                                            b.push("</tr>");
                                        }
                                        b.push("<tr>");
                                        jQuery.each(spjs.vLookup.dataObj[arg.fin][spjs.dffs.data.thisItemID].fields, function (fin, disp) {
                                            val = item[fin] !== null ? item[fin] : "";
                                            val = val.split("<").join("<").split(">").join(">");
                                            b.push("<td valign='top' class='vLookupTexttd' style='" + (i % 2 === 1 ? "background-color:#eaeaea;" : "") + "'>" + val + "</td>");
                                        });
                                        b.push("</tr>");
                                    });
                                    b.push("</table>");
                                    setFieldValue(arg.writeTo, b.join(""));
                                }
                                • This reply was modified 4 years, 9 months ago by Notrega.
                              • #29122
                                HYM
                                Participant

                                  Hi Alex, to send the vlookup data by email is working fine but if we need to generate a document including the vlookup report i am not able to save it as table in a word/excel document, the table format will change to text even if i set it as plain text. any idea?

                                • #29129
                                  Alexander Bautz
                                  Keymaster

                                    I don’t understand what you mean – where is it converted to text – in the textarea you write to? – if so, it is saved as text here. How are you generating the Word / Excel file?

                                    Alexander

                                  • #29146
                                    HYM
                                    Participant

                                      Hi Alex, i am saving the vlookup data in a multi-line field in a list and i have a workflow generating a doc (word) in a doc library, the metadata of this doc are pulled from a list where i have the vlookup data in a multi-line text field. i need to save the table design in my word document cz now its just lines the design will be destructed.

                                    • #29162
                                      Alexander Bautz
                                      Keymaster

                                        The HTML code that you have stored in the multiline text field in your list item is in fact text HTML encoded as text – and I’m not sure you can convert it to actual HTML in word at all – it depends on the solution you use to create the word file.

                                        I’m unfortunately not familiar with converting to Word using a workflow so I’m afraid I cannot help you with that.

                                        You might be able to use javascript to generate your word file from HTML directly. I don’t have any ready solution for you, but you will find a lot on the internet – for example here (not tested): https://www.codexworld.com/export-html-to-word-doc-docx-using-javascript/

                                        Alexander

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