Work (business) days calculations with C#

If you think it’s a decent solution vote for it! :)

 

Work days between 2 dates:

DateTime startDate = new DateTime(2000, 5, 28);

DateTime endDate = new DateTime(2009, 3, 22);

double calcBusinessDays =

1 + ((endDate – startDate).TotalDays * 5

- (startDate.DayOfWeek – endDate.DayOfWeek) * 2) / 7;

if ((int)endDate.DayOfWeek == 6) calcBusinessDays --;

if ((int)startDate.DayOfWeek == 0) calcBusinessDays --;

 

Add work days to a start date:

DateTime startDate = new DateTime(2000, 5, 28);

double businessDays = 2300;

int DoW = (int)startDate.DayOfWeek;

double temp = businessDays + DoW ;

if (DoW != 0) temp --;

DateTime calcEndDate = startDate.AddDays(

Math.Floor(temp / 5)*2 – DoW + temp

- 2* Convert.ToInt32(temp % 5 == 0)) ;

How to remove initial row in repeating tables (or delete all rows) with rules only

Bookmark that post

Special thanks: Karl D. Swartzendruber

Form to test. Web browser forms OK.

First of all you have to place your repeating group into the non repeating one. So your field structure should be looking like that:

Field structure

Then as you can see the simple rule f=cleaningGroup will delete all rows. Now we are getting to the point why it never been implemented before. As soon as you will try to assign any value to a group InfoPath will yell at you with the message: “You must select a field. Groups do not have values and therefore cannot be assigned to by this action.” Let’s prove that at least “cannot be assigned” part of that statement is false.

It’s time for some rule breaking.

Now we will screw up the group (“f” in our case) by giving it a temporary name. Create a field at the same level as your group formerly named “f”. Name that field … guess what? Right, with the name “f”. Now InfoPath UI won’t be objecting when you’ll try to create button rule f=cleaningGroup. Delete field “f”. Rename your group back to its original name: “f”.

A mutually exclusive check box (radio button) in a repeating table/section

Bookmark that post

Clean XPath only solution. Browser forms OK.
The FORM to try.

Create following field/group structure:

Assign 0 as initial value to rStarter field. Assign following XPath expression as inital value to the ChBx field:

(../my:rStarter = xdMath:Max(../../my:CheckG/my:rStarter)) and (../my:rStarter != 0)

Create rule at ChBx field.
This rule has 2 actions:

• Assign rStarter to 2 * (. != “false”)
• Assign rStarter to 1 * (. != “false”)

Place control you like (check box or radio button) on the layout. For check box default “Value when cleared” to false and “Value when checked” to true. For radio button limit amount of choices to 1 and set “Value when selected” to true.

Thats it. Happy codeless programming!

P.S. Actually that form looks as potentially a good example to demonstrate field updating concepts like “push” and “pull” and how they are interrelate with each other. In particulary it would be interesting to explain why the sequence of operators like i=2; i=1; which have no sense in any language can be useful thing in Infopath. (Kind of a statement, eh? My only hope here is that people familiar with volatile variables concept and multithreading are not reading posts about InfoPath)

How to check if your InfoPath form is “dirty”?

Bookmark that post

1) Create field called InitialState
2) At open event assign InitialState field to the following Xpath expression:

.. Yes, it’s two dots :)

3) At the submit rule compare InitialState field with the following expression assuming it’s a rule condition at a button:
starts-with(., my:InitialState)
Make sure the InitialState field is a very last node in the myFields group fields list.
I also believe some people might find interesting the way I used conditional expression to parameterize output message.

Here is form to test

Form with more advanced XPath (InitialState can be at any position)

Infopath codeless programming (walkthrough) 3

Other parts: 1, 2.
Bookmark that post

I hope this will be the last publication about codeless programming in InfoPath. In the first two parts I demonstrated few examples where we can see implementations of ‘while’ and ‘if’ like operators. The only area remained uncovered is block of code reusable by multiple controls (analog of procedure/function). Hopefully that approach will be able to address quite frequent complains about absence of ability to copy set of actions/rules across similar controls in InfoPath UI. The essential element of that approach is hidden field with shared set of actions/rules. Form to download

Happy codeless programming!

Most underused/unknown SharePoint feature

You can tag any element on your portal with the comment. The comment becomes visible for everybody who are at the same page and have Discussion tollbar open. It works as instant messaging too.

When you are at any page of your portal go to the IE explorer menu bar Vew->Explorer Bars->Discuss.

Use the button “Insert Discussion in the Document”. The server will highligt all places on the page you could tag with a comment.

Type in your comment.

That feature is available because Office Server Extentions are part of Sharepoint installation.

Link about Office Server Extentions and thier relation to SharePoint.

Multiple merge views in InfoPath form library

There is no out of the box way how to create custom view which supports merge functionality. But such minor obstacles shouldn’t stop you from creating as many merge views in your library as you want.

1) Click “Create new view” link in your form library
2) Navigate to the “Start from an existing view” section (and we going to do so to not just one more time to ensure that merge view is not presented among proposed choices)
3) Right click on any of the choices and copy shortcut to clipboard
4) Open NotePad (or any other editor) and paste
5) Open existing merge view and click “Modify this view” link
6) Copy part of the hyperlink in your browser which is in between ‘View=%7B’ and ‘%7D&Source’
7) In NotePad replace part from ‘View=’ to the end with information from your clipboard
8) Copy resulting link into addres bar of the new browser window and press “go to” button.
9) Enjoy your new and shiny merge supporting view :)

Infopath codeless programming (walkthrough) 2

Other parts: 1, 3.
Bookmark that post

Updated: 3 more forms added at the bottom: “work days calculation (excluding weekends and holidays)”, “day of the week calculation” and “Days since form was created”.

Special thanks: Karl D. Swartzendruber

So what about so called “codeless programming” in InfoPath? As we all probably know, InfoPath can performed only 16 calulations without user interaction. But what if your business rules require you to iterate through 17 repeating elements? You will get a message box from InfoPath with phrase “The rules or code may be causing an infinite loop”. Yes, in InfoPath 16 is a very big number and 17 is considered infinite. To you and I this might seem stupid and arbitrarily restrictive, but Microsoft’s wisdom is greater than ours. Perhaps they know that 17th iteration would give us developers too much power. It would break the fine balance of Microsoft applications, make other development tools obsolete, and become unbearable for non InfoPath developers. But let’s drop the light irony and just accept that InfoPath already has more codeless programming power than mere mortals should ever need.

Today’s challenge is to find the difference in days between 2 datepicker controls disregarding the absence of datediff() function in InfoPath. We are going to show that the 16 iterations limit just can’t stop the true codeless programmer.

Disclaimer: During developing cycle author has been having in mind “finite automates” principles as well as primitive binary search.

Anyway, lets get to the business. Date Difference Calculation. Form to download.

Our recipy is the following: We will need 3 fields. 2 of them are date type fields with datepicker controls (startDate, endDate) and one (dayDifference) is a text type field represented by a simple textbox. All rules have exactly 1 action.

startDate rule:

condition expession: ../my:endDate != ""
field: dayDifference
string value: 0;4096

endDate rule:

condition expession: ../my:startDate != ""
field: dayDifference
string value: 0;4096

dayDifference rules:

MainRule:

condition expession:
xdDate:AddDays(../my:startDate, substring-before(., ";")) != ../my:endDate and contains(., ";")
field: dayDifference
xPath expression: concat(substring-before(., ";") + substring-after(., ";") * ((translate(xdDate:AddDays(../my:startDate, substring-before(., ";")), "-", "") < translate(../my:endDate, "-", "")) * 2 – 1), ";", substring-after(., ";") div 2)

finalize rule:

condition expession: contains(., ";")
field: dayDifference
xPath expression: substring-before(., ";")

Your form is ready!

What else can be done by applying same principles:

Here is form that shows amount of days since it was created
Form that shows current day of the week and day of the week from Date Picker control.
The tricky one: Work days calculation (business days)

That’s it. Happy codeless programming!

Posted under tags: Infopath, Date Picker Control

Infopath codeless programming (walkthrough) 1

Special thanks: Karl D. Swartzendruber and all participants of following discussion:
http://www.infopathdev.com/forums/p/10098/35969.aspx#35969

Other parts: 2, 3.
Bookmark that post

Let me guess what might have brought you to that blog page about codeless programming in InfoPath. Any of you who developed at least one sophisticated InfoPath form sooner or later have been realized that writing forms with real complex behavior is impossible without some sort of coding in C# or VB.NET. So developers have no choice but to deal with mixed developing environment like Visual Studio, managing form security levels, security certificates and so on. But there are people who like to keep things simple. At least I do. And that’s why probably you are reading that page too:)

Anyway, lets get to the business. How to iterate an Infopath Repeating table?

Here is our scenario: we have a repeating group on the form which will be populated with wonderful (but often overlooked) Contact Selector Control with some user information. The control provides us all necessary information about user except his email.

But wait, that is outrageous! The email was the only reason why we decided to use that now not so awesome control! Well… at least we have access to the web service which able to return user email in exchange of account name. There are countless blogs describing how to utilize UserProfileService to get info of a single user in Contact Selector. Here I’ll try to demonstrate how to deal with multiple selection in Contact Selector. So now our task is clear – we have to construct a string acceptable in Email address field iterating through repeating group and submitting query to the web service on each cycle of iteration.

Let’s go.

The first things first – here using-the-contact-selector-control you will find detailed explanation how to utilize Contact Selector Control. The resulting data structure for the control in main data source should look like this

The Control also requires secondary data source

Add text field named HlinkToEmail. That field will hold result of our computations. Resulting Main Data Source:

When your new shiny control starts providing you users from your network that means we are ready for the web service part. Here we’ll use SharePoint GetUserProfileByName web method. You will need SharePoint Services running somewhere on your network. That part is pretty straightforward. Add secondary web service receive connection. Your link in connection settings should look like

http://<youservername>/_vti_bin/UserProfileService.asmx.

The results of that part:

“Programming” part

Place a button on the layout. At Rules and Merge tab create rule named setfCounter. Press “Set Conditon”. In the first area choose “The expression”. Here is string to paste in second area:

count(my:Person)>0

The only action will assign initial value to the HlinkToEmail field. Press fx button at new action form. Check “Edit XPath (advanced)” checkbox. To avoid too detailed instructions of choosing functions and form elements just copy paste the following expression:

xdMath:Eval(xdMath:Eval(my:Person, ‘concat(my:AccountId, "|")’), "..")

At main data source pane double click at HlinkToEmail field. At Rules and Merge tab create rule named fConterRule. It’s a conditional rule. In the Set Condition dialog in the first area: Choose The expression. Then paste this expression into second area:

contains(., "|")

The resulting set of actions:

The rule will consist of 3 actions. The fist action will be setting AccountName field of data source named GetUserProfileByName to the portion of HlinkToEmail field. Check “Edit XPath (advanced)” checkbox. Paste Xpath expression:

substring-before(., "|")

Next is self-explanatory action. Add action -> Query using data connection -> Choose GetUserProfileByName.

Add the last action to set HlinkToEmail field to the new value by assigning following XPath expression:

concat(substring-after(., "|"), ";", xdXDocument:GetDOM("GetUserProfileByName")/dfs:myFields/dfs:dataFields/s0:GetUserProfileByNameResponse/s0:GetUserProfileByNameResult/s0:PropertyData/s0:Values/s0:ValueData/s0:Value[../../../s0:Name = "WorkEmail"])

We done with actions of that rule. Make sure that check box “Stop processing rules then this rule finishes” is checked.

Add the last rule right after fConterRule. In Set Condition dialog choose The expression. In expression area copy paste:

substring-before(., ";") = "" and contains(.,";")

And finally, set the HlinkToEmail to the following XPath expression:

substring-after(., ";")

That’s it.

P.S. Now you know how to iterate and read repeating structures. But what if you need to write? Well, InfoPath doesn’t provide UI to parameterize repeating structures are assigned to new values. Still here you will find a workaround which overcomes this limitation.

Posted under tags: MOSS Contact Selector Control Infopath Sharepoint