Skip to main content

 

 

 

 

Template:OutSystems/Documentation_KB/Breadcrumb_New_Layout

 

 

Template:OutSystems/OSLanguageSwitcher

 

Applies only to Reactive Web Apps

 

 

OutSystems

How to use dynamic sorting in a Table fed by a SQL query

Template:OutSystems/Documentation_KB/ContentCollaboration
  • Edit
    Collaborate with us
    Edit this page on GitHub
  • You can enable dynamic sorting for a Table by selecting (New On Sort Client Action) on the On Sort event. Service Studio creates an action with the sorting logic that uses the Sort Attribute of each Header Cell. The format of the Sort Attribute is optimized for Aggregates and the Sort Attribute needs to be encoded before using it in a SQL query.

    To enable the dynamic sorting in a Table that has a SQL query as the data source follow these steps:

    1. To create a new action to handle the On Sort event of the Table, select the Table and add a (New On Sort Client Action) to the On Sort event.

      Selecting (New On Sort Client Action) on the On Sort event also adds two Local Variables, TableSort and StartIndex.

    2. To define the default sort attribute, select the TableSort variable, and set the Default Value property to "<Sort Attribute>", where <Sort Attribute> is the value of the Sort Attribute of a Header Cell.

      For example, setting the Default Value to "User.Name" sets the Name attribute of the User entity as the default sort attribute.

    3. In the new OnSort action flow, set the Data Source of the Refresh Data element to the Data Action that contains the SQL query that feeds data to your Table.

    4. Add a Query Parameter to the SQL query, set the Name to SortForSQL and set the Expand Inline property to Yes.

      Setting the Expand Inline property to Yes allows the use of the Query Parameter as part of the SQL code that's sent to the database at runtime without first being evaluated and turned into a literal by the SQL engine.

    5. Add the SQL snippet ORDER BY @SortForSQL to your SQL query.

    6. Create a new Function, EncodingSortForSQL, to encode the TableSort variable used by the OnSort action to a format that's usable by a SQL query.

      1. In the Logic tab, create a new Server Action and add the following Variables:

        • SortForAggregate Input Parameter.
        • SortForSQL Output Parameter.
        • Entity Local variable.
        • Attribute Local variable.
      2. Set the Name of the new action to EncodingSortForSQL and set the Function property to Yes.

      3. Add an If between the Start and End elements, and set the Condition to the following expression:

        Index(SortForAggregate,".") <> -1
        
      4. Add an Assign to the False branch of the previous If. Add the following assignment:

        • SortForSQL = SortForAggregate
      5. Add an Assign and connect it to the True branch of the If. Add the following assignment:

        • Entity = "{" + Substr(SortForAggregate,0,Index(SortForAggregate,".")) + "}"
      6. Add and connect an If to the right of the Assign created on the previous step. Set the Condition to the following expression:

        Index(SortForAggregate,"DESC",ignoreCase: True) <> -1
        
      7. Add and connect an Assign to the True branch of the If. Add the following assignments:

        • Attribute = "[" + Substr(SortForAggregate,Index(SortForAggregate,".")+1,Index(SortForAggregate," ")-Index(SortForAggregate,".")-1) + "]"

        • SortForSQL = Entity + "." + Attribute + " DESC"

      8. Add and connect an End element to the right of the Assign.

      9. Add and connect an Assign to the False branch of the If. Add the following assignments:

        • Attribute = "[" + Substr(SortForAggregate,Index(SortForAggregate,".")+1,Length(SortForAggregate)) + "]"

        • SortForSQL = Entity + "." + Attribute

      10. Add and connect an End element to the bottom of the previous Assign.

    7. Set the SortForSQL parameter of your SQL query to EncodingSortforSQL(TableSort).

    • Was this article helpful?