Data - How to apply customize sort sequences with SQL and Power Apps
May 1. 2021
In cases where we need to display non English data, there can be the requirement to sort data using langauge specific sort sequences. With SQL Server data sources, we apply custom sort sequences through collation settings, and this post describes how to apply this technique to a Power App.
A benefit of using SQL Server as a data source, is that it's possible to apply language specific sort sequences.
On occasions where we need to apply a specific sort sequence, this is something that is difficult to carry out through other means from Power Apps.
A good introduction is the following Wikipedia article which summarises sort conventions for latin based languages.
A popular example of a different sort sequence is that in traditional Spanish, "CH" and "LL" are treated as single letters. Therefore, these three words would be sorted in following sequence: lomo, luz, llama. (notice how llama comes last).
To demonstrate how to apply a language specific sort sequence to Power Apps, lets take the example of a table of Employees. The requirment is to sort this data using the traditional Spanish sort sequence.
If the collation of the database or the Employee table is set to traditional Spanish, Power Apps will apply the expected sort sequence. If not, we can create a view to apply our desired sort sequence like so:
CREATE VIEW vwEmployee
[Name] COLLATE Traditional_Spanish_CS_AI AS [Name],
Surname COLLATE Traditional_Spanish_CS_AI AS [Surname]
Notice how we use the T-SQL Collate function to apply the traditional Spanish collation. From Power Apps, we can now add this view to an app through the data panel.
Let's suppose we want to display a list of employees that are sorted by surname. The screenshot beneath shows how we can accomplish this by setting the Items property of a data table to a call to the SortByCoumns function.
Because this is a delegable expression, SQL Server applies the sort sequence and the results will appear in the data table control as expected. Notice how 'Chávez' appears correctly, after 'Conner'.
When we display non English data, there can be the requirement to sort data using langauge specific sort sequences. With SQL Server data sources, we can accomplish this by setting the collation sequence of the data, and we can use views to convert the collation sequence of data.
- Dataverse - How to connect to SQL Server in Model Driven/Portal apps through virtual tables
- Forms - How to add and edit records against SQL Server tables GUID primary keys
- SQL - What to do when edit/add/delete options missing from an app, or edit controls unavailable in forms
- SQL - How to configure case sensitive or case insensitve searches against SQL Server data sources
- SQL - Caution - the Distinct function may not return the records that you expect!
- SQL - How to lookup/display related details with SQL Server Views
- SQL - How to show distinct values in the most efficient way
- Dataverse - How to access data more easily through TDS
- SQL - What you need to know about bulk updating records