Tag : filter

InfoPath – Using the filter option

Querying and retrieving data within InfoPath can be confusing mainly because the ‘filter…’ option for data retrieval is sometimes used but not always. This makes it seems like two queries are being used and the beginner will not understand which one takes precedence or when the ‘filter’ option should be used.

The ‘Query using a data connection’ action is the query that populates the data connection, while the ‘filter…’ button used when assigning values to a field assigns a value to a field from a Data Connection that has already been populated. Only the rows returned by the query are available to controls within your form. Rows that have been excluded from the Data Connection (through the setting of a query field) are not available even though they are in the SharePoint list

For example, if in a SharePoint list we have the following table:

Row ID Key Department First Name Last Name
1 P001 IT John Smith
2 P006 IT Lynette Lee
3 Q001 IT Mark Charter
4 Q002 IT Kevin Jenzen
5 Q003 HR Joe Bloggs
6 Q004 HR Sue-Anne Harding
7 Q005 Sales Mike Smith


… and you set the data connection query field ‘Department’ to ‘IT’ and query the table, the system will make the following records available to any control that uses this list

Row ID Key Department First Name Last Name
1 P001 IT John Smith
2 P006 IT Lynette Lee
3 Q001 IT Mark Charter
4 Q002 IT Kevin Jenzen


The other list items (ID’s 5, 6 and 7) will not appear and are not available to any of the controls that access this list. If you want them to be available, you would need to set the query field of ‘Department’ to blank and redo the query

Now consider that we have 4 rows of data in the Data Connection and we wish to display the first and last names of the approver ID selected from a combo. In this case we would need to filter the 4 rows in the data connection to get the correct names to display

You do NOT need to always filter and a good rule of thumb is that if your Data Connection returns a single record, or you do not care about the value, then no filtering is needed. If multiple rows can be returned by the query and you care about the data displayed, then you need to filter

In short, though:

If the connection will return a single record available, do a straight assign

If the connection returns multiple records, do a filtered assign

Straight assign

In the example below I assign the email address of the user from the ‘User Information List’ to a field called ‘CurrentUserEmail’

107-1Here I set the query field Account (which strangely enough displays as ‘name’ in the Rule Details box) to the function ‘userName()’.

Next, I query the User Information list. Since the query will be based on the name of the current user, only a single row from the User Information list will be returned as there is always only one account with the value in ‘userName()’


Now, I want to set and display a field that contains the users email. Since I know the ‘user Information List’ will only be populated with a single row, I can do a straight assign


I use the ‘Insert Field or Group’ button to select the ‘Work Email’ field from the list of available fields in the ‘User Information List (Secondary)’. Since I know that there is only a single record in memory, I do not need to use the ‘Filter Data …’ option. You would click OK all the way through to save your changes


Filtered assign

In the next example, I am working with a form that allows the user to select a Location from a list of locations. I then want to default the Entity value on the form to the default Entity value for that location

Add an action rule to perform the assign statement


Click on the function button to open up the ‘Insert Formula’ dialog box and then click on the ‘Insert Field or Group …’ button

106-18Once you click the ‘Insert Field or Group …’ button, a ‘Field’ selection box will be displayed, initially set to ‘Main’. I use the drop-down arrow to select the list I want – in this case ‘Locations (Secondary). This will display fields similar to the following


I want to get the value of the Entity in my form, however, which location record should I use to get Entity as the system has multiple location records in memory? I had previously selected a location I needed from the list of locations and saved it to the field called ‘CapexLocation’, so now I will use that value to make sure I get the correct entity in my form


To specify which record I need to use, I select the ‘Filter Data…’ button which opens up a form similar to the following. I select ‘Add…’ from the dialog box


This opens yet another window where I specify the criteria to use to select the correct Location record as shown below. I click on the drop down box and select ID (I select ID because my ‘CapexLocation’ field on my form contains Location ID but displays the location name. This can cause some confusion to new users of InfoPath)


I leave the condition as ‘is equal to’ and set the last combo box to ‘Select a field or group …


… and another dialog box opens up allowing me to select the ‘CapexLocation’ from the ‘Main’ group


After clicking on ‘OK’ [more than] a few times, we end up with the following in the Formula dialog box


The system will now return the Entity related to the Capex Location that I had previously selected. You can now click OK once again to finalize your setting. The system will now return the correct entity from the location list based on the location ID in the form

Workflow – Troubleshooting REST calls

When the REST/HTTP Call to a Web Service is not returning correct results, the reason why may not be immediately obvious. In these cases you may need to interrogate the data returned to discover what went wrong

Check the Response Status Code

Add a Log action after the rest call to display the value of the ‘ResponseStatusCode’ variable ‘ResponseCode’ as shown below


Check the logged Response Status Code

Run your workflow and check your workflow log. If everything is OK, this will normally return a positive indication such as ‘OK’ or ‘Created’. If there is a problem, it will display something similar to the message shown below:


If it returns a message such as ‘NotFound’ (as shown above) or ‘InternalServerError’ you may need to display the error in more detail, by logging the actual error message to the workflow log. This is done using a ‘Get’ action on the ResponseContent Variable (‘Response’) as follows:

Get odata.error/message/value from Variable:Response (Output to Variable:ErrorMessage)


The field ‘ErrorMessage’ is a workflow variable that I created to store the contents of the error message. Alternatively, you can display the entire ‘Response’ but displaying this value if the length is greater than 255 characters would hang your workflow.

Run the workflow again

Publish and run the workflow again and check your workflow log again. In my case it displayed the following


In my case, the message is saying that it could not find the SharePoint resource ‘GetByNameXXX’.

Aha! – It should be ‘GetByName’ not ‘GetByNameXXX’. I change my workflow to specify ‘GetByName’ instead of ‘GetByNameXXX’ and republish it. When it runs without error, I remove the error message from the workflow and the world is in balance

Other issues

If no error is being generated, it could be that the field names or their assignments are incorrect in the ODATA call. Check the following:

The body of the REST/HTTP call is normally in an ODATA format similar to the following:

[%Workflow Context:Current Site URL%]_api/Lists/GetbyTitle(‘Quotes’)/items/?$select=ID&$filter=Form_x0020_Status eq ‘In Approval’ and Active eq 1&$Top=500&$orderby=SpecApprover

Some common mistakes in the REST calls:

  1. Your field names are not in the correct case. If your SharePoint field is ‘Title’, then in the REST call it should be ‘Title’ not ‘title’
  2. Your field names are incorrectly stated in either the select, filter or orderby options. This can sometimes be tricky especially if your SharePoint fields have spaces in them (Not the best practice, but it happens). For example the SharePoint field Form Status must be specified as Form_x0020_Status. To find the real value of the field, open the list and select Library Settings. Scroll down to the field you are interested in and click on it. Then cut the full URL it navigates to and view the last bit after ‘Field=’. This is the internal field name. In my case, Form Status is actually Form%5Fx0020%5FStatus (Note how the system substitutes %5F for underscores and x0020% for spaces)
  3. You have mistakenly used ‘=’ instead of ‘eq’ or vice-versa in your ODATA statement
  4. Your Boolean vales are specified as true or false or yes or no. Boolean values must be specified as 1 for true and 0 for false. To check for records that have the field ‘Active’ equal to Yes, the ODATA statement should contain Active eq 1 and not Active eq Yes
  5. Your Top query option ($top) is not specified and will therefore only return 100 rows. If you expect more, then set ‘$Top’ to the maximum amount you expect (say ‘$Top=500’). At the time of writing, the maximum number of rows returned is 500
  6. Your string filter values are not enclosed in single quotes (for example BU eq AB should be BU eq ‘AB’ or BU eq ‘[%Current Item:BUShortName%]’
  7. Your number values are placed in single quotes (ID eq ‘307’ should be ID eq 307)
  8. When an HTTP Call statement returned more records than expected, I often found that either the names of fields or values was specified incorrectly (Boolean values in ODATA appear to be 1 or 0 instead of true or false for example, or perhaps the field name was misspelled or used spaces instead of the substitution characters)