InfoPath date calculations and date comparisons without code

If you have the unfortunate task of doing some date calculations within InfoPath, you may have already found out the fun news:  InfoPath only gives you TWO functions dealing with date and time:  addDays (which lets you add a certain number of days to a date) or addSeconds (which lets you add seconds to a dateTime field).  This can be a little difficult when you intend on doing some calculations like adding a month to a specific date or subtracting a few days from a date to determine another day.  Here's a few ways you can do date calculations:

1.  Use addDays - the addDays function looks like this:  addDays(insert_a_date_field_here,"type_the_number_of_days_to_add_here").  An example would look like:  addDays(StartDate,"1") and this would add a single day to the Start Date field.  Key thing to note - you can add negative numbers here to subtract a certain number of days.  This can be useful in conditional formatting or data validation for greater than/less than comparisons (e.g. If DueDate field is less than [addDays(StartDate, "10")] then give the error message "Your due date is too soon; please set it for more than 10 days after the start date" in the data validation tooltip.

2.  Use addSeconds in the same manner as addDays - this can be done for time issues like turning an item in by 5pm on a certain day.  The reason this might be necessary is that, often, when calculating dates, the time assumed for a day is 12:00 AM on that day...so if you want a realistic time like 8am, you will need to use the addSeconds to give you a few hours...you'll just have to use a calculator to figure out how many seconds to add for so many hours/minutes.

3.  Adding a month or year to a date:  this is tricky but doable without code.  What you have to do is a little behind-the-scenes work with the date field.  Here's the steps:

1. Have your two date fields, the first one is where the user picks a day (we'll call it UserDate), the second one is for that day plus 2 months (DatePlus2).
2. Create 4 text fields:  temporaryDate, month, day, and year (you'll hide all these but I'm showing you them in the example)
3. Use a rule to set the temporary date to the value of the UserDate.
4. You'll notice that the format that it sets that field in looks like this:  YYYY-MM-DD
5. Now you get to use the dreaded substring-before and substring-after functions to pull out the year, month, and days.  The idea of substring is that a "string" is a bunch of letters and numbers (like a sentence, word, whatever) and that you are getting a portion or less than the whole thing (like subtracting out a certain part of the string...substring).  There are different types of substring-ing like looking before or after a certain symbol (like a slash or dash in our case) or substring a certain number of letters in (e.g. you have a Social Security number field that always starts with SS#...you could say you want to substring starting at the 4th letter and get the next 11 characters to get all the numbers and dashes for a social security number).

6. Use a rule on the temporaryDate field to set the YEAR field to the following formula:  substring-before((click Insert Field and choose temporaryDate), "-")...this will get all the characters before the first dash that it finds (so all the year numbers).
7. Add another action to the rule on temporaryDate to set the MONTH field as the following formula:  substring-before(substring-after((insert temporaryDate here), "-"),"-")  This looks weird, but what it's going to do is substring after the dash (which grabs the MM-DD) and then substrings before the dash of what you grabbed (so it grabs MM since it's the only thing before the dash).
8. Add another action to the rule on temporaryDate to set the DAY field as substring-after(substring-after((insert temporaryDate here), "-"),"-")  This looks very similar to the Month one but it substrings after the first dash (so MM-DD is what we have) and then it substrings AFTER the dash this time (so it gets DD)
9. Phew, almost done, you've now got the numbers in the month, day, and year fields for your day...all you need to do now is add to the month, day, or year field.  The only problem is this:  InfoPath thinks these fields are TEXT fields, not numbers, so if you try to just add 1 or 2 to the Month field, it's gonna laugh at you (because, as far as it knows, you're trying to add 2 to AA and it thinks you don't know how math works...seriously, it'll laugh at you).  So, here's what you do:
10. Add another action to the rule on temporaryDate to set the MONTH field to this formula:  value((insert MONTH field here))+2 (or however many months you want to add to that month field).  The value function will tell infopath that what you have in the MONTH field is really a number - so you can then do some math with it like adding a few months.  The only hard part here is when you get toward the end of the year...you'll need to have another rule that checks to see if the month is greater than 12 and, if it is, subtract 12 from it but also add 1 to the year.  Again, that's only if you are adding months...if you add to the year, then you won't have to worry about figuring out the month :)
11. The final step:  add another action to the rule for temporaryDate to set the DatePlus2 field to this formula:  concat((insert YEAR field),"-",(insert MONTH field),"-",(insert DAY field)).
12. Below are a couple of images showing the 3 rules I used here to first set the calculated date, second and third check to see if the month is over 12 and do different things based on what the number is.  The reason I had 2 rules for that is that, if you subtract 12 from 13, you get 1...which is correct; however, InfoPath will want you to have "01" in the Month field instead of "1"...so I had to change what I say when I'm setting the calculated date field

SharePoint KPI Best Practices

So, I'm hoping people will see this in google and give me some ideas. I'm trying to figure out some best practices for KPIs in MOSS (SharePoint) 2007. Thus far, it seems best to explain to those new to the topic why I'm looking for this.

A KPI stands for Key Performance Indicator, which basically means this is a report-at-a-glance of something specific in SharePoint. The more you track in SharePoint, the more you can generate these quick pictures that highlight (high-level) how areas are performing and give management a way to analyze the health of their departments, teams, divisions, etc. An example of this would be a task list:

1. You have a task list in SharePoint that shows every task that your team is working on right now and in the past.
2. You are given the responsibility of figuring out how many tasks are over a week behind schedule for every team member. Now, you could do this by sticking a bunch of the data in Excel and then using filtering and conditional formatting to get your data...but that data would be flat/static and you'd have to do a little extra work to make it dynamic...even then, it's not dynamic without a refresh. So, you want to make a KPI.
3. To make a KPI, you use the Site Actions->Create menu and create a KPI list (this is only available in MOSS 2007 and with the "Office SharePoint Server Enterprise Site features" is activated on your site). Form there, you can create a KPI based off of a SharePoint list and then set it to the URL of a view and then either count the number of items or a percentage of items exist there (even add additional filters for percentages if there's a number column involved). You then set whether a higher or lower number is better and what numbers change from an unsatisfactory to "ok" to satisfactory. This KPI list can then be opened directly or else placed on a web-part page in a dashboard style.

This is a common scenario and you may need to report quite a bit of information off of the exact same data. So, let's look at some performance best practices for KPIs:

1. SharePoint KPIs are based off of list views which means that the KPI will only be as good as your view. Views can be optimized by remembering the 3 F's: Folders, file indexes, and filters. Create folders to separate data (but don't let everyone create folders, make them yourself). File indexes involve taking a specific/key column in your list and having SharePoint make a copy of just that column so that it can reference it when searching the list (in views and, by extension, KPIs). You can turn on an index for a column by going to the List Settings and looking under the Column Section for "Indexed Columns"...just remember not to index more than 1 or 2 columns. Finally, filters will help your views keep from retrieving too much data at once (which would then be interpreted by KPIs). Use filters FIRST on indexed columns and THEN on non-indexed columns. Make sure that no view, personal or public, would retrieve more than 2000 items at once. This isn't a hard-and-fast requirement but it's best practice for SP 2007 (2010 raises this limit significantly as it handles your views/queries a little differently). Also, even if you only see items in batches of 5, you can still be pulling a massive amount of data (you just have it set to display them in batches of a certain number)...so verify the number of items returned when you switch to a certain view.

2. KPIs can be generated through a KPI list and also through calculated columns/data view web parts (see www.endusersharepoint.com for more details on the latter). Where this is displayed is a key performance difference: if you can display a KPI inside a list (as a column) then you will be able to maximize performance. The reason that this can maximize performance is that each KPI queries a list view...which means that 10 KPIs that use the same view but show slightly different information will re-query the same view 10 times. If you have a column built into the list then you can include a KPI as a part of a specific view (maybe a reporting view).

3. If a KPI collection is going to be on a special page (maybe a dashboard for management), then there are some factors to think about: the number of KPIs, the views associated with those, and any custom graphics that we're trying to display; all of these will be performance hits on your web part page. To minimize these, try to actually use a list form web part and show a specific view of information in a list whenever possible. This can be a specific view of all late tasks grouped by person instead of a KPI that reports that information (so you don't get a pretty icon stating whether or not there's too many tasks behind schedule, but you'll see a number and be able to see corresponding data too). If we have more than 10-20 KPIs on a page then it takes maybe 30 seconds to display the data correctly (though some of that will be through inefficiencies in our own organization), so use KPIs to seriously high-level ONLY the information that managers need to see in a web part page and RESTRICT ACCESS to this page to the specific manager or manager SharePoint Permission group so that the page isn't constantly refreshed.

I'm welcome to receive other suggestions so that someone can reference this information as we discover more about KPIs. If any of this information is incorrect, please comment and explain why. Hope this helps!

Filtering by a group in a SharePoint Person or Group column

I recently stumbled across this in a technet site here and modified it for my own use and to help anyone who wants it. I was trying to put multiple people in a SharePoint group, use a person or group column to categorize an item to that group, and then have it automatically filter based on which group you are in when you look at the item. I found the solution but it requires you to create the view that filters the Person or Group column to [Me] but then open the view in SharePoint designer and copy-and-paste the following (I know, it's scary) code. This means that you CANNOT add anything else to that view or attempt to modify it in any way after you change the code in SharePoint Designer because it will break that view and you have to do this all over again. This isn't a great solution because of that limitation but it will do if you absolutely HAVE to do this. Here are the steps:

1. Create the STANDARD view and filter to [Me] on the Person or Group column that contains the sharepoint group in which a person exists.
2. Open this view in SharePoint Designer…if it ever says you do not have permission to do something, simply click ‘Cancel’ and everything will be fine.
3. Click on the CODE or the SPLIT view near the bottom left...you want to be able to see the code itself.
4. If you are using SP 2010, skip to step 5. Look in the code for the phrase ‘ListViewXml=’ tag and then scroll a LONG ways toward the right (after all the ‘FieldRef Name=blahblah_x0020_blahblah’) and look for ‘<Where’ and it ends a little ways after that with ‘/Where>’
5. Look at the ‘FieldRef Name=’ and note what it is for this filter.
6. For 2007 users, replace the <Where thru the /Where> with the following:
<Where><Or><Membership Type="CurrentUserGroups"><FieldRef Name="REPLACEME"/></Membership><Eq><FieldRef Name="REPLACEME"/><Value Type="Integer"><UserID Type="Integer"/></Value></Eq></Or></Where>
(NOTE: DON’T MESS WITH ANY OTHER > and < in this!!)

For 2010 users, the split view in SharePoint designer will show you the encoded versions of these statements inside the <View> tag (it's SOOO pretty compared to all this garbage), so, you will want to fix them by copying and pasting this instead:
<Where><Or><Membership Type="CurrentUserGroups"><FieldRef Name="REPLACEME"/></Membership><Eq><FieldRef Name="REPLACEME"/><Value Type="Integer"><UserID Type="Integer"/></Value></Eq></Or></Where>

7. NOTE: TO USE CALENDAR VIEWS – For 2007 users, look inside the &lt;Where&gt; statement to find /DateRangesOverlap and then it has a couple of &gt’s and &lt’s and then it says EQ followed by the FieldRef Name of the key column you are using. Replace &lt;EQ through /EQ&gt; with the following:
&lt;Or&gt;&lt;Membership Type="CurrentUserGroups"&gt;&lt;FieldRef Name="REPLACEME"/&gt;&lt;/Membership&gt;&lt;Eq&gt;&lt;FieldRef Name="REPLACEME"/&gt;&lt;Value Type="Integer"&gt;&lt;UserID Type="Integer"/&gt;&lt;/Value&gt;&lt;/Eq&gt;&lt;/Or&gt;

Again, for 2010 users, it should look all HTMLified and pretty so you will want to copy and paste this instead:
<Or><Membership Type="CurrentUserGroups"><FieldRef Name="REPLACEME"/></Membership><Eq><FieldRef Name="REPLACEME"/><Value Type="Integer"><UserID Type="Integer"/></Value></Eq></Or>

8. Replace the REPLACEME’s with the fieldref name that it originally said. For instance, on one form with the Ministry teams the field name was Singing_x0020_Team (the _x0020_ is a space in the field name…and the fieldref name is the name the column was given originally…doesn’t matter if you change it after that, the fieldref name remains).

There ya go. Use only in emergencies ^_^.

Why am I here?

I have been working lately with WSS3.0/MOSS 2007 (SharePoint 2007) and InfoPath 2007 to accomplish business processes. Everywhere I turn, I run into a roadblock and the only solutions seem to be programming-oriented. I may not be a genius but I realize neither I nor many of my colleagues have the skill or access to fix these problems (like filtering a data connection in a browser-based infopath form).

I am creating this blog to help vent some of that frustration and to provide training and tutorials for those interested in learning different Office products and what they can do without special programming/code. If you happen to stumble upon this site in its infancy, please let me know of problems you have encountered that you wish would be answered without using (or using the bare minimum of) CSS, HTML, VBA, etc.