Alec Pojidaev's InfoPath Blog

Feb

Combining two datasources in XPath

This post is a result of solving real life problem for one of my coworkers. The task was to make a multiple choice from secondary datasource and then present this choice on the summary page as a concatenated string. Not too complex, right? The problem was that the multiple choice control was displaying titles while the stored values had to be IDs. So to display concatenated titles somewhere else you need to build query against your secondary datasource applying choices made at multiple choice control. If it would be SQL the solution is clear – it’s called INNER JOIN. The capital letters here doesn’t mean to indicate how great this INNER JOIN thing is nor to indicate that this INNER JOIN thing is kind of luxury unattainable for poor InfoPath developers. :) No, it’s just a SQL notation. So here is the formula I come up with during my attempt to lower the level of suffering of another fellow codeless programmer:

substring-after(xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM(“CompetitiveWorkloads”)/dataFields/harePointListItem_RW[contains(xdMath:Eval(xdMath:Eval(xdXDocument:get-DOM()/my:myFields/my:compworkload/my:competitive_workload, ‘concat(., “;”)’), “..”), ID)], ‘concat(“;”, Title)’), “..”), “;”)

Looking at this formula it’s not too hard to realize that another animal called the OUTER JOIN can be killed with the similar formula:

substring-after(xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM(“CompetitiveWorkloads”)/dataFields/harePointListItem_RW[not(contains(xdMath:Eval(xdMath:Eval(xdXDocument:get-DOM()/my:myFields/my:compworkload/my:competitive_workload, ‘concat(., “;”)’), “..”), ID))], ‘concat(“;”, Title)’), “..”), “;”)

There are two key elements of these formulas:
1) Using function “contains” to provide evaluation of a given ID belonging to the set of selected IDs
2) You have to start XPath for the multiple selection repeating field from xdXDocument:get-DOM() to reset current relative XPath.

The FORM
to try.

P.S. Here is another formula for INNER JOIN, this formula maybe more preferable because it’s simplier:
substring-after(xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM(“CompetitiveWorkloads”)/dataFields/harePointListItem_RW[xdXDocument:get-DOM()/my:myFields/my:compworkload/my:competitive_workload = ID], ‘concat(“;”, Title)’), “..”), “;”)

Happy codeless programming!

Categorized in browser forms, browser-enabled, eval, InfoPath, iterate and XPath
Tags: double eval, multiple choice

Aug

Multiple value control

5 Comments

Thanks to Ranjit Batra who chalenged me to create a control similar to what is exist in Lotus Notes.

This control
emulates multiple choice control that is not available in InfoPath. Enjoy!

Categorized in browser forms, browser-enabled, eval, InfoPath, SharePoint and XPath
Tags: double eval, multiple choice

Feb

How to promote InfoPath field to the SharePoint link type field

1 Comment

Special thanks: Karl D. Swartzendruber: cruelest boss of all

From time to time I’m getting into situations where InfoPath and/or SharePoint limitations don’t allow me to implement what I’ve been tasked to do. Like in that case when my cruel bosses were mercilessly cracking whip on me disregarding my whining about damn limitations preventing me to promote InfoPath field to the SharePoint link type field. As I was told, “Without beautiful icon (link type field can be represented as an icon, neat eh?) my users might fail to distinguish one type of announcement from another”.

That means it’s a hack time!

1. In InfoPath create richtext type field “MyIcon”. The value of the “MyIcon” field should look like:

http://yourlink , Description
Note these spaces from both sides of the comma.

2. Promote it to the new library column of the same name.

3. Delete the column you’ve just created.

4. Create link type column with “MyIcon” name.

Categorized in browser forms, browser-enabled, InfoPath, MOSS, SharePoint and templates
Tags: browser forms, browser-enabled, formulas, icon, InfoPath, repeating, templates, tips and tricks

Jan

Accessing rows in repeating group for browser enabled forms

46 Comments

There are not so many differences in versions of XPath between regular forms and web enabled ones. But inability to reference rows in web enabled forms by the index is one of the most annoying. Here are several XPath formulas useful when interacting with repeating tables:

1. The current row index expression:
count(preceding-sibling::*[local-name() = “MyRepeatingGroup”])
As you can see that formula successfully substitutes the position() function not available in web forms.

2. Accesing value of the previous row:
preceding-sibling::my:MyRepeatingGroup[count(preceding-sibling::my:MyRepeatingGroup) = count(current()/preceding-sibling::my:MyRepeatingGroup) – 1]/my:FieldToAccess

3. Implementation of the last() function:
my:MyRepeatingGroup[count(following-sibling::*[local-name() = “MyRepeatingGroup”]) = 0]/my:FieldToAccess

4. Count all rows that have duplicates:
count(my:MyRepeatingGroup[following-sibling::*/my:FieldToAccess = my:FieldToAccess])

Form to try.

5. The other technique demonstrates idea of how to wright into repeating group by providing index of the row and a value:
Form to try.

Happy codeless programming!

Categorized in browser forms, browser-enabled, InfoPath, iterate, templates and XPath
Tags: autonumber, browser forms, browser-enabled, formulas, InfoPath, iterate, loop, repeating, Rules, table, templates, tips and tricks, XPath

Oct

Work (business) days calculations with C#

15 Comments

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

Work days between 2 dates:

public static double GetBusinessDays(DateTime startD, DateTime endD)

{

double calcBusinessDays =

1 + ((endD-startD).TotalDays * 5 –

(startD.DayOfWeek-endD.DayOfWeek) * 2) / 7;

if (endD.DayOfWeek == DayOfWeek.Saturday) calcBusinessDays –;

if (startD.DayOfWeek == DayOfWeek.Sunday) calcBusinessDays –;

return calcBusinessDays;

}

Note 1: Some changes are made to comply with the changes made by admins of StackOverflow.com site. They did removed unnecessary cast to int.

Note 2: Another useful input from the same site: Just to remark, with the ‘1+’ it assumes start of first day until end of last day, without the ‘1+’ it assumes end of first day until end of last day. Took me a while to figure that out, since I was assuming start of first day until start of last day, which made more sense to me. – Jeffry van de Vuurst Oct 23 ’15 at 9:21

Note 3: The code is relying on the fact that by default in C# (int)DayOfWeek.Saturday is 6 and (int)DayOfWeek.Sunday is 0. This option might be different in your region so the code should be refactored accordingly. Looks like both “ifs” could be removed if Monday would be 0 (if week starts Monday). Need to be tested though.

Add work days to a start date:

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

double businessDays = 2300;

int DoW = (int)startD.DayOfWeek;

double temp = businessDays + DoW + 1;

if (DoW != 0) temp –;

DateTime calcendD = startD.AddDays(

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

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

Categorized in C#
Tags: business days, C#, weekdays, work days

Sep

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

9 Comments

Bookmark that post

Special thanks: Karl D. Swartzendruber

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”.
The Form to test. Web browser forms OK.
Deleting single row from the repeating field.

Here is form
that is doing exactly this. The way it was created (renaiming hack) is similar to the previous form. There are several limitations assosioated with this approach. First is it supports single repeating field not repeating group. Another “feature” you have to consider before you decide to use this form is unchecking checkbox at single row will delete all rows with the same value (might be useful in certain scenarios). Also because of Multiple selection check box this form can be made browser enabled in SP2010 only.

Categorized in browser forms, browser-enabled, InfoPath, templates and XPath
Tags: browser forms, browser-enabled, formulas, InfoPath, repeating table, Rules, templates, tips and tricks, XPath

Aug

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

43 Comments

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:

(xdMath:Max(../../my:CheckG/my:rStarter) = ../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.

Form with more complex behaivior (excludes rows of the same category only, also not tested in SP2010)

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? But that’s fine, hopefully people familiar with volatile variables concept and multithreading are not reading posts about InfoPath :) )

Categorized in browser forms, browser-enabled, InfoPath, MOSS, templates and XPath
Tags: browser forms, browser-enabled, check box, formulas, InfoPath, radio button, repeating, Rules, table, templates, tips and tricks, XPath

Jul

How to check if your InfoPath form is “dirty”? (Tracking changes)

3 Comments

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 have find interesting the way I used conditional expression to parameterize the output message.

Here is form to test
Form
with more advanced XPath (InitialState can be at any position)

Categorized in browser forms, browser-enabled, InfoPath, templates and XPath
Tags: browser forms, browser-enabled, expression box, formulas, InfoPath, Rules, templates, tips and tricks, XPath

Jun

Alec Pojidaev's InfoPath Blog
Stuff published for no reason (cool codeless infopath solutions mostly)

Combining two datasources in XPath

Multiple value control

How to promote InfoPath field to the SharePoint link type field

Accessing rows in repeating group for browser enabled forms

Work (business) days calculations with C#

Work days between 2 dates:

Add work days to a start date:

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

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

How to check if your InfoPath form is “dirty”? (Tracking changes)

Infopath codeless programming (walkthrough) 3

Most underused/unknown SharePoint feature

Blog Stats

Archives

Categories

Alec Pojidaev's InfoPath Blog Stuff published for no reason (cool codeless infopath solutions mostly)

Work days between 2 dates:

Add work days to a start date:

Blog Stats

Archives

Categories

Alec Pojidaev's InfoPath Blog
Stuff published for no reason (cool codeless infopath solutions mostly)