REF: http://www.infopathdev.com/blogs/hilary/archive/2009/03/13/locked-schema-got-you-down.aspx
Locked Schema Got You Down?
Sometimes we want to create a new template off an existing schema. Microsoft has a great article Here , but for those of you who have already created a form off an existing schema, and just want to know how to unlock it, it can be a bit much to dredge through (also, they don't even mention the little .xsf hack I'm going to show you....). Fortunately, you have me, and I feel your pain.
The Problem
You saved a beloved form as source files, because its schema was perfect, and you want to base another form off of it. You create a new form template, chosing XML or Schema for the source
and you happily design away... until you want to add a field. Or change the name of an existing field. That's when you realize.... you're stuck. See those little locks on the data fields? And at the bottom of the Data Source Task Pane, Add a Field or Group is grayed out....
The Reason
If you read the article I've linked in the first paragraph, it says:
When you design a form that is based on an external schema, Microsoft Office InfoPath 2007, or Microsoft Office InfoPath 2003 assumes that the schema is finalized and therefore prevents any modification of the schema in the Data Source task pane.
So there you have it. InfoPath figured you were perfectly happy with the schema when you built the form off it.
One Solution
You can manually add elements to your schema. Save your form as source files and open myschema.xsd in a text editor.
Another Solution
But what if you just want it unlocked, so you can add fields like a normal old data source? For that, again, we need to save as source files. Open manifest.xsf with a text editor. Look for your schema file in the files element. You'll see a property named editability which has helpfully been set to 'none':
Set this property to 'full':
Save your changes, and open your template in design mode again:
All your fields will be fully editable.
The Usual Warnings Apply
Save a copy of your form someplace safe before manipulating form files. This is by no means a full discussion of potential pitfalls that can be encountered -- more information on xsf structure is available here. Happy unlocking!
Published Mar 13 2009, 05:32 PM by Hilary Stoupa
More:
REF: http://www.infopathdev.com/forums/p/9499/33583.aspx
Friday, April 9, 2010
Tuesday, March 23, 2010
InfoPath 2007 - Conditional SUM
REF: http://blogit.create.pt/blogs/miguelisidoro/archive/2008/08/02/InfoPath-2007-_2D00_-Conditional-SUM.aspx
This blog post will show you how to create an expression box in an InfoPath 2007 form whose value is based on the result of an conditional sum. Consider the following InfoPath form template:
The previous image shows a simple expense report. To support the introduction of the expense information, a Repeating Table control is used with three columns:
Expense - Expense Description. A simple Text Box control;
Value - Expense value. A simple Text Box control;
Expense Type - Drop-down List Box control that allows 4 expense types: Food, Land Travel, Air Travel and Parking.
Below the repeating table there are 5 expression boxes that show the total amount of the expense report and the total amount for each expense type. The total amount expression box is based on a simple sum expression and each of the expense type expression boxes are based on conditional sums filtered by the value of the expense type Drop-down List Box control. The expressions used for each expense type are the following:
Expression Box Expression XPath Expression
Expense Total sum(expensevalue) sum(my:accounting/my:expensevalue)
Food Expense Total sum(accounting[expensetype = "Food"]/expensevalue) sum(my:accounting[my:expensetype = "Food"]/my:expensevalue)
Land Travel Expense Total sum(accounting[expensetype = "Land Travel"]/expensevalue) sum(my:accounting[my:expensetype = "Land Travel"]/my:expensevalue)
Air Travel Expense Total sum(accounting[expensetype = "Air Travel"]/expensevalue) sum(my:accounting[my:expensetype = "Air Travel"]/my:expensevalue)
Parking Expense Total sum(accounting[expensetype = "Parking"]/expensevalue) sum(my:accounting[my:expensetype = "Parking"]/my:expensevalue)
As the previous table shows, the conditional sum expressions for each expense type are relatively simple and pretty straightforward for those who are familiarized with XPath, since XPath syntax is used for each expression. This comes as no surprise since the underlying data of the InfoPath form is stored in XML. The following image shows an expense report example filled with some sample values:
Posted: Saturday, August 02, 2008 3:52 PM by misidoro
This blog post will show you how to create an expression box in an InfoPath 2007 form whose value is based on the result of an conditional sum. Consider the following InfoPath form template:
The previous image shows a simple expense report. To support the introduction of the expense information, a Repeating Table control is used with three columns:
Expense - Expense Description. A simple Text Box control;
Value - Expense value. A simple Text Box control;
Expense Type - Drop-down List Box control that allows 4 expense types: Food, Land Travel, Air Travel and Parking.
Below the repeating table there are 5 expression boxes that show the total amount of the expense report and the total amount for each expense type. The total amount expression box is based on a simple sum expression and each of the expense type expression boxes are based on conditional sums filtered by the value of the expense type Drop-down List Box control. The expressions used for each expense type are the following:
Expression Box Expression XPath Expression
Expense Total sum(expensevalue) sum(my:accounting/my:expensevalue)
Food Expense Total sum(accounting[expensetype = "Food"]/expensevalue) sum(my:accounting[my:expensetype = "Food"]/my:expensevalue)
Land Travel Expense Total sum(accounting[expensetype = "Land Travel"]/expensevalue) sum(my:accounting[my:expensetype = "Land Travel"]/my:expensevalue)
Air Travel Expense Total sum(accounting[expensetype = "Air Travel"]/expensevalue) sum(my:accounting[my:expensetype = "Air Travel"]/my:expensevalue)
Parking Expense Total sum(accounting[expensetype = "Parking"]/expensevalue) sum(my:accounting[my:expensetype = "Parking"]/my:expensevalue)
As the previous table shows, the conditional sum expressions for each expense type are relatively simple and pretty straightforward for those who are familiarized with XPath, since XPath syntax is used for each expression. This comes as no surprise since the underlying data of the InfoPath form is stored in XML. The following image shows an expense report example filled with some sample values:
Posted: Saturday, August 02, 2008 3:52 PM by misidoro
Monday, March 22, 2010
SPD Workflows - Out of the Box
REF: http://www.mannsoftware.com/workflow/Wiki%20Pages/Out%20of%20the%20Box%20Actions%20and%20Conditions.aspx
SPD Workflows all come down to three things:
1. Steps
2. Conditions
3. Actions
That’s it. Master those three things and you’re golden.
Like anything else, the devil’s in the details, so here’s some links to existing information to help you build workflows in SPD:
Office Designer 2007 Overview from MSDN
Ted Pattison Screencast on Channel 9
Create a Workflow from Office Online
Those are all fairly basic, but a good start nonetheless. Here is some additional information:
Steps: A Step in an SPD workflow is a logical grouping of Actions and Conditions. They are used to provide structure
Conditions: Conditions are the circumstances that signal that a given Step in a workflow should execute. The default Conditions available in SPD are:
Condition
Description
Compare
FieldAllows you to specify a value for a column in the list this workflow is attached to. If the payload item has that value in the specified column, then this step will process.
Compare Any Data Source
Allows you to specify a set of values to be compared. Each value can contain either a hard-coded value or a lookup to another list on the site. See the section
Title Field Contains Keyword
Allows you to specify a value to look for in the Title column. If the current list item has that value in the title, then this step will process.
Modified in a Specific Date Span
Allows you to specify begin and end dates for when the list item was modified. If the current list item was modified within this span, then this step will process.
Modified by a Specific Person
Allows you to specify the name of a person. If that person modified the list item, this step will execute.
Created in a Specific Date Span
Same as Modified in a Specific Date Span but works off the list item creation date.
Created by a Specific Person
Same as Modified by a Specific Person but works off the original list item creator, not the last modifier.
The File Type Is a Specific Type
Allows you to specify a certain type of file. If the file payload matches this file extension, the step will process
The File Size in a Specific Range
Allows you to specify a range of kilobytes. If the file of Kilobytes payload falls within this range, the step will process.
Actions: An Action is what happens in a workflow step – they are the tasks that will be completed by the workflow as part of that step. The default Actions available in SPD are:
Action
Description
Add Time to Date
Allows you to work with date values and store the result in a variable. You can add (or subtract, by indicating a negative value) time (minutes, hours, days, months, or years) to a specified date and store the result in either an existing variable or in a new variable created in this step.
Assign a Form to a Group
Launches a custom wizard that allows you to easily build a form to collect information from users. The wizard is similar to the Variables Editor discussed in a moment. You can indicate which users are assigned the survey as part of the configuration for this action.
Assign a To-do Item
Launches a dialog box that allows you to specify the parameters for creating a simple task for a specified user.
Build Dynamic String
Configure this step to create a string value and assign it to a variable. The string value can contain values retrieved from the current workflow instance by using lookups. We discuss lookups later in this chapter. The variable used to store the string value can be either preexisting or created as part of this action.
Check In Item
Checks in an item so other users can edit it. You can specify Current Item or provide a column name and value to identify which item to check in. Also allows you to specify a check-in comment, which can be either hard-coded or set via a workflow lookup.
Check Out Item
Checks out an item so other users cannot edit it. You can specify Current Item or provide a column name and value to identify which item to check out.
Collect Data from a User
Creates and assigns a task to the specified user. Tasks are used to collect specific information from the assigned user or to have the user complete a process. The ID of the task is stored in a variable so that the information from that task is available later in the workflow via a workflow lookup. Configuring this action will launch the custom task wizard, which is similar to the Variables Editor discussed in a moment. This action is similar to the Assign a To-Do Item action, but allows you to collect information for later use instead of just creating a task.
Copy List Item
Creates an exact duplicate of an existing list item in a different list on the current site. You specify both the source and destination lists. You can specify Current Item or provide a column name and value to identify which item to copy.
Create List Item
Creates a new list item in any list on the current site. You specify the list as well as values for all necessary columns. Values can either be hard-coded or based on a workflow lookup.
Delete Item
Deletes an item from a list on the current site. You can specify Current Item or provide a column name and value to identify which item to delete.
Discard Check Out Item
Pretty self-explanatory, but this allows you to undo the checkout of an item. All changes to that item will be lost.
Do Calculation
Performs a simple calculation (plus, minus, multiply, divide, modulo) with two values, which you can either specify or base on a workflow lookup. The results of the calculation are stored in a variable. Typically, this variable would be one created specifically for this workflow.
Log to History List
Allows you to write an entry to the designated history list for this workflow instance. You can use either fixed text or a workflow lookup value (but not both) as the text to be written.
Pause for Duration
Allows you to specify that the workflow should suspend processing for a time period you specify (in days, hours, and minutes) when you configure this action.
Pause Until Date
Similar to the previous action, except that you configure a specific date on which to continue processing the workflow.
Send an Email
Sends an email when it is executed. The action can be customized to specify the recipient, CC, subject, and body of the message. Each of these fields can be either hard-coded or based on formulas, lookups, or workflow variables.
Set Content Approval Status
Allows you to specify the current status of the payload item. You can also specify comments, but keep in mind that those comments will be the same for every instance of this workflow unless you use lookups.
Set Field in Current Item
Allows you to set the value of a specific column in the payload item to a specified value. You can specify this value with either fixed text or a workflow lookup value (but not both).
Set Time Portion of Date/Time Field
Similar to the Add Time to Date action, except that you specify a specific value for hours and minutes (i.e., not a relative time—plus 5 minutes from when the action executes) and store the results in a variable.
Set Workflow Variable
Sets a variable for this workflow to a specified value. The value can be either hard-coded or set via a workflow lookup. You can reference a previously created variable or create a new one.
Stop Workflow
Causes the workflow to stop processing. You can specify text to be logged to the history list with either fixed text or a lookup.
Update List Item
Updates one or more existing columns in one or more lists. You can specify the list(s), the column(s), and the value(s). You can also specify Current Item to indicate the item that triggered the current workflow instance.
Wait for Field Change in Current Item
Pauses the workflow until the specified column for this item from the attached SharePoint list equals the value specified. The value can be hard-coded or based on a workflow lookup.
It is important to note that you can create custom Conditions and custom Actions and use them in your SPD workflow. We’ll cover that in a later posting.
There are a few things that you need to understand about a workflow developed with SPD:
1. They operate on a single List. When you start building the workflow, you pick the list to attach it to. Once that is set, there’s no changing it. You can’t take the workflow and attach it to another list or the same list type on another site
2. You can’t deploy SPD-developed workflows from one environment to another – or rather, you can’t deploy without recreating the workflow on the new environment
3. You can’t debug SPD workflows
4. You must have the .Net Framework 3.0 on the machine where you are running SPD
SPD Workflows all come down to three things:
1. Steps
2. Conditions
3. Actions
That’s it. Master those three things and you’re golden.
Like anything else, the devil’s in the details, so here’s some links to existing information to help you build workflows in SPD:
Office Designer 2007 Overview from MSDN
Ted Pattison Screencast on Channel 9
Create a Workflow from Office Online
Those are all fairly basic, but a good start nonetheless. Here is some additional information:
Steps: A Step in an SPD workflow is a logical grouping of Actions and Conditions. They are used to provide structure
Conditions: Conditions are the circumstances that signal that a given Step in a workflow should execute. The default Conditions available in SPD are:
Condition
Description
Compare
FieldAllows you to specify a value for a column in the list this workflow is attached to. If the payload item has that value in the specified column, then this step will process.
Compare Any Data Source
Allows you to specify a set of values to be compared. Each value can contain either a hard-coded value or a lookup to another list on the site. See the section
Title Field Contains Keyword
Allows you to specify a value to look for in the Title column. If the current list item has that value in the title, then this step will process.
Modified in a Specific Date Span
Allows you to specify begin and end dates for when the list item was modified. If the current list item was modified within this span, then this step will process.
Modified by a Specific Person
Allows you to specify the name of a person. If that person modified the list item, this step will execute.
Created in a Specific Date Span
Same as Modified in a Specific Date Span but works off the list item creation date.
Created by a Specific Person
Same as Modified by a Specific Person but works off the original list item creator, not the last modifier.
The File Type Is a Specific Type
Allows you to specify a certain type of file. If the file payload matches this file extension, the step will process
The File Size in a Specific Range
Allows you to specify a range of kilobytes. If the file of Kilobytes payload falls within this range, the step will process.
Actions: An Action is what happens in a workflow step – they are the tasks that will be completed by the workflow as part of that step. The default Actions available in SPD are:
Action
Description
Add Time to Date
Allows you to work with date values and store the result in a variable. You can add (or subtract, by indicating a negative value) time (minutes, hours, days, months, or years) to a specified date and store the result in either an existing variable or in a new variable created in this step.
Assign a Form to a Group
Launches a custom wizard that allows you to easily build a form to collect information from users. The wizard is similar to the Variables Editor discussed in a moment. You can indicate which users are assigned the survey as part of the configuration for this action.
Assign a To-do Item
Launches a dialog box that allows you to specify the parameters for creating a simple task for a specified user.
Build Dynamic String
Configure this step to create a string value and assign it to a variable. The string value can contain values retrieved from the current workflow instance by using lookups. We discuss lookups later in this chapter. The variable used to store the string value can be either preexisting or created as part of this action.
Check In Item
Checks in an item so other users can edit it. You can specify Current Item or provide a column name and value to identify which item to check in. Also allows you to specify a check-in comment, which can be either hard-coded or set via a workflow lookup.
Check Out Item
Checks out an item so other users cannot edit it. You can specify Current Item or provide a column name and value to identify which item to check out.
Collect Data from a User
Creates and assigns a task to the specified user. Tasks are used to collect specific information from the assigned user or to have the user complete a process. The ID of the task is stored in a variable so that the information from that task is available later in the workflow via a workflow lookup. Configuring this action will launch the custom task wizard, which is similar to the Variables Editor discussed in a moment. This action is similar to the Assign a To-Do Item action, but allows you to collect information for later use instead of just creating a task.
Copy List Item
Creates an exact duplicate of an existing list item in a different list on the current site. You specify both the source and destination lists. You can specify Current Item or provide a column name and value to identify which item to copy.
Create List Item
Creates a new list item in any list on the current site. You specify the list as well as values for all necessary columns. Values can either be hard-coded or based on a workflow lookup.
Delete Item
Deletes an item from a list on the current site. You can specify Current Item or provide a column name and value to identify which item to delete.
Discard Check Out Item
Pretty self-explanatory, but this allows you to undo the checkout of an item. All changes to that item will be lost.
Do Calculation
Performs a simple calculation (plus, minus, multiply, divide, modulo) with two values, which you can either specify or base on a workflow lookup. The results of the calculation are stored in a variable. Typically, this variable would be one created specifically for this workflow.
Log to History List
Allows you to write an entry to the designated history list for this workflow instance. You can use either fixed text or a workflow lookup value (but not both) as the text to be written.
Pause for Duration
Allows you to specify that the workflow should suspend processing for a time period you specify (in days, hours, and minutes) when you configure this action.
Pause Until Date
Similar to the previous action, except that you configure a specific date on which to continue processing the workflow.
Send an Email
Sends an email when it is executed. The action can be customized to specify the recipient, CC, subject, and body of the message. Each of these fields can be either hard-coded or based on formulas, lookups, or workflow variables.
Set Content Approval Status
Allows you to specify the current status of the payload item. You can also specify comments, but keep in mind that those comments will be the same for every instance of this workflow unless you use lookups.
Set Field in Current Item
Allows you to set the value of a specific column in the payload item to a specified value. You can specify this value with either fixed text or a workflow lookup value (but not both).
Set Time Portion of Date/Time Field
Similar to the Add Time to Date action, except that you specify a specific value for hours and minutes (i.e., not a relative time—plus 5 minutes from when the action executes) and store the results in a variable.
Set Workflow Variable
Sets a variable for this workflow to a specified value. The value can be either hard-coded or set via a workflow lookup. You can reference a previously created variable or create a new one.
Stop Workflow
Causes the workflow to stop processing. You can specify text to be logged to the history list with either fixed text or a lookup.
Update List Item
Updates one or more existing columns in one or more lists. You can specify the list(s), the column(s), and the value(s). You can also specify Current Item to indicate the item that triggered the current workflow instance.
Wait for Field Change in Current Item
Pauses the workflow until the specified column for this item from the attached SharePoint list equals the value specified. The value can be hard-coded or based on a workflow lookup.
It is important to note that you can create custom Conditions and custom Actions and use them in your SPD workflow. We’ll cover that in a later posting.
There are a few things that you need to understand about a workflow developed with SPD:
1. They operate on a single List. When you start building the workflow, you pick the list to attach it to. Once that is set, there’s no changing it. You can’t take the workflow and attach it to another list or the same list type on another site
2. You can’t deploy SPD-developed workflows from one environment to another – or rather, you can’t deploy without recreating the workflow on the new environment
3. You can’t debug SPD workflows
4. You must have the .Net Framework 3.0 on the machine where you are running SPD
Sunday, March 21, 2010
Calculate the difference between two date picker controls in InfoPath using rules and formulas - no code!
REF: http://www.bizsupportonline.net/infopath2007/calculate-date-difference-infopath-rules-formulas.htm
Use rules, conditions, and the number(), floor(), and substring() functions in formulas to calculate the difference between two date picker controls in InfoPath.
Problem
You have an InfoPath form template with two date picker controls and you would like to calculate the difference between the two date picker controls without writing code.
Solution
Use rules, conditions, and the number(), floor(), and substring() functions in formulas to calculate the difference between two date picker controls in InfoPath.
Discussion
You can accomplish this functionality as follows:
Design an InfoPath form template as shown in figure 1 with two Date Picker controls named startDate and endDate, and one Text Box control named difference.
Figure 1. InfoPath form template in Design mode.
The Main data source of the InfoPath form template should resemble the following figure:
Figure 2. The Main data source of the InfoPath form template.
Add the following Rule to the startDate field:
Action: Set a field's value
Field: difference
Value:
(number(substring(../my:endDate, 9, 2)) + floor((153 * (number(substring(../my:endDate, 6, 2)) + 12 * (floor((14 - number(substring(../my:endDate, 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) * 365 + floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 4) - floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 100) + floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 400) - 32045) - (number(substring(., 9, 2)) + floor((153 * (number(substring(., 6, 2)) + 12 * (floor((14 - number(substring(., 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) * 365 + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 4) - floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 100) + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 400) - 32045)
with the following Conditions on the Rule:
startDate is not blank and
endDate is not blank
Add a second Rule to the startDate field with the following settings:
Action: Set a field's value
Field: difference
Value: 0
with the following Conditions on the Rule:
startDate is blank or
endDate is blank
Add the following Rule to the endDate field:
Action: Set a field's value
Field: difference
Value:
(number(substring(., 9, 2)) + floor((153 * (number(substring(., 6, 2)) + 12 * (floor((14 - number(substring(., 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) * 365 + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 4) - floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 100) + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 400) - 32045) - (number(substring(../my:startDate, 9, 2)) + floor((153 * (number(substring(../my:startDate, 6, 2)) + 12 * (floor((14 - number(substring(../my:startDate, 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) * 365 + floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 4) - floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 100) + floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 400) - 32045)
with the following Conditions on the Rule:
startDate is not blank and
endDate is not blank
Add a second Rule to the endDate field with the following settings:
Action: Set a field's value
Field: difference
Value: 0
with the following Conditions on the Rule:
startDate is blank or
endDate is blank
Add the following Rule to the difference field:
Action: Set a field's value
Field: .
Value: 0
with the following Condition on the Rule:
difference does not match pattern Custom Pattern: -{0,1}\d+
You should now have a fully functional InfoPath form that will calculate the difference between the dates soon after you have entered valid dates. This solution also works for InfoPath 2003 form templates and InfoPath 2007 browser-enabled form templates.
Use rules, conditions, and the number(), floor(), and substring() functions in formulas to calculate the difference between two date picker controls in InfoPath.
Problem
You have an InfoPath form template with two date picker controls and you would like to calculate the difference between the two date picker controls without writing code.
Solution
Use rules, conditions, and the number(), floor(), and substring() functions in formulas to calculate the difference between two date picker controls in InfoPath.
Discussion
You can accomplish this functionality as follows:
Design an InfoPath form template as shown in figure 1 with two Date Picker controls named startDate and endDate, and one Text Box control named difference.
Figure 1. InfoPath form template in Design mode.
The Main data source of the InfoPath form template should resemble the following figure:
Figure 2. The Main data source of the InfoPath form template.
Add the following Rule to the startDate field:
Action: Set a field's value
Field: difference
Value:
(number(substring(../my:endDate, 9, 2)) + floor((153 * (number(substring(../my:endDate, 6, 2)) + 12 * (floor((14 - number(substring(../my:endDate, 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) * 365 + floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 4) - floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 100) + floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 400) - 32045) - (number(substring(., 9, 2)) + floor((153 * (number(substring(., 6, 2)) + 12 * (floor((14 - number(substring(., 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) * 365 + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 4) - floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 100) + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 400) - 32045)
with the following Conditions on the Rule:
startDate is not blank and
endDate is not blank
Add a second Rule to the startDate field with the following settings:
Action: Set a field's value
Field: difference
Value: 0
with the following Conditions on the Rule:
startDate is blank or
endDate is blank
Add the following Rule to the endDate field:
Action: Set a field's value
Field: difference
Value:
(number(substring(., 9, 2)) + floor((153 * (number(substring(., 6, 2)) + 12 * (floor((14 - number(substring(., 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) * 365 + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 4) - floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 100) + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 400) - 32045) - (number(substring(../my:startDate, 9, 2)) + floor((153 * (number(substring(../my:startDate, 6, 2)) + 12 * (floor((14 - number(substring(../my:startDate, 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) * 365 + floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 4) - floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 100) + floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 400) - 32045)
with the following Conditions on the Rule:
startDate is not blank and
endDate is not blank
Add a second Rule to the endDate field with the following settings:
Action: Set a field's value
Field: difference
Value: 0
with the following Conditions on the Rule:
startDate is blank or
endDate is blank
Add the following Rule to the difference field:
Action: Set a field's value
Field: .
Value: 0
with the following Condition on the Rule:
difference does not match pattern Custom Pattern: -{0,1}\d+
You should now have a fully functional InfoPath form that will calculate the difference between the dates soon after you have entered valid dates. This solution also works for InfoPath 2003 form templates and InfoPath 2007 browser-enabled form templates.
Wednesday, March 17, 2010
Improving the Performance of InfoPath 2007 Forms
http://msdn.microsoft.com/en-us/library/bb380251.aspx
Improving the Performance of InfoPath 2007 Forms
Improving the Performance of InfoPath 2007 Forms
Friday, March 12, 2010
Writing CAML Queries For Retrieving List Items from a SharePoint
http://sharepointmagazine.net/technical/development/writing-caml-queries-for-retrieving-list-items-from-a-sharepoint-list
Introduction
The Collaborative Application Markup Language (better known as CAML) is an XML-based query language that helps you querying, building and customizing Web sites based on Windows SharePoint Services. The XML elements define various aspects of a WSS site.
In this first series of articles I will explain in detail how to build and execute CAML queries to retrieve list items from a SharePoint list. List items can be retrieved in several different ways: using the SharePoint object model, using the SharePoint Lists web service or even by using Powershell. Use the SharePoint object model when your code runs on the server (like f.e. when you’re developing a web part or an application page). Use the SharePoint Web Services when your code doesn’t run on the server where SharePointis installed, for example when you develop office clients or windows applications. Powershell, at the other side, can be used by administrators when they quickly need to retrieve some information. In any way the CAML query is the same.
Building the CAML
CAML is an XML-based query language. Its root element is Query. Within the Query element two other elements are possible but not required: the OrderBy element and the Where element.
The OrderBy element is the simplest one. It is used to sort the returning list items. You have to specify the field(s) on which you want to sort the items and the sort direction. The syntax looks as follows:
The OrderBy clause is not required and you can specify one or more fields on which you want to sort. If you omit the Ascending attribute, your resulting rows will be sorted in ascending order. If you want to order in descending order you have to specify Ascending=’False’.
The Where clause is used to specify one or more filter criteria. This clause can be very simple but can end up being rather complex. In its most simple form you specify an operator, a field name for which you want to specify a criterion, and a value.
Janssens OperatorsYou have different operators:
Eq Equals
Neq Not equal
Gt Greater than
Geq Greater than or equal
Lt Lower than
Leq Lower than
IsNull Is null
BeginsWith Begins with
Contains Contains
FieldsThe FieldRef element can be any field of the list on which you want to execute the CAML query. If you use the Name attribute you need to specify the internal name of the field. But you can also use the IDattribute to specify the Guid of the field.
ValueThe Valueelement specifies the value part of the criterion. The attribute Typeis optional and specifies the data type of the field you want to specify the criterion for. If omitted the data type is considered as being Text. In all other cases you have to specify the Type attribute. DateTime fields are a special case and will be described in more detail later in this article.
If the field type is a Lookup you need to specify the text value.For example you have an Employees list and the Country field is a lookup field referring to the Countries list. In that case an employee living in Belgium will have f.e. following value: #15;Belgium. If you have to query for the employees living in Belgium you will have to write your query as follows:
Belgium You can find this not good coding practice because the name of the country can change in time. In that case you can also query on the id of the country specifying the LookupId attribute in the FieldRef element:
15 If you want to specify two filter criteria you also have to specify a join operator And or Or.
Janssens 21 If you want to specify more filter criteria you have to nest them in a very specific way:
Janssens 21 60 For each extra criterion you have to add an extra join operator at the outside of the query and add the criterion at the end:
Janssens 21 60 Belgium Retrieving List Items with CAML using the SharePoint Object Model
If you need to retrieve items from a list when developing web parts, application pages or custom field types you can best use the SPQueryobject from the SharePoint object model. This object is located in the Microsoft.SharePoint namespace of the Microsoft.SharePoint.dll located in the Global Assembly Cache.
Instantiate the object as follows:
SPQuery qry = new SPQuery();The most important property is the Query property, which needs to be set to your CAML query:
string camlquery = "" + "Smith ";qry.Query = camlquery;At this point you can execute the query on your list:
SPListItemCollection listItemsCollection = list.GetItems(qry);A small remark with the GetItems method of the SPList instance: this method returns a collection of type SPListItemCollection. It is possible that it is easier working with a DataTable. In that case you can execute the query as follows:
DataTable listItemsTable = list.GetItems(qry).GetDataTable();The query will not only return all list items that have their last name set to Smith, but also all columns of each list item. In cases you work with large lists it can be important to retrieve a subset of list items containing only the columns you need. In that case you will have to set the ViewFields property of the SPQuery object. You can do this by specifying all columns you want to see returned:
qry.ViewFields = "
The major disadvantage of the SPQuery object is that you can query only one list. If you want to query more than one list you will have to use the SPSiteDataQuery. More on this object in a later article because it earns special attention.
It is common knowledge by now but let me remind you that it’s always a good idea to use SPQuery to retrieve a subset of list items. You can loop through the list item collection to find the list items that match your needs but this will have a serious negative impact on the performance of your work.
Retrieving List Items with CAML using the SharePoint Web Services
If you are developing office clients or any other application that will not run on the server where SharePoint is installed you will need to use the SharePoint Web Services to retrieve (or update) information from SharePoint. If you want to query a list you will need to execute the GetListItems method from the Lists.asmxSharePoint web service.
Working with the SharePoint web services is different in Visual Studio 2005 then in Visual Studio 2008.
Retrieving List Items from the Lists.asmx using Visual Studio 2005First you have to reference the web service in your Visual Studio project. If you work with Visual Studio 2005 you have to add a Web Reference to theLists.asmx.
Then you have to instantiate the web service and pass the url of the SharePoint site, plus the location and name of the SharePoint web service. The SharePoint web services are all located in the ISAPI directory of the SharePoint 12 hive but are accessible from outside via the _vti_bin directory.
Initialize the Lists.asmx web service:
ListService listsWs = new ListService.Lists();listsWs.Url = siteUrl + @"/_vti_bin/lists.asmx";Then you have to set the credentials. In case you don’t need to specify a username or password you can proceed as follows:
listsWs.Credentials = System.Net.CredentialCache.DefaultCredentials;If you have to specify a user name and password:
listsWs.Credentials = new System.Net.NetworkCredential(userName, password);If you also have to specify a domain, use the other overload:
listsWs.Credentials = new System.Net.NetworkCredential(userName, password, domain);Then you are ready to call the GetListItems method, which requires the following syntax:
resultNode = _sharepointSite.ListsWSS.GetListItems(listName, viewName, queryNode, viewFieldsNode, rowLimit, queryOptionsNode, webID);Not all arguments are required. Arguments like view name, row limit and web ID are optional.
The query node argument will contain the CAML query as explained in previous sections but need to be enclosed within a and tag:
XmlDocument camlDocument = new XmlDocument();XmlNode queryNode = camlDocument.CreateElement("Query");queryNode.InnerXml = "" + "Smith ";The same applies to the ViewFields node:
XmlNode viewFieldsNode = camlDocument.CreateElement("ViewFields");viewFieldsNode.InnerXml = "
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");Retrieving List Items from the Lists.asmx using Visual Studio 2008But in case you are working with Visual Studio 2008 you will have to add a Service Reference to theLists.asmxweb service. If you don’t want to work asynchronously you have to uncheck the Generate Asynchronous Operations option in the Advanced dialog. When closing the dialog box Visual Studio automatically adds an app.config or web.config file. Open this file and locate the security section. The following XML is generated automatically:
Replace it with the following:
Return to your code and instantiate the web service:
ListsWS.ListsSoapClient ws = new ListsWS.ListsSoapClient();You also have to set the necessary credentials. If you can work with the default credentials you can write the following:
ws.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials; ws.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;If necessary you can also pass user name, password and domain:
ws.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential("Administrator", "secret", "U2UCOURSE"); ws.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;If necessary you can set the URL to the web service dynamically:
ws.Endpoint.Address = new System.ServiceModel.EndpointAddress( "http://wss.u2ucourse.com/_vti_bin/lists.asmx");Now you are ready to call the GetListItems method of the web service. There is no difference in calling this method from within Visual Studio 2005:
XmlDocument camlDocument = new XmlDocument(); XmlNode queryNode = camlDocument.CreateElement("Query");queryNode.InnerXml = "" + "Smith "; XmlNode viewFieldsNode = camlDocument.CreateElement("ViewFields");viewFieldsNode.InnerXml = "
Query Options
Executing a query is not only about CAML. When working with the SPQuery object you can set different properties to influence the returned list items. When working with the SharePoint web services, these options are translated into CAML and are part of the QueryOptions element.
RowLimitSetting this property limits the number of rows returned in the result set.
When working with the SPQuery object:
qry.RowLimit = 10;When working with the GetListItems method of the Lists.asmx web service:
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");queryOptionsNode.InnerXml = "10 ";IncludeMandatoryColumnsWhen this Boolean property is set to True, the result set will not only return the columns as defined in the ViewFields property, but also the columns that you defined in the list as required.
When working with the SPQuery object:
qry.IncludeMandatoryColumns = true;When working with the GetListItems method of the Lists.asmx web service:
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");queryOptionsNode.InnerXml = "True ";DatesInUtcSetting this Boolean property specifies whether the query returns dates in Coordinated Universal Time (UTC) format. The syntax is similar to that of the previously described property.
ExpandUserFieldWhen you omit this property or set it to false, user fields will return the login name of the user.
Setting this Boolean property to true (or include it in the QueryOptions node), user fields are returned as follows:
Karine Bosch,#U2UCOURSE\karine,#karine@U2UCOURSE.COM,#,#Karine BoschThe returned value includes the login name, e-mail address, Session Initiation Protocol (SIP) address, and title, when present, which causes a user field to behave as a multilookup field. The syntax is similar to that of the previously described property.
There are some other query options to set but they will be explained in detail in the next section.
Special Types of Queries
Some special types of lists require more specialized CAML queries. In some cases it only affects the CAML but in other cases you have to set extra SPQuery properties. If you execute this query against the Lists web service it will affect another argument of the GetListItems method, i.e. the QueryOptions argument.
Building queries for working with FoldersA first variant I will explain is how you can work with folders and sub folders. A folder is a special list item on a list or document library. If you execute a standard CAML query you will end up with list items from the root folder.
If you want to query all folders and sub folders of a list or document library, you have to define extra query options. If you are working with the object model you have to set the ViewAttributes property of the SPQuery object as follows:
qry.ViewAttributes = "Scope='Recursive'";If you work with GetListItems method of the Lists.asmx SharePoint web service, you have to define an extra node with the QueryOptions element:
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");queryOptionsNode.InnerXml = "
qry.Folder = list.ParentWeb.GetFolder("Folders DocLib/2008");When working with the web services you have to do the following:
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");queryOptionsNode.InnerXml = "Folders DocLib/2008 ";Building Queries for working with DateTime ValuesFiltering on DateTime fields also requires some extra attention. First of all when querying for a specific date, you have to use the SharePoint datetime notation:
2008-08-10T10:00:00Z But in this case the date is hard coded and the time part will not be taken into account. This query will return all list items with a start date as of 10 August 2008, also those starting before 10 o’clock. If you want your query to take into account the time part, you have to use a special attribute IncludeTimeValue that you can set on the FieldRef element:
2008-08-10T10:00:00Z This query will return all list items with a start date as of 10 August 10 o’clock.
As already said, this way the date is hard coded. If you want your query a bit more dynamic, you can always use the element Today.
Today will not take a time part into account. Unfortunately a Now element doesn’t exist.
You can also add or subtract a number of days from today’s date. In that case you have to add the Offset attribute to the Today element. The Offset attribute accepts a positive value for adding days and a negative value for subtracting days.
As all this is pure CAML, the query is the same whether you work with the object model or the SharePoint web services.
Building Queries for Calendar ListsCalendar lists also require a little more attention. A calendar list is based on the Event content type. Normal events can be retrieved with the usual CAML queries. It’s only when you start working with recurring events (events that occur once a day or twice a week or month) that you run into problems.
The Event content type defines a field named fRecurrence, which is set to 1 when a recurring event is created.
Defining a recurring event
When the Calendar view is rendered a recurring event is split into a number of event instances, one for each recurrence.
Calendar view showing event instances for recurring event
The definition of the recurrence is XML stored in another field defined by the Event content type, i.e. RecurrenceData. An example of such a definition looks as follows:
su FALSE Fortunately you don’t have to parse the XML yourself to get the actual instances of the recurring event. You can use a combination of CAML syntax and query options to achieve this.
When working with the SPQuery object you need to set the ExpandRecurrences property to true. You also have to specify a date in the CalendarDate property. This date will be used to compare the recurring event instances.
Next you have to add a DateRangesOverlap element to the Where clause of the CAML query. This element is used in queries to compare the dates in a recurring event with the date specified in the CalendarDateproperty to see whether they overlap. The CAML looks as follows:
The
In case you need to work with the GetListItems method of the Lists.asmx web service, you translate the query options into XML:
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");queryOptionsNode.InnerXml = "TRUE " + "2008-08-12T10:00:00Z ";Read more about Calendar lists here: http://blogs.msdn.com/sharepoint/archive/2007/05/14/understanding-the-sharepoint-calendar-and-how-to-export-it-to-ical-format.aspx
This is it about retrieving List Items using CAML. Don’t hesitate to post your comments and questions below.
Introduction
The Collaborative Application Markup Language (better known as CAML) is an XML-based query language that helps you querying, building and customizing Web sites based on Windows SharePoint Services. The XML elements define various aspects of a WSS site.
In this first series of articles I will explain in detail how to build and execute CAML queries to retrieve list items from a SharePoint list. List items can be retrieved in several different ways: using the SharePoint object model, using the SharePoint Lists web service or even by using Powershell. Use the SharePoint object model when your code runs on the server (like f.e. when you’re developing a web part or an application page). Use the SharePoint Web Services when your code doesn’t run on the server where SharePointis installed, for example when you develop office clients or windows applications. Powershell, at the other side, can be used by administrators when they quickly need to retrieve some information. In any way the CAML query is the same.
Building the CAML
CAML is an XML-based query language. Its root element is Query. Within the Query element two other elements are possible but not required: the OrderBy element and the Where element.
The OrderBy element is the simplest one. It is used to sort the returning list items. You have to specify the field(s) on which you want to sort the items and the sort direction. The syntax looks as follows:
The Where clause is used to specify one or more filter criteria. This clause can be very simple but can end up being rather complex. In its most simple form you specify an operator, a field name for which you want to specify a criterion, and a value.
Eq Equals
Neq Not equal
Gt Greater than
Geq Greater than or equal
Lt Lower than
Leq Lower than
IsNull Is null
BeginsWith Begins with
Contains Contains
FieldsThe FieldRef element can be any field of the list on which you want to execute the CAML query. If you use the Name attribute you need to specify the internal name of the field. But you can also use the IDattribute to specify the Guid of the field.
ValueThe Valueelement specifies the value part of the criterion. The attribute Typeis optional and specifies the data type of the field you want to specify the criterion for. If omitted the data type is considered as being Text. In all other cases you have to specify the Type attribute. DateTime fields are a special case and will be described in more detail later in this article.
If the field type is a Lookup you need to specify the text value.For example you have an Employees list and the Country field is a lookup field referring to the Countries list. In that case an employee living in Belgium will have f.e. following value: #15;Belgium. If you have to query for the employees living in Belgium you will have to write your query as follows:
If you need to retrieve items from a list when developing web parts, application pages or custom field types you can best use the SPQueryobject from the SharePoint object model. This object is located in the Microsoft.SharePoint namespace of the Microsoft.SharePoint.dll located in the Global Assembly Cache.
Instantiate the object as follows:
SPQuery qry = new SPQuery();The most important property is the Query property, which needs to be set to your CAML query:
string camlquery = "
SPListItemCollection listItemsCollection = list.GetItems(qry);A small remark with the GetItems method of the SPList instance: this method returns a collection of type SPListItemCollection. It is possible that it is easier working with a DataTable. In that case you can execute the query as follows:
DataTable listItemsTable = list.GetItems(qry).GetDataTable();The query will not only return all list items that have their last name set to Smith, but also all columns of each list item. In cases you work with large lists it can be important to retrieve a subset of list items containing only the columns you need. In that case you will have to set the ViewFields property of the SPQuery object. You can do this by specifying all columns you want to see returned:
qry.ViewFields = "
The major disadvantage of the SPQuery object is that you can query only one list. If you want to query more than one list you will have to use the SPSiteDataQuery. More on this object in a later article because it earns special attention.
It is common knowledge by now but let me remind you that it’s always a good idea to use SPQuery to retrieve a subset of list items. You can loop through the list item collection to find the list items that match your needs but this will have a serious negative impact on the performance of your work.
Retrieving List Items with CAML using the SharePoint Web Services
If you are developing office clients or any other application that will not run on the server where SharePoint is installed you will need to use the SharePoint Web Services to retrieve (or update) information from SharePoint. If you want to query a list you will need to execute the GetListItems method from the Lists.asmxSharePoint web service.
Working with the SharePoint web services is different in Visual Studio 2005 then in Visual Studio 2008.
Retrieving List Items from the Lists.asmx using Visual Studio 2005First you have to reference the web service in your Visual Studio project. If you work with Visual Studio 2005 you have to add a Web Reference to theLists.asmx.
Then you have to instantiate the web service and pass the url of the SharePoint site, plus the location and name of the SharePoint web service. The SharePoint web services are all located in the ISAPI directory of the SharePoint 12 hive but are accessible from outside via the _vti_bin directory.
Initialize the Lists.asmx web service:
ListService listsWs = new ListService.Lists();listsWs.Url = siteUrl + @"/_vti_bin/lists.asmx";Then you have to set the credentials. In case you don’t need to specify a username or password you can proceed as follows:
listsWs.Credentials = System.Net.CredentialCache.DefaultCredentials;If you have to specify a user name and password:
listsWs.Credentials = new System.Net.NetworkCredential(userName, password);If you also have to specify a domain, use the other overload:
listsWs.Credentials = new System.Net.NetworkCredential(userName, password, domain);Then you are ready to call the GetListItems method, which requires the following syntax:
resultNode = _sharepointSite.ListsWSS.GetListItems(listName, viewName, queryNode, viewFieldsNode, rowLimit, queryOptionsNode, webID);Not all arguments are required. Arguments like view name, row limit and web ID are optional.
The query node argument will contain the CAML query as explained in previous sections but need to be enclosed within a
XmlDocument camlDocument = new XmlDocument();XmlNode queryNode = camlDocument.CreateElement("Query");queryNode.InnerXml = "
XmlNode viewFieldsNode = camlDocument.CreateElement("ViewFields");viewFieldsNode.InnerXml = "
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");Retrieving List Items from the Lists.asmx using Visual Studio 2008But in case you are working with Visual Studio 2008 you will have to add a Service Reference to theLists.asmxweb service. If you don’t want to work asynchronously you have to uncheck the Generate Asynchronous Operations option in the Advanced dialog. When closing the dialog box Visual Studio automatically adds an app.config or web.config file. Open this file and locate the security section. The following XML is generated automatically:
ListsWS.ListsSoapClient ws = new ListsWS.ListsSoapClient();You also have to set the necessary credentials. If you can work with the default credentials you can write the following:
ws.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials; ws.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;If necessary you can also pass user name, password and domain:
ws.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential("Administrator", "secret", "U2UCOURSE"); ws.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;If necessary you can set the URL to the web service dynamically:
ws.Endpoint.Address = new System.ServiceModel.EndpointAddress( "http://wss.u2ucourse.com/_vti_bin/lists.asmx");Now you are ready to call the GetListItems method of the web service. There is no difference in calling this method from within Visual Studio 2005:
XmlDocument camlDocument = new XmlDocument(); XmlNode queryNode = camlDocument.CreateElement("Query");queryNode.InnerXml = "
Query Options
Executing a query is not only about CAML. When working with the SPQuery object you can set different properties to influence the returned list items. When working with the SharePoint web services, these options are translated into CAML and are part of the QueryOptions element.
RowLimitSetting this property limits the number of rows returned in the result set.
When working with the SPQuery object:
qry.RowLimit = 10;When working with the GetListItems method of the Lists.asmx web service:
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");queryOptionsNode.InnerXml = "
When working with the SPQuery object:
qry.IncludeMandatoryColumns = true;When working with the GetListItems method of the Lists.asmx web service:
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");queryOptionsNode.InnerXml = "
ExpandUserFieldWhen you omit this property or set it to false, user fields will return the login name of the user.
Setting this Boolean property to true (or include it in the QueryOptions node), user fields are returned as follows:
Karine Bosch,#U2UCOURSE\karine,#karine@U2UCOURSE.COM,#,#Karine BoschThe returned value includes the login name, e-mail address, Session Initiation Protocol (SIP) address, and title, when present, which causes a user field to behave as a multilookup field. The syntax is similar to that of the previously described property.
There are some other query options to set but they will be explained in detail in the next section.
Special Types of Queries
Some special types of lists require more specialized CAML queries. In some cases it only affects the CAML but in other cases you have to set extra SPQuery properties. If you execute this query against the Lists web service it will affect another argument of the GetListItems method, i.e. the QueryOptions argument.
Building queries for working with FoldersA first variant I will explain is how you can work with folders and sub folders. A folder is a special list item on a list or document library. If you execute a standard CAML query you will end up with list items from the root folder.
If you want to query all folders and sub folders of a list or document library, you have to define extra query options. If you are working with the object model you have to set the ViewAttributes property of the SPQuery object as follows:
qry.ViewAttributes = "Scope='Recursive'";If you work with GetListItems method of the Lists.asmx SharePoint web service, you have to define an extra node with the QueryOptions element:
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");queryOptionsNode.InnerXml = "
qry.Folder = list.ParentWeb.GetFolder("Folders DocLib/2008");When working with the web services you have to do the following:
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");queryOptionsNode.InnerXml = "
As already said, this way the date is hard coded. If you want your query a bit more dynamic, you can always use the element Today.
You can also add or subtract a number of days from today’s date. In that case you have to add the Offset attribute to the Today element. The Offset attribute accepts a positive value for adding days and a negative value for subtracting days.
Building Queries for Calendar ListsCalendar lists also require a little more attention. A calendar list is based on the Event content type. Normal events can be retrieved with the usual CAML queries. It’s only when you start working with recurring events (events that occur once a day or twice a week or month) that you run into problems.
The Event content type defines a field named fRecurrence, which is set to 1 when a recurring event is created.
Defining a recurring event
When the Calendar view is rendered a recurring event is split into a number of event instances, one for each recurrence.
Calendar view showing event instances for recurring event
The definition of the recurrence is XML stored in another field defined by the Event content type, i.e. RecurrenceData. An example of such a definition looks as follows:
When working with the SPQuery object you need to set the ExpandRecurrences property to true. You also have to specify a date in the CalendarDate property. This date will be used to compare the recurring event instances.
Next you have to add a DateRangesOverlap element to the Where clause of the CAML query. This element is used in queries to compare the dates in a recurring event with the date specified in the CalendarDateproperty to see whether they overlap. The CAML looks as follows:
In case you need to work with the GetListItems method of the Lists.asmx web service, you translate the query options into XML:
XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");queryOptionsNode.InnerXml = "
This is it about retrieving List Items using CAML. Don’t hesitate to post your comments and questions below.
Thursday, March 11, 2010
Syntax for directly calling the Web Service using HTTP GET
The syntax for directly calling the Web Service using HTTP GET is
http://server/webServiceName.asmx/functionName?parameter=parameterValue
Therefore, the call for our Web Service will be
http://localhost/work/aspx/SampleService.asmx/GetSecurityInfo?Code=IBM
http://server/webServiceName.asmx/functionName?parameter=parameterValue
Therefore, the call for our Web Service will be
http://localhost/work/aspx/SampleService.asmx/GetSecurityInfo?Code=IBM
Subscribe to:
Posts (Atom)