How to filter and sort table data

A how-to guide for filtering and sorting table data.
Tags:

Introduction

Would you like to filter and sort your table data? Then follow these steps.

Prerequisites

  • You configured the frames and routes for your solution.
  • You configured the relevant contents such as the control panel(s), model(s), viewer(s) and data store.
  • You configured the entity data store. See the store inputs for more information.
  • You configured the entity table to display your entities. See the display stored inputs guide for more information.

Filter or search entities with a control

1. Add a control to filter the entity data store

  • Add a ControlPanel to your frame.
  • Configure a control to filter or search entities.
    • To filter with a value in the EntityDataStore, you can use a Dropdown.
    • To search for a value in the EntityDataStore, you can use a TextInput.

The configuration should look similar to the example below:

  my-control-panel: 
    kind: ControlPanel
    controls:
      name-filter: # Control for searching
        kind: TextInput
        placeholder: Name to search
      type-filter: # Control for filtering
            kind: Dropdown
            label: Type    
            choices:
              - label: Type 1
                value: 1
              - label: Type 2
                value: 2
              - label: Type 3
                value: 3
      date-filter: # Control for filtering
        kind: DateTimeInput
        label: date

2. Configure a filter in the entity datastore.

Filter entities with one condition

  • In your EntityDataStore filters, add a filter. The filter key matches the control to filter with.
  • Define the filter type. For filters of type:
    • equal, an entity is shown if the control value and property value are equal.
    • contains, an entity is shown if the property value is found in the control value list.
    • search, an entity is shown if a text search of the control value gives a positive result.
    • type: lesserThanOrEqual , an entity is shown if the property value is smaller than or equal to the control value.
    • type: greaterThanOrEqual , an entity is shown if the property value is larger than or equal to the control value.
  • Define which property or properties should be filtered or searched.

💡 If there are multiple filters, the filters are applied with an ‘and’ condition. To be included in the filtered list, entities need to satisfy all filter conditions.

The configuration should look similar to the example below:

my-entity-data-store: 
  kind: EntityDataStore
  properties:
    name:
      type: String
    type:
      type: Number
    start-date:
      type: DateTime
      nullable: false
    end-date:
      type: DateTime
      nullable: true
      default: null
  filters:
    type-filter:
      type: equal
      property: type
    name-filter:
      type: search
      properties: [name]
    date-filter: #Matches control to filter with
      type: lesserThanOrEqual
      property: start-date

Filter entities with multiple conditions

  • In your EntityDataStore filters, add a filter with type: and or type: or. The filter key matches the control to filter with.
  • Define your conditions with EntityDataStore filters.
  • Define which property to check the condition against.

In the example below, if the end-date value is null OR greater than or equal to the date-filter AND start-date is lesser than or equal to the date-filter, then the entities are shown in the EntityTable.

my-control-panel:
	kind: ControlPanel
	controls:
    date-filter:
      type: NumberInput
      label: Date filter
my-entity-data-store:
	kind: EntityDataStore
	properties:
    start-date:
      type: DateTime
      nullable: false
    end-date:
      type: DateTime
      nullable: true
      default: null
	filters:
	  date-filter:
	    type: and
	    conditions:
	    - type: lesserThanOrEqual
	      property: start-date
	    - type: or
	      conditions:
	      - type: greaterThanOrEqual
	        property: end-date
	      - type: isNull
	        property: end-date

3. Subscribe the entity data store to the relevant control panel.

  • To filter or search with a control, subscribe the EntityDataStore to the ControlPanel.
  • Define the frame and source.
  • Define which controls to filter with the filterByControls property.

The configuration should look similar to the example below:

my-entity-data-store: 
  kind: EntityDataStore
  persistToStore: my-db-v1
  properties:
    name:
      type: String
    type:
      type: Number
  filters:
    type-filter:
      type: equal
      property: type
    name-filter:
      type: search
      properties: [name]  
    date-filter:
	    type: and
	    conditions:
	    - type: lesserThanOrEqual
	      property: start-date
	    - type: or
	      conditions:
	      - type: greaterThanOrEqual
	        property: end-date
	      - type: isNull
	        property: end-date 
  subscribe:
    - kind: ControlPanel
      frame: home-frame
      source: my-control-panel
      filterByControls: [name-filter,type-filter,date-filter]

See the image below as an example.

Filter entities with a column

1. Add a filter to a column in the entity table

  • Configure a filter to the column you’d like to filter with kind: MultipleChoice.
  • Define the choices and their labels.

💡 The name of the choices should match the control choices that set this column property.

The configuration should look similar to the example below:

 my-entity-table:
    kind: EntityTable
    columns:
      status:
        kind: Text
        label: Status
        filter:
          kind: MultipleChoice
          choices:
            started: 
              label: Started 
            progress: 
              label: In progress
            completed:
              label: Completed

2. Configure a filter in the entity datastore

  • Add filters to the EntityDataStore.
  • Define which property should be filtered with the property.
  • Define the type of the filter.

The configuration should look similar to the example below:

 my-entity-data-store: 
  kind: EntityDataStore
  properties:
    status: 
      type: String
  filters:
    status:
      type: contains
      property: status

3. Subscribe the entity data store to the entity table.

  • To filter the entities based on the entity table column, subscribe the EntityDataStore to the EntityTable.
  • Define the frame and source.
  • Define which columns to filter with the filterByColumns property.

The configuration should look similar to the example below:

my-entity-data-store: 
  kind: EntityDataStore
  properties:
    status: 
      type: String
  filters:
    status:
      type: contains
      property: status
  subscribe:
    - kind: EntityTable
      frame: home-frame
      source: my-entity-table 
      filterByColumns: [status]

See the image below as an example.

Sort table data

To sort the entities shown in the Entity Table:

  1. In the EntityDataStore configure sort .
  2. Define which property to sort with and the type of sorting.
  • ascending order to sort the values from smaller to larger.
  • descendingorder to sort the values from larger to smaller.
  • fixed order to sort the values in a defined order.

Note that the sorting type isn’t user defined, the configured sorting is automatically applied to the entities.

The configuration should look similar to the examples below:

my-eds:
  kind: EntityDataStore
  properties:
  	my-date:
      type: ...
    my-number:
      type: Number
    my-status:
      type: String
	filters:
		...
  sort:
    my-date:
      type: ascending
    my-status:
      type: fixed
      values:
      - active
        in-progress
        archived

Configuration

See the entity data store configuration docs and entity table configuration docs for more information.