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