• Tag Archives Designer
  • 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.


    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.


    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.



    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



    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.



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


  • 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.”


    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,


    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.

    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) 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.


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

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