• Tag Archives SharePoint
  • Featured ImageOrganizing Custom List Items to Display Content Based on Fiscal Quarter

    Requirement: Organize potentially tens of thousands of rows of data in a custom list in Office 365 SharePoint 2013 Online.

    Projected Risks: The 5000 item threshold limit cap & getting a fiscal quarter.

    Summary: I had an interesting task of making a custom library for field reps to document their visits to different locations. We have about 50 or so reps that go to over 50 different locations each per quarter. So as you can guess, the list is going to grow quickly! We already had content to be added into the library in Excel dating back to 2013. So we would be importing that content when the new list is ready.

    So my biggest issue was trying to figure out how to get the fiscal quarter value. Obviously, I would need to use a custom SPD workflow to calculate the value of fiscal year and quarter.

    Custom Views per fiscal quarter

    So in order to organize the content easily, I created custom views. Each filtered based on a value in the Fiscal Quarter field that denoted the quarter and fiscal year. Basically the value was “Q1 2013”, “Q2 2013”, “Q3 2013” and so on.

    Below is the Filter function to be setup on each of the new views settings page. I created a column called “Calendar Quarter” and the value as you can see below is formatted as Q2 2015.

    6

    Fortunately, one of the columns in the spreadsheet was called “Visit Date” and was a basic date field (MM/DD/YYYY). So that gave me something to work with. All I had to do was convert that to my text formatting of the Calendar Quarter column.

    To Begin

    I started to layout a blue print of the custom workflow path. A process I find very helpful in gathering my thoughts to design a custom workflow.

    2

    The Workflow

    Creating the Variables for the conversion

    I created the following STRING variables to the workflow to pass all the values around to get my resultant values of “Q1 2013”, “Q4 2014”, etc.:

    • Fiscal Date Conversion: The Visit Date value is in a date format of MM/DD/YYYY which is useless to us unless we convert it to a string. So the first rule in the workflow is to copy the Visit Date calendar value to a string format for getting the values.
    • Fiscal Month Conversion: Once we have the string, we copy the 1st two characters (MM) from the new Fiscal Date Conversation variable here)
    • Fiscal Year: No real conversion needed. Copy the last four characters of the Fiscal Date Conversion to this variable will give us the four digit year.
    • Fiscal Text Quarter: This was the unexpected field. Because the MM field may only have one digit, it would copy in the single digit month for the first 9 months of the year and a “/” character. Such as 1/, 2/, 3/, etc. So I had to add that into the equation.

     

    I also needed to create a text column in the library called Calendar Quarter.

    Now I have the tools needed to get the work done with SharePoint Designer 2013.

    For the fields below, I used the Extract Substring End & Start functions in the workflow.

    2

    3

    So now I have the two character month value in the Fiscal Month Conversion variable and the four character year in the FiscalYear variable.

    To calculate the fiscal quarter, I had to take the resultant value in the fiscal month variable and convert it to the fiscal text quarter variable with end result values of Q1, Q2, Q3, Q4. As shown below. Just to be clear, I learned that not all company fiscal years are the same. For this company, their fiscal years are as follows: Q1:  Jan 1-March 31, Q2: April 1-June 30, Q3: July 1-September 30, Q4: Oct 1-December 31

    4

     

    The last part is to simply concatenate the two variables “Fiscal Text Quarter” and “Fiscal Year” together into the Calendar Quarter field with the Set Field in Current Item function.

    56

     

    The end result will give you this value in the Calendar Quarter column! (Sorry I couldn’t show you the whole list…legal issues)

    7



  • NT Authentication Failure: Utilizing Custom Web Services External to SharePoint

    Scene:
    We have a server hosting a webservice (we’ll call this the Sending Webservice) that takes documents/pdfs, etc. and uploads them to a specific site collection in SharePoint. On the SharePoint WFE, there is also a custom webservice (we’ll call this the Receiving Webservice) running in the same app pool as the hosted SharePoint site collection that receives the authentication, connection and then uploads the document into the document library.

    This is where the fun begins. Our security department is running the domain controllers on Server 2003. OOTB Server 2003 comes with an ancient credentialing capability called LanMan or Lan Manager. I am no security guru, but I’ll explain it’s purpose as best I can.

    From my understanding, the Lan Manager attaches a hash to every account that has a password tied to it within active directory. This hash could be used by applications to pass an encrypted password through to A.D. and obtain a successful authentication. The encryption methods this hash could handle were for both LM and NTLMv1 (version 1) encryption. Both versions were incredibly weak and easy to hack if someone was sniffing the network traffic watching for this handshake. LM and NTLMv1 have also been retired for over 10 years. You do not need to fear if you are running domain controllers with Server 2008 or greater as Lan Manager is all but gone from this environment now. But by default, Lan Manager and the LM authentication is enabled when running Active Directory in Server 2003. However, you can easily disable these.

    Back to the story. Our security department decided to do several things to increase security. Change the user ids to unrecognizable letter and number combonations and force all passwords to 15 characters with all the standard character requirements that would go with it. They didn’t mention Lan Man had already been disabled the week earlier. Now here is the catch…once the Lan Manager was disabled, the hash remained tied to the account. So the service account that was using an “LM” or “LMNTv1” encryption hash still worked just fine. But once a password or ID on the account was changed, the hash that was originally tied to the account in Active Directory dropped off and the account failed to authenticate with these encryption methods.

    A good test to see if this is ever the case in another situation, change the service account to an ID that did not have the password changed after Lan Man was disabled. If it’s a “Lan Man” issue, the account will work just fine. Then change the password and expect to see the functionality break again.

    As we had already recieved the updated account credentials and they were not working, we reverted the account back to the original service account login ID, display name and password credentials just to make sure we wern’t missing anything on the code side that was holding the credentials somewhere we did not realize…but even with the reverted credentials it was failing.

    When we look at the log files, it gives the error that the credentials supplied were invalid. That’s pretty much it. This did not anser the really important questions. Such as, “what is requesting the credential (AD or SharePoint)? What is sending the credential (recieving or sending webservices)? And who is saying it is denied (SharePoint or AD)?” Now, we know the account worked just fine if you used the account via the front end of SharePoint so we know it is not end user error. You could log into a site just fine. But using the Sending wservice caused the problem. Is SharePoint recieving a hashed password and rejecting it automatically? or was was it capturing the authentication and sending it re-encrypted to Active Directory and then AD was denying it because of that or was SharePoint simply passing the authentication through (sending on whatever it recieves).

    A lot of questions that we couldn’t answer without some help. So, we got our Network guru on a call and he started to watch the traffic coming from and going out from the SharePoint WFE server (I believe with a program called NetMon). We triggered the Sending webservice. There we saw the credential go to the WFE and pass on to the domain controller. Then the DC rejection packet. Both the Sharepoint server and the AD server rejected the credential.

    This answered, who was doing the rejecting (both SharePoint and AD). Which also means that the credentials were not geting reencrypted by the SharePoint server since SharePoint itself was rejecting the credential.

    After many hours trying to isolate the issue, we learned that there was another Sending webservice that was using this custom SharePoint Receiving webservice and it was working just fine. :-/

    So in the end, when an account is requesting to get authenticated to SharePoint, SharePoint does nothing but try to authenticate it itself. And if it cannot, SP will pass on the credentials (as it received them) to Active Directory for authentication. AD says yeay or nay, and sends that answer back to SharePoint. SharePoint then simply allows or denies based on that response from AD.

    Below is a depiction of the the path security takes:
    1) Client pc requests action from the hosted webservice.
    2) Webservice passes it’s service account credentials to the custom webservice hosted on the SharePoint server.
    3) SharePoint receives credentials and attempts to authenticate the account itself. If it cannot, it passes on the credentials to the Domain Controller for authentication within Active Directory (AD).
    4) AD approves/denies credential and sends response back to SharePoint WFE requester.
    5) SharePoint notifies original sender of the response.
    The Credentialing Process
    NTLM Path
    Useful information:
    http://www.techrepublic.com/article/tech-tip-lock-down-systems-by-disabling-lm-authentication/5287636



  • Office Appliations Opening Files Twice

    This is quite short and fairly simple, but I felt it needed to be noted because it stumped me for over a day and I don’t want it to happen again…

    I found that when some users select a document to open from within a SharePoint 2010 library, it would bring the document/spreadsheet up twice. I was unable to find a viable solution for this within SharePoint. Evidently, they were using the 64-bit version of Internet Explorer. Which is incompatible with a lot of sites outside of SharePoint as well. Using the 32-bit version of I.E. seemed to resolve this issue.



  • Open Documents in Client Applications

    Recently, I had a situation where end users were trying to open Office files in the new SharePoint 2010 environment we released to them for testing. They would try to open an xlsx spreadsheet and it would try to open in the browser. We didn’t want this functionality to work so we decided to turn on the feature called “Open Documents in Client Applications by Default” at the site collection level. Which was suppose to automatically disable that functionality.

     

    Unfortunately, it didn’t work. I learned shortly after, that there is a setting (that enabling the said feature was suppose to take care of and didn’t switch for me)…

    I’m sure like most of you, your environment(s) have a ton of sites and within each, tons of document libraries. Going through them each and manually setting this, would be out of the question. So I took the approach of PowerShell. It had performed miracles for me in the past, no reason it wouldn’t now. Or so I hoped. I’m no PS guru by any means. So I rely on Google to help me out. After some time I came across a couple scripts that looked like viable solutions. But they failed me. After a couple days of troubleshooting, I finally came up with a solution that actually worked…


    #Get the site collection...
    $site=Get-SPSite "http://servername/sitecollection"
    $web=$site.RootWeb


    #Define the list type within the sitecollection to change (SPDocumentLibrary)
    #This changes the setting for all matching list types within the sitecollection
    foreach ($list in ($web.Lists | ? {$_ -is [Microsoft.SharePoint.SPDocumentLibrary]}))


    #define the setting and the value to modify.
    #You can define as many different settings as you like
    {
    $list.DefaultItemOpen = "PreferClient”
    $list.Update()
    }
    $web.Dispose()

    _________________________________________________________________________________________

    I did have a situation where even after this was adjusted, a small handful of end users were still getting SharePoint to open Excel documents in the browser somehow. Their configuration was Windows 7 (both 64 & x86), IE 8 and Office 2010. I couldn't entirely isolate what the cause was of the issue, but what I do know is that these guys were always tinkering with their OS/applications/registry. So who knows what caused it. The fix below seemed to resolve it:

    On each of SP servers in the farm go to:

    1. c$program filescommon filesMicrosoft SharedWeb Server Extensions14TEMPLATEXML.
    2.  Locate the file called “serverfilesExcelServer.xml”
    3. Comment out the xlsx file extension reference
    4. Perform an IISReset on each of the servers you made the change on.




  • SharePoint – Custom Master Page Seen as V4 When Built for V3

    Just so it is clearly understood, SharePoint 2010 came with the capability to display the master pages of migrated 2007 sites. This capability therefore differentiates the master pages of the two environments into two categories: V3 (SharePoint 2007/WSS 3.0) and V4 (SharePoint 2010/Foundations).

    So I came across an issue where we began migrating our existing 2007 environment to SharePoint 2010. We had a custom master page and theme feature rolled out on the 2007 environment. We needed to maintain this master page post migration so we could ease the user base to the 2010 (v4) environment one site collection at a time.

    When we deployed the 2007 master page and theme to the 2010 environment, it wasn’t showing up as an optional master page in the drop down of the Master Page Settings. This was because SharePoint considered it a version 4 (v4) master page instead of a version 3 (v3) master page.

    Of course, I searched online for two days with no viable solution. Evidently, the answer was VERY simple (as it usually is)…

    1)      Go to Site Settings > Modify All Site Settings

    2)      Under Galleries, go to Master pages and page layouts.

     

     

    3) Locate your custom master page and edit it’s properties.

     

    4) Change the check box from 4 to 3.

    5)      Save

    It should now be available as an optional master page in the Master Page Settings of Look and Feel.



  • Create a Custom Content Type Lookup Field that Pulls Data From External (SQL) Custom List.

    Creating a custom content type that is a lookup to an external content list is not as straight forward as all that. But once you have completely built one, it’s significantly easier than resources on the web would have you believe. The only downside is there are A LOT of steps.

    We had a scenario where we had several Custom Content Types (CCT). Each of the CCTs needed a custom site column look up field that pulled data from a SQL database. A fairly common business requirement but quite tasking to complete from scratch. As I’m a big proponent of visual aids, I’ll include as many pictures as possible and keep the instructions to a minimum but clear.

    The task consists of the following steps:

    • Secure Store Service configuration
    • SP Designer – Create (ECT), read item & read List & custom external list
    • BDC Service App (permissions)
    • Add to a custom list to test the ECT functionality
    • Verify the new list in the hosted site.
    • Create Custom Site Column

    Secure Store Service (SSS)

    In this situation, we need to pass a security account to the SQL database. This is where the SSS comes into play.

    NOTE: Before beginning, this is assuming you already have an account configured on the SQL side that has the needed access to the data you are querying for.
    Access the SSS in Central Administration > Application Management > Secure Store Service
    1) Create a secure store item by selecting ‘New’.

    2)     In the next window complete the fields below and select ‘next’.

    3) The next steps are titles for the columns for storing the credentials. NOTE: that this is not the credentials themselves.

    4) You can setup custom permissions for a specific set of users to be able to manage this SS item. Select OK.

     

    5)     Enable the checkbox and hit the “Set” button to add the credentials.

     

    6)     Enter the owner of the credentials and the account credentials. Hit OK.

     

    The SS account has now been created and is ready to be utilized in the setup of the External Content Type we will create in SharePoint Designer.

    *Note: If this is to be setup on a farm, the Secure Store Service will need to be enabled on each server hosting a web interface that will utilize this account.

     

    External Content Type Creation & Configuration

    Now we are going to create the connection that pulls the data from the database into an external SharePoint list.

    1)     Open SharePoint and connect to the site you want to build out your connection to.

    2)     Select ‘External Content Types’ from the Site Navigation window and choose the ‘External Content Type’ button in the top left corner of the ribbon.

    3)  Complete the following fields: Name & Display Name. Then click the link ‘Click here to discover external data sources and define operations’.

    4)    On the next screen, select “Add Connection”. Since our connection is SQL based, we will choose SQL from the drop down.

    5)     Next, complete all the fields, and refer back to what your original SSS Identity was for the Secure Store App ID:

    6)    Now we drill into the database for the table we need to pull data from & create the needed ECT Operations. We do this by locating the respective table, R+Click and choose the needed option. I only need READ access, so I will choose Read of both Item & List options. I will choose Item first.

    *note: At minimum a Read Item and a Read List must be created and configured before this ECT can be saved or an external list created.

    7)    The Operation and Display names are inconsequential to the purpose of my needed functionality. So we will leave these as they are. Click Next.

    8)     The next window shows the Input parameters. We only need a single column to map as an identifier for the columns we are tying to the list we are building. Hit ‘Next’.

    9)     Here we need to make sure we map at least one identifier and check all the fields that we wish to include in the Custom list. Click Finish.

    NOTE: The display name field is the name that will get translated to the column of the external table. So make sure this is a legible name for you & your end users instead of a naming standard for your SQL data column.

    10)     Next is to create the Read List. R+Click on the column again and choose the Read List Operation.

     

    11)     Again, hit Next. Here you can include filter parameters if you like. At this time, I do not need them. There are plenty of good resources that can walk you through this process. Hit Next again.

     

    12)     Here in the Return Parameter Configuration we will do the same thing we did in the Read Item Configuration. You also have an option called “Show in Picker”. Checking this will enable the specified column to be displayed in our lookup column (picker content type). We will want to check this for the needed columns pulled from the table. Hit Finish.

    13)     We now need to create the Custom List on the SP site. Hit the Create Lists & Forms button in the ribbon and follow the instructions for the list configuration.

    1)     Once the external list is created, you can view it on the site you connected this to. I would suggest gonig to the site and make sure the data is pulling in properly. At this point, you are now ready to create the custom lookup content type.

    2)     In the site, go to Site Actions > Site Settings > under Galleries, select Site columns.

    3)     Select “Create” at the top of the page.  From here the rest is pretty self-explanatory. Choose the “Lookup” information type and the lower column settings allow you to select the newly created external content list and from all associated columns from within that list. Once you save the custom Site Column, you can now add it to any library/list you wish.

    *NOTE: There is another option at the base of the lookup content type that allows you to add additional columns that are associated with the external content list. This feature, though offered, is unfortunately NOT supported and does not work. Even if you try to build it out programmatically.


    Links to some information I found useful in completing my task:

    http://msdn.microsoft.com/en-us/library/ee557243.aspx

    http://msdn.microsoft.com/en-us/library/ee558778.aspx

    http://answers.flyppdevportal.com/categories/sharepoint2010/sharepoint2010general.aspx?ID=7c47c583-ff7a-4e99-b221-9da1ee0c8517

    https://littletalk.wordpress.com/2011/08/18/external-content-type-an-error-occurred-while-retrieving-data-from-a-system-administrators-see-the-server-log-for-more-information/

    http://msdn.microsoft.com/en-us/library/ff798274.aspx

     



  • Customizing a SharePoint External Content List with Infopath

    Anyone trying to customize the boring layout of an external list in InfoPath that you made in SharePoint, is warmly greeted with the fun loving error: “An unknown error has occurred. The form template has been published to the server but it can only be opened in InfoPath Filler.”

    3

    The solution is far more simple than having to hard code anything like so many different sites claim. and I won’t go into the how-to’s for creating a Business Connectivity Service connection (BCS) or an External Content Type (ECT). As there are many other tutorials out on Google that will cover it well. A great resource is Clayton Cobb. He doesn’t fancy himself a coder so he actually speaks in a language I can understand. Links to his tutorials directly are the following: BCS & ECT 1 BCS & ECT 2 BCS & ECT 3

    To get right to the heart of the issue, let’s look at the error itself. “An unknown error has occurred. The form template has been published to the server but it can only be opened in InfoPath Filler.” It doesn’t tell you much at all. Except that it doesn’t like it when you try to upload the InfoPath form (template.xsn). When we push that button in SharePoint Designer to convert the External List to an InfoPath form,

    1

    it creates a new folder called “Item”. Then adds the following files to it: displayifs.aspx, editifs.aspx, newifs.aspx & template.xsn. IT also sets the new .aspx files as the default views for the list and the template.xsn file holds all the InfoPath form data for the list.

    With that said, open SharePoint Designer in file view and navigate to the list folder containing the new files. Once there, and with the list already open in InfoPath, delete the template.xsn.

    4
    Now, magically, you can save your new InfoPath form without issue.
    Don’t do your happy dance just yet though! If you try to add/modify/view the items in SharePoint, you’ll get an error. Two things need to happen now.
    1) In InfoPath, create the corresponding views of the item views in SharePoint (New, Edit and Display). Then save the template.xsn file.

    2

    2) Modify the three new .aspx files in SharePoint Designer to point to the new InfoPath template.xsn file by highlighting the list web part and changing the value of FormLocation to the correct location. See the field in the top right corner of the pic below.

    5



    Then in the web part properties of each, set the view to match the corresponding file type.
    7

    Save each of the .aspx files and enjoy your new fully customized external content list form in all it’s glory. *BAM*