Blog

Data - How to apply customize sort sequences with SQL and Power Apps

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.
https://en.wikipedia.org/wiki/Alphabetical_order

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
AS 
SELECT 
	[Name] COLLATE Traditional_Spanish_CS_AI AS [Name], 
	Surname COLLATE Traditional_Spanish_CS_AI AS [Surname]
FROM 
	Employee

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.

SortByColumns(vwEmployee, "Surname")

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'.


Conclusion

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.
  •   Categories: 
  • sql
Previous
Next
Related posts