Data filtering and sorting

Applies to Infiniti v8.0 or later
 

Data Filtering and Sorting

Records retrieved from a data source can be filtered and sorted in ‘Design’. For example a list of customers could be filtered so that only new customers are retrieved, and those records then sorted alphabetically by surname. Filtering refers specifically to retrieving particular records only if they meet certain criteria whereas sorting refers to the order in which the records are displayed.
 
Data filtering and sorting applies to the data source question type only. Filtered and sorted records can be automatically included in the generated document or presented back to the end user in ‘Produce’ for selection.
 

Data Filtering Using Data Filter

Data filtering enables data to be presented as a sub-set, showing only relevant records, or even filtered to a single record. For example, the details of a particular staff member could be retrieved by filtering the question on a Staff ID. The Staff ID could be sourced from the user’s profile or collected during the question set.
 
Data filters are based on a key field, comparison operator and a value. For example, the key field might be Staff ID, comparison operator might be ‘Equals’, and the value would be the ID to be matched exactly.
 
Filtered data source questions retrieve single or multiple records according to the question’s Selection Type property. The retrieved records are either presented to the end user in ‘Produce’ for further selection or included in the document automatically.
 
Multiple filters can be added to a data source question, which have an accumulative effect on the records retrieved. For example, if you filter a Staff database for all people who started after January 1, 1999 and also on all people whose surname begins with the letter ‘A’, only records that match both criteria will be returned.
 
Filters are applied to key fields that have been identified in the data source. Key fields are defined in ‘Manage’. For more information refer to Article: Key Fields, Custom Fields and Display Fields Explained
 
Data filtering properties are located on the Data Filter tab.
 
Each filter comparison value can be based on a parent question in the question set, which is any question that precedes the current question, or based on a constant value typed into the filter. Parent questions can include other data sources, allowing you to join multiple data sets in a primary/foreign key arrangement.
 
A list of all the data filters that a question is referenced by can be retrieved by right-clicking a question in the question set and selecting the Find Dependencies option from the pop-up menu.

 How to create data filters

  1. Select an appropriate data source question. Note that data filtering can apply to both visible and invisible data source questions

  2. On the Data Filter tab, select a field to be filtered from the Key Field property.

  3. Select a Comparison property, which defaults to ‘Equals’. This is the comparison operator to use to filter data.

  4. If Parent Question was selected above, use the Question and Answer drop-downs to select a parent question.

If, however, Constant Value was selected above, enter a constant value into the textbox provided.
  1. Click Add to add the new filter, or Update if you are updating an existing filter.

  2. Repeat for each additional filter required, filters can be organized into ‘Any of’ and ‘All Of’ Groups, in the same manner as conditions and rules above.

 

Nested Multiple Record Filters

Often when retrieving multiple records for a document it is necessary to also retrieve nested sets of records, for example:
  • Meetings contain lists of attendees, and

  • Schools have classes each with students.

Infiniti supports these scenarios by allowing you to apply filters to multiple data source questions, with each nested data source question filtering based on at least one of its immediate parent’s data fields. Often there is a ‘one-to-many’ relationship between the data, as in the example below:
 
Meeting Table/View
Attendee Table/View
 
meetingID
meetingDescription
101
Board Meeting
102
Project Review
103
Staff Lunch
 
meetingID
attendee
101
Greg Jones
101
Sally Hales
101
Ben Perry
102
Sally Hales
102
Ben Perry
103
Greg Jones
103
Sally Hales
103
Ben Perry
103
Mary O’Brien
 
To properly organize the above data into a generated document, it is necessary to use two data source questions - one for the meeting data and one for the nested attendee data. The attendee table needs to be filtered by the meetingID of the parent meetings table.
 
In the Article: Repeating Bookmarks/Placeholders it is described how to set up bookmarks for the data source question type, including the repeat bookmark. When using nested data tables, the bookmarking of the Word document becomes more involved. You need to add bookmarks for each of the data fields to be included in the document, and wrap a repeat bookmark around each section that will be repeated by each data table question.
 
For example, the document below has a ‘Meeting_SECTION’ bookmark around the entire block, including all its nested sections. It then includes an ‘Attendee_SECTION’ bookmark surrounding the nested section.
Nested repeat bookmarks must be contained entirely within its parent repeat bookmark.
 
 
Once the bookmarks have been added to the document, the data source questions can be configured in ‘Design’. The attendee data table question needs to be filtered by the meeting data table question as shown below.
 

Aggregate Filtering

While nested data sources are commonly filtered from a single parent data record, sometimes it’s important to be able to filter a child data source on more than one selected parent data row. The Aggregate option on the Data Filter properties allows this situation to be handled.
For example, imagine a report showing Net Income for each month in a Financial Quarter. You want users to select which Financial Quarter to report on, and then select the specific months in those quarters. They may select more than one Financial Quarter. The first data source may appear as follows:
Once selections are made,  the Aggregate option on the child data source question’s Data Filter properties allows both the selected quarters to be filtered on the returned Reporting Month data, as depicted below:

How to configure Aggregate Data Filtering

  1. Configure the parent and child multi-row data source questions.

  2. Configure a new data filtering rule on the child data source, based on the parent data source.

  3. Tick the Aggregate check box, then click Add.

 

Sorting Data

The Sort Fields tab allows you to control the order retrieved data is presented in for the generated document, or within ‘Produce’ questions such as drop-down lists. For example, a collection of books can be ordered by the author’s name and then by the book’s title in ascending order.
Sorting is performed for each sort field in order from top to bottom. In the example above, the author’s name is the first sort ensuring ‘Abraham’ is before ‘Huntman’. The second sort field on the book name is ordered alphabetically for each author. In other words, if ‘Abraham’ has more than one book title, these will be in alphabetical order.
 

How to create sort fields

  1. In the Sort Field drop-down list, select a field by which the data is to be sorted.

  2. Select the Direction of the sort order from the drop-down list.

  3. Click Add to add a new sort field, or Update if modifying an existing sort field.

  4. Add additional sort fields as necessary.

  5. Use the up () and down () arrows to change the order in which the sorting is applied.

 

Related Articles

Keywords

data source datasource