Archive

Archive for the ‘SharePoint’ Category

Inserting multiple list items at once into a SharePoint list

October 16, 2013 14 comments

Couple of weeks ago I was asked if it is possible to insert multiple list items into a SharePoint list at once where some fields of inserted items are the same, like a header, and others are different?

The answer is “Yes it is”.

In my example I will use a custom list called “Inventory”. In my scenario this list is managed by internal IT where they track devices for each employee like phones, notebooks etc.  A guy from IT department needs to insert the “Employee Name” and “Employee Number” of new colleague for each entry in the list again and again the only difference is in the “Article”, “Quantity” and the “Serial Number”

Read more…

Advertisements
Categories: Infopath, NoCode, Office, SharePoint

SharePoint & Office 2013 App Development

February 13, 2013 1 comment

I was presenting two session about the new Apps Model which was introduced with Office and  SharePoint 2013. Here are the recordings of this session.
The sessions were held in German language.

Katapult.07: Die neue Office Plattform – Deep Dive – Developing Office Applications

Katapult.07: Die neue Office Plattform – Deep Dive – SharePoint 2013 App Development

Categories: Office, SharePoint Tags: ,

Working with Multi Value Choice Fields from SharePoint list in InfoPath

September 4, 2011 13 comments

One of my colleagues was working on a SharePoint solution where he has a master data list. This list has a choice column with email addresses. The choice column allows multiple selections.

His task was to create an InfoPath form which uses this master data list as a secondary data source. The end user should be able to select one of the list items from a drop down. After the user has selected an item and saved the InfoPath in a form library a workflow should run and send an email to all email addresses which are stored in the choice column. He was not allowed to use source code in his InfoPath form.

In this post I would like to show you how you can extract data from a choice field where a multiple selection is enabled. I will show you how you can transfer an array (multi value choice field) to a string, so you can use it in a SharePoint Designer (SPD) workflow. Let’s get started ….

Initial problem with multi value fields in InfoPath

The problem is as old as InfoPath is. The rules engine in InfoPath is not well designed to work with repeating tables/sections. InfoPath action “Set field’s value” can only copy single value fields and if you try to use this action on a repeating table, this will only copy the first element, all other elements will be ignored.

 

Initial environment

For my demonstration I’ve created a simple custom list in SharePoint and have added a choice field to my list. At the end my “master data” list which I will use as secondary data source in InfoPath looks as follows:

image

After adding this list to an InfoPath form template the secondary data source looks as displayed in the picture below. Small blue-white triangle near “:Value” shows us, that this field is a repeating field. As I mentioned above we won’t be able to extract all data from this field using InfoPath rule engine.

image

 

The Plan

As we now know, InfoPath is not good at handling repeating fields if you are not allowed to use source code. To achieve our goal we will

  • Create a single line text field “Responsibles_flat” on the “master data” list
    This field will be used in InfoPath because it can be handled well by InfoPath rule engine.
  • Copy selected values from choice field to “Responsible_flat”
    We will create a small SPD workflow to copy values from the choice field to “Responsible_flat” field automatically after “master data” item has been created, or updated.
  • Use “master data” list in InfoPath form as secondary data source
  • Clean up the value of “Responsibles_flat” field
    The raw value in the “Responsibles_flat” can’t be used directly as an email recipients value. We need to clean up the string so it can be used as email recipient in an email.

Solution

  • Create a single line text field “Responsibles_flat”
    image
  • Copy selected values from choice field to “Responsibles_flat”
    To copy the values we will create a workflow in SPD and configure it to run after a new item has been created, or updated in the “master data” list.

    Open your site, where “master data” list is stored, in SPD.
    Select “List and Libraries” from the left and click on the “master data” list to open list details.
    image

    After you’ve opened the details page you will see, that your ribbon has changed. Now you are able to create a workflow which will be attached to the list.

    To create a workflow click on: “List Workflow” button in the Ribbon.
    image

    Give your workflow a name, type a small description and click “OK”.
    image

    Our workflow will run after a new item has been created or an existing item has been updated. In our workflow we will update our item and copy values from our choice field into the text field, this will cause the workflow to run again and again and again.

    To avoid a never ending loop we will compare our value which we would like to copy to the text field with the value which is already there. If they are equal we will skip the copy activity. This will stop the loop.

    Our first activity will be to copy the value from our choice field in to a workflow variable. Click into “Step1” of your workflow and start type “Set”. SPD will inform you that there are 10 results available which match your text. Press “Enter” and you will see all workflow actions where you can select from.

    Select a workflow action named “Set workflow variable”.
    Your workflow should look as follows:
    image

    Click on “workflow variable” to create a new variable of type “String”.
    image

    Click on “value” and then on image icon to select the field which will be copied to the new workflow variable.
    Select the choice field from current item.
    Make sure you’ve selected “Choice, Comma Delimited” in “Return field as” field.
    image

    Now we will compare the value of our variable with the value of the field “Responsibles_flat”.
    Click belong your first action in your workflow and type “If”, press “Enter” and select the action “If current item field equals any value”.
    image

    Click on “field” and select “Responsibles_flat”.
    Click on “equals” and change it to “not equals”. Remember we want to copy the string only if "Responsible_flat” is not equals “Responsibles”.
    Click on “value” and then on image icon to select the variable which we’ve created before.
    image

    Your workflow should look as follows:
    image

    Click on “(Start typing or use ….” text to add an activity inside the if statement. Type “update” and press “Enter”. This will add  “Update item” action to your workflow. Click on “this list”, a dialog will appear.
    image

    Click on “Add” button and set the value for “Responsible_flat” field. Click “OK”
     
    image

    At the end your workflow should look like this one:
    image

    Click “Publish” in the ribbon so you can test your workflow. With actual settings the workflow will not run automatically on any changes to our “meta data” list. Make the first test manually on one of the items.
    After your workflow runs your item should look similar to this screenshot:
    image

    The columns “Responsibles” and “Responsibles_flat” look almost the same. The big difference is in the type of the column and how they can be used in InfoPath.

    After successful test go back to SPD and change the settings for your workflow. Set “Start Options” of your workflow as shown in this screen and publish your workflow again.
    image

    NOTE: If you are testing your workflows under “System Account” they wont start automatically. You will receive an exception in your SharePoint logs like follows. Test your workflows always with a user account. 
    Declarative workflows cannot automatically start if the triggering action was performed by System Account.

  • Use “master data” list as secondary data source in an InfoPath form
    Now we’ve prepared our “master data” list so we can use it in our InfoPath form. In our use case we will copy the value of “Responsibles_flat” to a field of our InfoPath form. Further it will be used in another workflow to send an email. I wont cover this workflow in this post but if you have questions on this topic feel free to contact me.

    My sample form contains only two fields in the Main data source:
    EmailRec – Textfield where we will store email addresses from item of “master data” list which was selected by user.
    dpRep – Dropdown field which is connected to the secondary data source.

    image

    The secondary data source should look as follows:
    image

    My drop down field “drpResp” stores “ID” of an item as value and displays “Title”. Make sure that you stores an “ID” as value, otherwise you want be able to select right email addresses  for “EmailRec”.
     image

    To fill our form field “EmailRec” with the value of “Responsible_flat” column we need to add a rule to our dropdown field.
    Select your dropdown field in the form and click on “Add Rule” in the ribbon. Select “This Field Changes” from the list and click on “Set a Field’s Value” action.
    image

    Select “EmailRec” field from main data source as “Field” and click on formula icon near “Value” field.
    image

    Now we need to select the right value from our secondary data source.  Our meta data list contains several items. To find the right value we need to filter the list.
    Click on “Insert Field or Group …” button and select “Responsible_flat” from your secondary data source. DO NOT CLICK “OK” YET.
    image

    Click “Filter Data…” This will allow us to get the right value from our “Meta Data” list. If we do not filter we will always receive the first item from the list.
    Click on “Add” button in the dialog.
    Select “ID” from the left drop down. This “ID” represents the “ID” of secondary data source.
    image

    Select “drpResp” from main data source for the right drop down.
    image

    Click “OK” on all opened dialogs and test your form in a preview. Select an item from the drop down and your “EmailRec” field should display a value from “Responsibles_flat” field.

  • Clean up the value of “Responsibles_flat” field
    As you can see, the values in “Responsibles_flat” are separated by a “,”. This might cause an error on your email server. Most of the servers needs “;” as delimiter between recipients. We need a small adjustment on our rule to make our string look well.

    Click on the “Set a field’s value EmailRec=…” action we’ve added before to modify it.
    image

    Click on the formula icon near “Value” field.
    SELECT existing formula text, so it appears to be marked and click on “Insert Function …” button.
    (You need to select the existing formula text so it will be automatically integrated into the function we will select in the next step.)
    image

    From function dialog select “Text” from the left list, and “translate” from the right and click “OK”.
    image

    As you see, the “translate” function needs three parameters.
    * text – text to be analyzed
    * find_chars – characters to be replaced
    * replace_chars – new characters to be inserted instead of find_chars

    After you’ve clicked on “OK” your formula should look as follows. Two parameters are missing.
    image

    Add missing parameters to the formula so it looks at the end as follows. Remember we want to replace “,” with “;”
    image

    Click on “Verify Formula” button to be sure you updated the formula in the right way.
    Click “OK” after your formula contains no errors.

    Verify your form in the “Preview” mode. The delimiter between email addresses is now “;” and will be accepted by our email server.

Categories: Infopath, SharePoint Tags: ,

Prevention of duplicate entries based on two or more columns in a SharePoint list

July 26, 2011 20 comments

SharePoint 2010 has a lot of improvements compared to SharePoint 2007. One of this improvements is the possibility to create unique constraints on a column in a SharePoint list.

image

This constraint is valid only for one column, but sometime you need to ensure that your unique constraint includes two or more columns.

The best way to create a multi column unique constraint is to implement an event receiver for the list. (http://msdn.microsoft.com/en-us/library/ff728093.aspx)

To be clear: An event receiver is the best way to avoid double entries in your list because it called every time your list item is changed regardless of the origin of the change.

To implement an event receiver you need to be familiar with visual studio, and be able to deploy source code solution to your site collection. An event receiver can be implemented as sandbox solution. In this case it can be deployed to Office365 also.

Solution for business users (no code)

In this post I would like to demonstrate a solution for an multi column unique constraint which can be implemented by a business user, who is not familiar with visual studio or is not allowed to deploy a sandbox solution to the site collection.

This approach will work only if the user enters data through the SharePoint UI. The approach I will demonstrate will not work for updates through source code, or through data sheet view. If you need to cover all possible input channels you need to implement an event receiver.

In our case we have a list “Cust List” with two columns “Title” and “Additional Title”. Our goal is to implement a unique constraint including this two columns to prevent a normal user to save duplicate entries in the list.

Please note that there is a column called “temp” we will need this column during this lab to raise an error if the constraint is violated.

image

 

SharePoint 2010 allows you to customize edit and display forms for list item through InfoPath.

  1. Edit form in Infopath
    Click on “Customize Form” in the Ribbon.

    image

  2. Add secondary data source to your form
    To be able to check for existing entry with same values for “Title” and “Additional Title” we need to check the list before saving a new entry to it. For this purpose we need a "secondary data source” pointing to “Cust List”.

    To add a secondary data source to your form click on “From SharePoint List” button in the “Data” tab:
    image

    Type in the URL to the SharePoint site where your list is stored and click next.
    image

    Select the list, in my case it is “Cust List”, from the listbox and click “next”
    image

    Select only “Title” and “Additional Title” from the list, so the amount of data we will get from the secondary data source is as low as possible. This will reduce the time to check for a double entry and avoid freezing of the form.
    We need to select “Title” and “Additional Title” because we will make a query on this field to retrieve duplicate entries. Click “next”
    image

    We do not need to store a copy of our data for offline scenario because our form is hosted in the browser, so leave the checkbox empty and click “next”
    image

    Give your data source a name and uncheck the checkbox for retrieving data on form load, otherwise all entries from your list will be loaded to your secondary data source while form opens. In a large list scenario it will cause a delay and bad performance of the form.
    MAKE SURE YOU UNCHECK THE CHECKBOX. Click “Finish”
    image

  3. Creating a rule to check for duplicate entry
    After we’ve created a secondary data source to our list, we need to create the rule which will query the list for duplicate entries and prevent user to save actual item if it violates the unique constraint.

    Reminder: our constraint includes “Title” and “Additional Title” columns/fields.

    Select “Title” field in your form and click “Manage Rules” in the ribbon
    image

    Click on “New” and select “Action” from the drop down:
    image

    Give your rule a name and set the condition for the rule to run only if “Title” and “Additional Title” are not blank:
    image

    Add “Set field’s value” action to your rule. Select “Title” field of your secondary data source. Default InfoPath displays fields from “main data source”. To select a field from secondary data source click on “Show advanced view”
    image

    Select your secondary data source from the drop down at the top of the dialog and select “Title” from the “queryFields” node. Click “OK”
    image

    Now you should be back to “Rule details” dialog. Click on the button near “Value:” text box.
    image

    Click on “Insert Field or Group” and select “Title” from “main data source”
    Click “OK” until you get back to “Rule details” dialog.
    Click “OK” on “Rule details” dialog.
    image

    Your rule should contain one action:
    image

    Create same “Set field’s value” action for “Additional Title” field. After you’ve added your second action your rule should look as follows
    image

    Add “Query for Data” Action to your rule and select your secondary data source.
    Click “OK”.
    image

    Your rule should look as follows:
    image

    We’ve created a rule which queries our list for duplicate entries regarding our input in fields “Title” and “Additional Title”.

    We need to raise an error in the form if a duplicate entry was found by our rule.

  4. Raise an error if a duplicate entry was found
    To raise an error in the form we will use the “temp” field/column. We will update our rule we’ve created in “3.” to populate the field with the ID of the first entry of our secondary data source after we made a query to it.

    If there are no duplicate entries (query to secondary data source returned no items), the “temp” field will remain empty, otherwise it will contain an ID of the duplicate entry. We will create a “Validation” rule on the temp field to prevent user to save item if “temp” field is not empty.

    Select “Title” in your form and add another action to the rule we’ve created in “3.” Add a “Set field’s value” action.
    Select “Temp” as field and “ID” from secondary data source as value. Make sure you select the “ID” field from “dataFields” node. 
    image

    image

    Your rule should look as follows:
    image

  5. Copy the rule to “Additional Title” Field
    We need to make sure that the check is done after the user changes “Title” or “Additional Title” field. For now, the check is only performed after the user changes the “Title” field.

    With InfoPath 2010 we can copy rules from one field to another. Thanks god, and program manager of InfoPath who made a decision to implement this feature Smile.

    Select “Title” field on your form and click on the “Copy Rule” icon at the top of the rule pane:
    image

    Select the “Additional Title” field and paste the Rule. You need to update the rule on “Additional Title” field after you’ve copied it.

    After you rule has been copied to the “Additional Title” field the first action “Set field’s value: Title = Title” has been changed. InfoPath is to smart and updates the rule automatically to the field where the rules is copied to. This happens to actions and conditions. 

    We still need to copy “Title” from our “main data source” to the “Title” – Query parameter of our secondary data source. InfoPath makes changes to the rule which will result in wrong query, and our check for duplicate entry will not work if the user changes “Additional Title” field.
    image

    Change first action, and condition so your rule looks as follows:
    image

  6. Complete error handling
    Now we are checking for duplicate entries on both fields “Title” and “Addition Title”, we copy an ID of the duplicate entry to our temp field if we find one. Now we need to raise an error, and prevent user saving the form if a duplicate entry exists.

    To achieve this goal we need to create a validation rule for “temp” field.

    Select “temp” field.
    Click “New” button in the “Rules” pane and add “validation” rule.
    Set up the condition and for your rule and type in the “Screen tip”:
    image

    Now we’ve created a validation rule and a user will not be able to save a form which will cause duplicate entries in the list. Now we need to inform the user what kind of error prevent saving the form.

    Put “calculated value” field at the top of our form where we will display an error message if necessary:
    image

    Type in the error message you would like to present to the user. Make sure you place it inside “…” otherwise InfoPath will try to analyze your text as formula and display an error:
    image

    To make sure to display the error message only if a duplicate entry was found create a Formatting rule for the “Calculated value” field:
    image

  7. Publish your form
    image
    Verify your form. Insert couple of entries to your list. This approach will work for small lists and for large lists.
    image
Categories: SharePoint Tags: , ,

Using Content Query WebPart to display Files from a specific folder

July 22, 2011 4 comments

Content Query Web Part (CQWP) is a powerful tool to aggregate and display content from different sources. It also allows you to modify the presentation of your content through XSLT transformation to almost any design you like.

In this post I would like to show you how you can display documents/items from a specific folder in your document library.

In my use case I have a document library with two folders “news” and “old”. 
Folder “News” contains two documents “News 1.txt” and “News 2.txt”. Folder “Old” contains also two files “Old 1.txt” and “Old 2.txt”. There is also a file “root document.txt” which is stored directly in the document library.

Our goal is to display only the documents from “news” folder on our portal landing page. At the end of this exercise we should see only “News 1.txt” and “News 2.txt” in the CQWP.

Default view of the document library for this exercise:

image

 

Adding CQWP web part to the landing page

First of all we need to place our CQWP on the landing page of our SharePoint site.

  1. Edit your page

    image

  2. Select “Insert” tab from the SharePoint Ribbon

    image

  3. Click on the “Web Part” and add Content Query Web Part

    If you can not find the “Content Query” Webpart in the “Content Rollup” Category make sure that the “SharePoint Server Publishing Infrastructure” feature in site collection features is activated.

    image

    Configuring the query for CQWP

    After we placed the CQWP on our landing page we need to set up the query to display particular content. 

    To set up the query edit the web part and expand the “Query” node in the settings panel. Select the document library where your document are stored. In my case I will use “Shared Documents” library.

    Leave the settings for “List Type” and “Content Type” as they are:

image

    After clicking “Apply” button you should see all documents and folders from your document library in your CQWP:

    image

    Now you need to setup “Additional Filters” to show only documents from “news” folder.

    Select “URL Path” from the drop down. Set “contains” as condition and “/news/” as value for your filter. (“news” is the name of the folder where our target documents are stored)

    image

    If you can not find “URL Path” in the drop down, make sure that “Content Type” is set to “<All Content Types>”

    Click “OK” or “Apply” and you will see only documents from the “news” folder:

    image

    Categories: SharePoint Tags:

    Create “By My Groups” – View for a custom List

    March 21, 2011 2 comments

    Couple of days ago a friend of mine asked me how he can create a view on a custom list to show all items assigned to groups where logged on user is member of.

    Because I am a developer the first answer was:
    “Create a web part and use CAML to query the list. Use the Membership Element and you are fine.”

    But he replied:
    “My customer is not familiar with Visual Studio and CAML. Is there a way to achieve this task without digging deep into SharePoint stuff?”

    The answer is Yes and this post describes how.

    Just to make sure there are no misunderstandings: We are talking about creating a filter. It has nothing to do with security!!

    If you look at a Task list you will find a view called “By My Groups”. This view is exactly what we are looking for, it displays tasks which are assigned to a group where logged on user is a member of. It does not matter if it is a SharePoint group, or an Active Directory group.

     

    1. Creating a custom content type

    It is a good idea to encapsulate your objects in a content type, so you can reuse them across you site and do not have to rebuild your lists again making mistakes and searching for typos in column names etc.

    You can find more information about Content Types here.

    Open “Site Actions” – “Site Settings” – “Site content types” –> Click “Create” button to create a new custom content type.

    image

    Provide a name for new Content Type and select “List Content Types” and “Task” as parent content type for your new content  type:

    image
    Update your new content type with columns you need. Be careful not to delete “Assigned To” column. We will need this column for our view later on. In this example I’ve deleted some columns and added a column “My Custom Column” to the new content type:

    image

    2. Using new content type I a list

    Now it’s time to create a list and use our new content type. “By My Groups” – view is automatically available in a task list. We will use this benefit for our custom content type
    instead of default task content type.

    Navigate to your site and click:
    “Site Actions” – “More Options” –> select the “Tasks” list template, type in a name for your list and click “Create”:

    image

    You will be automatically redirected to the default view of your new list. Click “List Settings” in the ribbon to add your new content type to your list.

    image

    Click on “Add from existing site content types” to add your new content type. If you do not see the link you have to enable content type management on your list. Click “Advanced settings” and select “Yes” in the “Content Type” section.

    image

    Select your new content type and click “Add” and “OK”

    image

    Delete built in content types which were added automatically to your list by SharePoint if you do not need them.

    Testing new list and new content Type

    To test our list and content type I’ve created some entries in my list. My test user “Max Mustermann” is a member of Active Directory group “ad group1” and a member of a SharePoint Group “SP Group 1”. Here is how the result of  “All Tasks” view looks like:

    image

    Switching to “By My Groups” view returns only items which are assigned to groups where “Max Mustermann” is member of:

    image

    So we’ve used built in benefits of the Task list template for our custom content type bypassing Visual Studio and working directly with CAML.

    Just to make sure there are no misunderstandings: We’ve created a filter. It has nothing to do with security!!

    Categories: SharePoint Tags: