Showing posts with label input form. Show all posts
Showing posts with label input form. Show all posts

Tuesday, 6 August 2019

Can’t use Calculated Column to generate a URL?

ANYBODY WHO HAS WORKED WITH SHAREPOINT ONLINE will know that Microsoft loves to turn off functions we rely on. A recent casualty was the facility to use List Calculated Columns to concatenate a URL to a customised Display or Edit Form.


Oh, how I love to concatenate. And how sad I was when Microsoft decided
they didn't like me concatenating links.
How we would have done it in the past would be to create a Calculated column in a list, then add a formula like this:

=CONCATENATE("<A HREF='/sites/SiteName/Lists/ListName/DispForm00.aspx?ID=",[ID]'>Display Form</A>")

Or if you wanted to be really smart, you could include a redirect to take your user to a particular destination after they’d finished with the form, like this:

=CONCATENATE("<A HREF='/sites/SiteName/Lists/ListName/DispForm00.aspx?ID=",[ID],"&Source=https://xxx.sharepoint.com/sites/SiteName/Lists/ListName/'>Display Form</A>")

The problem is that Microsoft have now disabled that functionality, so that while the Calculated column will compile the link, the list will display the HTML line of code, and not a link as you intended.

I went trawling around Google, looking for a workaround for this “undocumented feature” and came up empty. There’s quite a few folks who suggest many different JavaScript and JQuery solutions, but I couldn’t get any of them to work. JavaScript is okay and can solve some of your problems in SharePoint, but I can’t help feeling it’s a bit of a hack. Far better to use in-built SharePoint technology to solve SharePoint problems where possible.

So I resolved to come up with a workable solution myself, something simpler than fiddling around with a JavaScript function that would magically transform a string of HTML into a working link.

Everything was pointing to a workflow. (You can also use a Flow, which I've documented at the foot of this posting.)

I set one up to copy the calculated HTML code into a simple text column, but of course that didn’t work.

My next attempt was to copy the concatenated string into a Hyperlink column. That kind of did work, but displayed both the link URL and the link text as the full line of HTML … and it seemed to be clickable, but it didn't quite work as I thought it would.

Even though it appeared to produce a functioning link, the "gotcha"
was that the call to the item's ID didn't work, so I had a rethink.
But that roadblock led me to a simpler, more elegant solution anyway ... use a Workflow Variable to compile and copy the URL string into a Hyperlink column.

From there, it was a straightforward task to fire up SharePoint Designer and add a simple workflow to copy the contents of a Variable to your Hyperlink Column.

In your new Workflow - I used a 2010 Workflow - under Actions, select "Set Workflow Variable".

Add your first action ...
In the following window, click on Workflow variable and select 'Create a new variable". Give a useful name and select "String" for the Type. The click on Value, and select the ellipsis to open the String Builder.

Make sure you set up the URL string in a format
that the Hyperlink column will accept.
The string should be in this format:

https://xxx.sharepoint.com /sites/SiteName/Lists/ListName/DispForm00.aspx?ID=[ID]&Source=https://xxx.sharepoint.com/sites/SiteName/Lists/ListName/, Display Form

Replace the "[ID]" in the above string with the "Current Item:ID" value from the Add or Change Lookup function.

The comma and the word space after the URL (but before the link text) is vital to ensure the string transfers to the Hyperlink field correctly.

I also set up my Workflow to check whether the Hyperlink Column was populated then, if not, copy the contents of the Variable into the Hyperlink Column, like this:

Having the Condition  place to check whether the Hyperlink column is populated or not
saves triggering the whole workflow process unnecessarily.
Then set the Workflow to trigger when a new item is created and that should be it.

This is how you want your Hyperlink column to look after the Workflow has done its job.

USING POWER AUTOMATE WITH SHAREPOINT ONLINE

So, someone asked in the comments if I'd gotten this to work with Flow ... and I can now reply, "Yes, I have."

I've had so much work converting all my existing 2010 WorkFlows to Flow that I haven't had any time to do bits of research explicitly for The SharePoint Hive, but I've just had to wait until one of my remediations called for me to do a Flow version of a WorkFlow action before posting the solution here.

It turns out that populating a SharePoint list URL field is a little fiddly in Flow, but works fine once you have it set up. Here's how ...

First we're going to add in an action - Send an HTTP Request to SharePoint. The way I used this, it was the only action in the Flow, as I needed it to create the appropriate dynamic link each time I added an item to a list. But you can use this method in any Flow where you need to create a dynamic link on the fly.

This Action is already set up, but check below for the changes you should make and any relevant code ...
So, first add your site address to the first field. Set the Method to POST. In the Uri field, add this:

_api/web/lists/GetByTitle('Reward Training')/items({ID})

... but make the {ID} bit a Dynamic Content call to the item ID. Reward Training is the name of my list

Next, click on the icon to the right of the Headers field and add this script:

 {
   "Content-Type": "application/json;odata=verbose",
   "X-HTTP-Method": "MERGE",
   "IF-MATCH": "*"
 }

The in the Body field, add this script:

{'__metadata': {'type':'SP.Data.NAMEOFYOURLISTListItem'},'NameOfColumn':
 {'Description': 'Book this slot',
 'Url': 'https://YourSite.sharepoint.com/sites/eo-event-registrations/Lists/Reward%20Training/EditForm.aspx?ID=[ID]&Source=https://YourSite.sharepoint.com/sites/eo-event-registrations/Lists/Reward%20Training/'}
}

It took me a couple of goes to understand what was going on here. I hope I've made it clear ... but the bit after SP.Data is the Name of Your List + "ListItem", no spaces. If your list name has spaces (best avoided, but on this occasion I didn't listen to my own advice) you'll need to use _x0020_ for the space.

(Weirdly, you can use a regular word space in the Uri field, and a %20 in the link you paste into the Body field. You have the whole set with this Flow Action!)

My link is a call to an Edit Form. This can be the standard out-of-the-box Edit Form or something you've customised in Power Apps - makes no difference.

And the Description value "Book this slot" is what will appear in the list as the link text.

I also added a "Source" reference so that users are taken back to the main List page once they've finished with the EditForm, but that's not mandatory.

I hope this has helped someone.



Friday, 16 November 2018

Add File Name field to custom EditForm

It's been a while since I posted here. Maybe this is because I've now switched to using SharePoint Online, and I'm finding it needs fewer workarounds than SP2007.

Okay ... that sounds a bit unlikely.

So here's a challenge I had recently. One of my clients asked that they get a File Name edit field in a custom EditForm I'd created for a Document Library.

The out-of-the-box EditForm included a File Name field as standard.

Here's an example of how a standard EditForm presents its fields. Right at the top, labelled "Name" is the file name field. We can tell it's for a PDF, as Microsoft has helpfully rendered the file suffix outside the editable field.

It seems that Microsoft disable the File Name field in custom EditForms. So I Googled to find a solution. The only workaround I came across suggested that the File Name field could be added manually by pasting some code into the EditForm in SharePoint Designer.

<asp:TextBox runat="server" id="ff26{$Pos}" ControlMode="Edit" text="{@FileLeafRef}" __designer:bind="{ddwrt:DataBind('u',concat('ff26',$Pos),'Text','TextChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@FileLeafRef')}"/>

Except that this doesn't quite work.

Let me explain.

If you use the above code - pasted in to the second table cell in the new row you'd create to hold it - you get this result:

Spot the difference - this version, the custom EditForm with the above code added doesn't render the file suffix. A bit confusing for end users, I'm thinking.
For me this was a bit of a deal-breaker. Without the visual hint that the file suffix gives the end-user, I didn't think it was obvious what that editable field was doing. Even more alarming, end users might change the file name without realising what they were doing.

This would explain why Microsoft chose to exclude this feature from the custom forms. Except, in this instance, my client needed it.

So I took a look at some of the other fields in the code and figured out a solution. Well, more of a lucky guess, really.

But first, let me say this. Look at the ID value in the above code: "ff26". SharePoint uses these values to differentiate between input fields. If you have two fields with the same ID, you will get an error: "Unable to display this web part ..." or similar.

So first make sure that before you add any code to the EditForm page you've scrolled to the end of the form fields section and checked the ID value of the last row in the form. In my case, it had the ID value of ff25.

Then, because I could see that the code for all the other form fields began with:

<SharePoint:FormField ...

I thought I'd try that instead, and re-wrote the above line of code as this:

<SharePoint:FormField runat="server" id="ff26{$Pos}" ControlMode="Edit" FieldName="FileLeafRef" __designer:bind="{ddwrt:DataBind('u',concat('ff26',$Pos),'Text','TextChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@FileLeafRef')}"/>

Be aware that the attribute "text" isn't permissible in the SharePoint:FormField tag, so I used FieldName instead.

And it worked.

The File Name field rendered perfectly and even tagged on a file suffix after the text field.


Success ... those small alterations to the code cause the custom EditForm to render in exactly the same way as the out-of-the-box one.
I hope that's helped someone ...





Wednesday, 2 November 2011

Hiding fields in NewForm.aspx

Sometimes you may want to set a field in a SharePoint 2007 list to pre-populate with a default value. However, SP still allows your site visitors to alter the value via the New Item form. For example, when I want to capture data supplied by the visitor, but I know only admin staff will be looking at the data, I'll often assign the default value of "Edit Details" to the Title field, so that admin staff know to click on the "Edit Details" hyperlink to see details of that List row.

One answer is to edit the NewForm.aspx page so that the Title field is not displayed. That way site visitors can't change the pre-set default value for the field.

Here's how ...

1. Open SharePoint Designer and navigate to the site that contains the list or document library you wish to customize.

2. Expand the folder named “Forms” under the desired list or document library. You should see about four .aspx pages (AllItems.aspx, EditForm.aspx, NewForm.aspx, etc)


Edit the NewForm.aspx file, and add the Javascript directly ...
3. Check out and open the NewForm.aspx page and switch to the “code” view to edit the HTML of the page.

4. Paste this JavaScript code immediately below the the following HTML tag (usually line 14):
 <asp:Content ContentPlaceHolderId=”PlaceHolderMain” runat=”server”> 

<script language="javascript" type="text/javascript">
_spBodyOnLoadFunctionNames.push("hideFields");
function findacontrol(FieldName) {
   var arr = document.getElementsByTagName("!");
   // get all comments
   for (var i=0;i < arr.length; i++ )
   {
      // now match the field name
      if (arr[i].innerHTML.indexOf(FieldName) > 0)
      {         return arr[i];      }
   }
}

function hideFields() {
   var control = findacontrol("Title");
   control.parentNode.parentNode.style.display="none";
   control = findacontrol("Document Link");
   control.parentNode.parentNode.style.display="none";
   control = findacontrol("PublishDate");
   control.parentNode.parentNode.style.display="none";

}
</script>


This will add the JavaScript to the HTML inside the content placeholder tag. 
Note: be sure to include the entire script above, including the <script> and </script> tags.


5. Modify the “hidefields()” section of the JavaScript code to refer to each SharePoint list field name to hide.  For example, the code sample above will hide the SharePoint fields named Title, Document Link, and PublishDate.  Notice that you do not need to worry about internal field names or field types like other JavaScript techniques, you simply need to know the name of the field.

6. Save the changes and check the page back in.  Select “Yes” when prompted to “… customize the page from the site definition …”

7. Test the form

Please note - this method doesn't work for SharePoint Online/2013/2016.

If you're using SP Online, you'd need to add a different JavaScript function.

Try this:

<script language="javascript" type="text/javascript">

$(document).ready(function() {

   {
    $('nobr:contains("Title")').closest('tr').hide();
    $('nobr:contains("Document Link")').closest('tr').hide();
    $('nobr:contains("PublishDate")').closest('tr').hide();
   }

});

</script>

You can add a SharePoint 2016 Script Editor Web Part to the List Form page to hold the above JavaScript ... it's less cumbersome than planting the script in Site Assets and referencing it with a Content Editor Web Part. 

Hope this helps someone.