Category Archives: InfoPath

Sharepoint / InfoPath: Use a Multiple Line of Text field in a Calculated Column

I stumbled upon this by accident. Try to do this by adding a multiple line of text field to a calculated column in SharePoint and it will tell you that it cannot be done. But it can with the use of InfoPath and some trickery:

For this example I will call the field that will be the multiple line of text field “Holder”.

1. Create a single line of text field and name it what you will be naming your multiple line of text field.

2. Create you calculated column and add the field you just create to it.

3. Open the form in InfoPath.

4. Delete the Single Line of Text field you just create and the publish.

5. In InfoPath create a Multiple Line of Plain Text field and name it EXACTLY the same as your old Single Line of Text field and publish once more.

You are now done and whatever you put in your multiple line of text field will show up in your Calculated Column.

Check out this post to see an example of this process in action

 

Also posted in SharePoint | Tagged , , | 2 Comments

Sharepoint / Sharepoint Desginer / InfoPath: Create a dynamic link that opens a particular InfoPath view in a SharePoint dialogue box with it’s own edit icon

I found that I needed to have 2 views in SharePoint that both had an Edit Icon but each icon need to point to a different InfoPath view. I wanted the item to pop up in the dialogue box just like the normal edit icon did.  I took a look at how SharePoint was encoding theirs and was able to mimic it. Below you will find the solution.

The first part of this solution requires us to trick SharePoint into allowing the use of a Multiple Line of Text field in a Calculated column. We need to do this because the URL string you will be using is way over the 255 character limit of a single line of text field and SharePoint will not let you just add a Multiple Line of Text field to Calculated column.

1. Create a single line of text field (I called mine “Link Holder“).

2. Create a Calculated column (I named mine Edit Item)
– Add the Single Line of Text field (Link Holder)  to the Formula
– Set the data type as Number (for some reason this is the only way to make it read as html)
– Click Ok

3. In SharePoint Designer make a workflow that puts together the html you would like using the Set Field to value action.

Ex. Set Link Holder to <a href=”[Encoded Absolute URL]/../Item/editifs.aspx?DefaultView=ViewName&amp;ID=[ID]” onclick=”EditItemWithCheckoutAlert(event, ‘[Encoded Absolute URL]/../Item/editifs.aspx?DefaultView=ViewName&amp;ID=[ID]‘, ‘0’, ”, ”, ‘1’);return false;” target=”_self”><img border=”0″ alt=”Edit” src=”/_layouts/images/edititem.gif”></a>

In the above example I am pulling the main part of the url from the Encoded Absolute URL field and also the current item’s ID. Notice that this happens twice. I have inserted the name of my view where it says ViewName. Please note that the view name is case sensitive.

Once you publish this workflow and run it on your item you will see the edit icon shows up in your Calculated Column field and, when clicked, it opens a dialogue to the correct View of your InfoPath form.

 

Also posted in SharePoint, SharePoint Designer | Tagged , , , | Leave a comment

Infopath: Get User Profile Data into your form

I needed to populate a drop down list with all the departments in my university (this is a field that is part of the user profile data.) Through my searching I stumbled through the UserProfileService.asmx and could not easily figure it out. I finally stumbled on a post from the famous Laura Rogers that explained a super easy way to access the information I wanted by pulling in the User Information List. I will put the pertinent parts below and will give a link to her blog post that covers it more in depth.

I am going to assume that you know how to create data connection.
In this case you will create a data connection to a SharePoint library or list then click next.

You will put in the url to your site collection (https://www.yoursite.edu/siteCollectionName/.) Then click next

You will then see list of all the lists or libraries you can choose from. Scroll all the way to the bottom and you should see User Information List. Choose that and click next.

You can then choose the field you would like to bring in. I just needed the Department field and checked that checkbox.
There ya have it.

There is on caveat that is worth noting: “If they are a new user, they will not show in this list until they’ve actually visited a site in that site collection.” I believe that by giving permissions to All Authenticated Users covered this for me as every department was present and I know for a fact that someone from every department has not visited the site collection, though that may not be true as I have no way of testing that theory.

Here is the link to Laura’s post about this:
 User Information within InfoPath Forms

Also posted in SharePoint | Tagged , , , | Leave a comment

InfoPath: Calculate Age based on Date of Birth date picker.

I needed to calculate the age of a person to run some validation rules on my form. This is how I did it:

  • Create a Date Picker field (I named mine Birth Date) and place it on the form.
  • Create a Number field (I named mine Age.) You don’t have to place it on the form, though you can initially to make sure it is working.
  • Put the following rules on the Birth Date field:

Rule 1

Condition:
None

Action:
Set a field’s Value

Field:
Age

Value (click the fx button):
(substring(today(), 1, 4) – substring(Birth Date, 1, 4)) – 1 * (translate(substring(today(), 6, 5), “-“, “”) < translate(substring(Birth Date, 6, 5), “-“, “”))

* Birth Date is the name of your Date picker field.

 

Rule 2

Condition:

Age  is less than 0  ( should look like Age>0 when you hit ok)

Action:

Set a field’s Value

Field:

Age

Value:

0

 

There ya have it. This should give you the correct calculation.

Also posted in SharePoint | Tagged , , , | 2 Comments

InfoPath: Validate that form has an attachment

I have found that I often need to make sure that people attach a file before they can submit. InfoPath will not let you run a rule on attachment fields however there is a way around it:

  • Create a single line of text field to use as the validation field (name is something like Attachment Validation). This field is also going to be the title text for the attachments field.
  • Place it on your form and remove the borders and shading.
    – go into text box properties under the Display tab and set it to Read Only
    –  then go to the Data tab and set its Default Value to the word “Attachment“.
  • Place a validation rule on this field. The condition should be The Expression and the expression should read like this:
    count(../my:Attachments/attachmentURL) = 0

The expression basically counts the number of attachments and if it equals 0 then it will put the red dashed line around the word Attachment.

 

I found the solution on Max Morrow’s site:
http://maxmorrow.wordpress.com/2012/05/03/infopath-2010-validating-the-attachments-field/

Also posted in SharePoint | Tagged , , , | Leave a comment

SharePoint – InfoPath: Validate attachment URL for special characters

I did a ton of searching for this and could only find 1 solution. With this solution, if you have special characters in your file name and hit submit a warning pops up and tells you that  you have special characters in your file name. It will not let you submit the form until that is fixed.

The solution is as follows:

Create an InfoPath form the way you usually would.

In SharePoint, place an InfoPath form web part into a web part page.

Add a Content Editor web part and place the following JavaScript inside the html part of it.

You will need to replace the bolded id below with your own. That text is the id for your submit button. In most browsers you can right click on the submit button and click inspect element to find it. It is will read something like this: <input style=”text-align:center” onclick=”return (Button.OnClick(this, event));” onfocus=”return (Button.OnFocus(this, event));” id=”ctl…

———————————————————————————————————————————————————————————————————-

<script type=”text/javascript” src=”https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js”></script>
<script type=”text/javascript”>
$(document).ready(function(){
$(“input[id=’ctl00_m_g_ed36b5b8_a7c8_45d8_a19a_ac7017df0aff_FormControl0_V1_I1_B3‘]”).live(“mousedown”, function()
{

spans = $(‘li[scriptclass=”SharePointFileAttachmentContainer”]’).find(‘span[id^=”ctl00_m”]’).find(‘span’);
spans.each(function(){
// alert( $(this).text() );
specialcharecter($(this).text());

});
function specialcharecter(FileName)
{
var iChars = “!`@#$%^&*()+=’;,/{}|\”:<>?~”;
// var data = document.getElementById(“txtCallSign”).value;
var data = FileName;

for (var i = 0; i < data.length; i++)
{
if (iChars.indexOf(data.charAt(i)) != -1)
{
alert (“Attached file has special characters (ex. & # $ %). \nThese are not allowed.“);
// document.getElementById(“txtCallSign”).value = “”;
return false;
}
}
}

});
});
</script>​​​​

———————————————————————————————————————————————————————————————————-

The bolded area above next to “alert” is the text that will display upon submit if there are special characters in the file name.

Here is the link to the answered post in which I found the solution:
http://social.msdn.microsoft.com/Forums/sharepoint/pt-BR/c9ebf3fc-062c-4ad6-9d0d-fde440e0edff/sharepoint-info-path-form-attachment-file-name-should-not-contain-special-character-

Also posted in SharePoint | Tagged , , | Leave a comment

Infopath: Limit decimal places to display for a calculation

We were adding up monetary fields and found that the calculation was giving us to many decimal places. Below you will find the solution to get them back to 2:


Round to 2 decimal places:  
round((calculation) * 100) / 100


Round to 3 decimal places: 
round((calculation) * 1000) / 1000


Example:

Formula: round((1.5555*42)*100) / 100

result: 65.33

Also posted in SharePoint | Tagged , , | 4 Comments

InfoPath/SharePoint/SharePoint Designer: Allow someone to only post once.

This is really a method that can be used to also run rules based on whether a user has submitted something before.

Create a single line of text field (Username), make a workflow to set the that field to the username of the person who created the item. Then create a text field (Filter) that you then set its Default Value to a filtered secondary data connection (copy of the original) using the Username as the filter. Then make a rule that looks to see if the Filtered text box is empty or not. If it is empty then the user has note filled out the form before. If it is not empty then the user has filled out the form before.

See Below of step-by-step directions:

Step 1
Create single line of text field (I named mine Username).

Step 2
Create a workflow in SharePoint Designer that will set the Username field to Created By: Login Name
workflow action ex: Set Username to Current Item:Created By.

Step 3
In InfoPath add the list as a secondary data connection
– make sure Username as one of the fields to grab

Step 5
In InfoPath create new single line of text field (I called mine Filter)
right click and go to properties or double click your new field
– in Default Value section next to Value: click fx.
– Click Insert Field or Group
– Click Show advanced view
– Under Fields you will see a drop down. Choose the data connection to Secondary one you created above
– Choose dateFields>d:SharePointListItme_RW>Username
– Click Filter Data… at the bottom
– Click Add
– In the first field click Select a field or group…
– Change the Data source to Main
– Choose choose dateFields>d:SharePointListItme_RW>Username
– Click OK
– For the last field choose select field or group
– Choose Username
– Click OK
Click OK
– you should see Username = Username
– Click OK
– Click OK
– You should see a formula that says Username[Username=.]
– Click OK
– Beside Value you should see a formula that says Username[Username=.]
– Click OK

Step 5
Create a rule who’s condition is set to see if the Filter field is empty or not.

Also posted in SharePoint, SharePoint Designer | Tagged , , , | Leave a comment

InfoPath: Run a validation rule on a people picker field (sort of).

You cannot run a validation rule on a people picker field, however this is what you can do to achieve the same effect:

You can create a single line text field.

Set the default value of the text box to be populated by the people picker field (Display Name, Username (AccountID), or Account Type).

Place the text field into your form

Put a formatting rule that hides this field.

Put a validation rule on that text box.

I also put a section in that had an explanation that had a formatting rule that had the same condition as the validation rule.

Also posted in SharePoint | Tagged , , | 3 Comments

SharePoint/Infopath: link to a particular view in an InfoPath form in SharePoint by the url.

All you need to do is add &DefaultView=ViewName to the end or the url.

Ex: https://sharepoint.edu/site/Lists/ListName/Item/editifs.aspx?ID=1&IsDlg=2&DefaultView=Approval

Also posted in SharePoint | Tagged , , | 4 Comments