vLookup and a document template

Forums vLooup for SharePoint vLookup and a document template

Tagged: 

Viewing 12 reply threads
  • Author
    Posts
    • #29668
      Paul Woodcock
      Participant

      Hi Alex,

      I have a SharePoint list connected to a SharePoint Document library via a vlookup. The document library has a word document template connected to it. When you are in the parent list is there a way of opening a new document generated from the template when you click + add new, instead of browsing for a document?

      Usually when I create a new document from the template in a webpage I use the following script:

      <a onclick="CoreInvoke('createNewDocumentWithProgIDEx',event,'https:\u002f\u002fcollaboration.contoso.com\u002fteams\u002fRegistrars\u002fTrainingExcessDeaths\u002fForms\u002fRegisterDeath\u002fPre-Registration-form.docx', 'https:\u002f\u002fcollaboration.contoso.com\u002fteams\u002fRegistrars\u002fTrainingExcessDeaths', 'SharePoint.OpenDocuments', false, 'ms-word')">

      I tried this script in the from field of prefill values in child and {url} in the To field. But this did not work.

      Is it possible to do this?

      Thanks

      Paul

      • This topic was modified 5 months, 3 weeks ago by Alexander Bautz. Reason: Obfuscated the site URL
    • #29676
      Alexander Bautz
      Keymaster

      Hi,
      This is not possible without some custom js. I created this function to let you create a new document from a template, set the metadata used to connect it to your item / vLookup and then open it in Word for the user.

      Please not that I could not access the actual template from the /Forms folder of the document library so you must either create a separate document library (named Templates in my example) or add the template document to your current document library as a docx and NOT a dotx (in case you use quickparts to show metadata in the document).

      You must change the six variables in the top of the function.

      function newDocFromTemplate() {
          var vLookupFieldname = "vLookupDocuments"; // Name of the vLookup field in this list
          var templateLibRelUrl = _spPageContextInfo.webServerRelativeUrl + "/Templates"; // Relative URL to the document library where the template is found
          var templateName = "Template_1.docx"; // Name of the tempate. It must be docx and not dotx
          var targetLibGUID = "{ab6a0e84-43e0-4acd-a76a-3d96e9a70933}"; // GUID of the save target document library. Used when getting and setting metadata to connect to this vLookup
          var targetLibPath = _spPageContextInfo.webServerRelativeUrl + "/Shared Documents"; // Relative URL to the document library you want to save to
          var newDocName = "New_doc_" + jQuery.now() + ".docx"; // Name of new document
          jQuery.ajax({
              "url": _spPageContextInfo.webServerRelativeUrl + "/_api/web/folders/GetByUrl('" + templateLibRelUrl + "')/Files/getbyurl('" + templateName + "')/copyTo(strNewUrl = '" + targetLibPath + "/" + newDocName + "',bOverWrite = true)",
              "method": "POST",
              "headers": {
                  "Accept": "application/json; odata=verbose",
                  "X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
              },
              "success": function() {
                  // Successfully created file - get id and set metadata
                  var res = spjs.utility.queryItems({
                      "listName": targetLibGUID,
                      "query": "<Where><Eq><FieldRef Name='FileLeafRef' /><Value Type='Text'>" + newDocName + "</Value></Eq></Where>",
                      "viewFields": ["ID", "EncodedAbsUrl"]
                  });
                  if (res.count > 0) {
                      var item = res.items[0];
                      var uRes = spjs.utility.updateItem({
                          "listName": targetLibGUID,
                          "id": item.ID,
                          "data": {
                              "_vLookupParentID": getFieldValue("_vLookupID") // Change this to match the query you use in the vLookup config to retrieve the documents
                          }
                      });
                      if (uRes.success) {
                          // Refresh vLookup table
                          jQuery("#vLookupManualRefresh_" + vLookupFieldname).trigger("click");
                          var a = document.createElement("a");
                          a.setAttribute("href", "ms-word:ofe|u|" + item.EncodedAbsUrl);
                          a.setAttribute("target", "_blank");
                          document.getElementsByTagName("body")[0].appendChild(a);
                          a.click();
                      }
                  }
              },
              "error": function(err) {
                  // Failed to copy document
                  alert("Failed to copy:\n\n" + JSON.stringify(err));
              }
          });
      }

      Add this to your Custom JS and call the function from a button created in a HTML section like this:

      <input style="margin-left:0;" type="button" onclick="newDocFromTemplate();return false;" value="New from template">

      Let me know how this works out.

      Alexander

    • #29709
      Paul Woodcock
      Participant

      Hi Alex,

      I’m getting the following error message:

      Failed to copy:
      {“readyState”:4,”responseText”:”The length of the URL for this request exceeds the configured maxUrlLength
      value.”,”Status”:400,”statusText”:”Bad Request”}

      I’m wondering if the document is not encoded correctly, and this is being pushed into the url?

      Paul

    • #29711
      Alexander Bautz
      Keymaster

      Which version of SP are you running?

      I’m not sure what could cause this, but, ensure all variables in the top of the function are correct.

      Alexander

    • #29717
      Paul Woodcock
      Participant

      Hi,

      Its an on premise SharePoint 2013 site. I also occasionally get the attached error. I’ve checked the variables again. I’m fairly certain they are correct but I’ll go back over them.

      Paul

      Attachments:
    • #29728
      Alexander Bautz
      Keymaster

      The error message looks like it is the SharePoint “error message page” HTML code you get in return. You will see more error details if you open the dev tools (hit F12) and look at the Console and Network tabs. I recommend using Google Chrome (or the new Edge with Chromium engine) because they have better debugging capabilities.

      It may very well be that the code only works in SP Online – I’ll see if I can test it in an on-prem site later to see if I find out more.

      Alexander

    • #29730
      Alexander Bautz
      Keymaster

      I tested it in an on-prem 2016 site without problems. Can you show me the six variables you use in the top of the function?

      Alexander

    • #29733
      Paul Woodcock
      Participant

      Hi Alex

      My variables are:

      
      
      var vLookupFieldname = "vLookup_Children"; // Name of the vLookup field in this list
          var templateLibRelUrl = _spPageContextInfo.webServerRelativeUrl + "/teams/Registrars/TrainingExcessDeaths/Forms/RegisterDeath"; // Relative URL to the document library where the template is found
          var templateName = "Pre-Registration-form.docx"; // Name of the tempate. It must be docx and not dotx
          var targetLibGUID = "{BD751507-B0AF-4ED3-9315-800384BFD011}"; // GUID of the save target document library. Used when getting and setting metadata to connect to this vLookup
          var targetLibPath = _spPageContextInfo.webServerRelativeUrl + "/teams/Registrars/TrainingExcessDeaths/Forms"; // Relative URL to the document library you want to save to
          var newDocName = "New_death_" + jQuery.now() + ".docx"; // Name of new document
    • #29742
      Alexander Bautz
      Keymaster

      Is the “Forms” here a folder in a document library or is it a subsite with a document library named RegisterDeath in it? – please explain the URL path so I know what is subsites, document libraries and folders.

      Alexander

    • #29745
      Paul Woodcock
      Participant

      Hi Alex,

      – Registrars is the site
      – TrainingExcessDeaths is the document library
      – Forms is part of the file path but I’m not sure what it is
      – RegisterDeath is the content type where the pre-registration-form.docx document template is located.

      I don’t have any folders in the document library.

      I’ve tried just using teams/Registrars/TrainingExcessDeaths as the file path for templateLibRelUrl and targetLibPath. But I still get the same error message.

      From looking at the Microsoft REST documentation I think there is a slight difference between SharePoint 2016/online and Sharepoint 2013. I’m going to try altering the REST api code tomorrow when I’m back in work.

      Paul

    • #29760
      Alexander Bautz
      Keymaster

      OK, you must move the template out from the /Forms folder like I specified in the original comment above:

      Please not that I could not access the actual template from the /Forms folder of the document library so you must either create a separate document library (named Templates in my example) or add the template document to your current document library as a docx and NOT a dotx (in case you use quickparts to show metadata in the document).

      This means that if you add the template to the same document library as you plan to save the files in your templateLibRelUrl must be

      var templateLibRelUrl = _spPageContextInfo.webServerRelativeUrl + "/teams/Registrars/TrainingExcessDeaths";

      and your targetLibPath must be:

      var targetLibPath = _spPageContextInfo.webServerRelativeUrl + "/teams/Registrars/TrainingExcessDeaths";

      Ensure you have a file named Pre-Registration-form.docx in the TrainingExcessDeaths library.

      Alexander

    • #29784
      Paul Woodcock
      Participant

      Hi Alex,

      Thank you for taking the time to help me. I got it working. I had not appreciated that the ‘template’ must be an item in a document library rather than stored as a template within the content type settings.

      Paul

      • This reply was modified 5 months, 3 weeks ago by Paul Woodcock.
    • #29803
      Alexander Bautz
      Keymaster

      Thanks for the feedback – I’m glad it worked out.

      Alexander

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