How to Sort Localisation fields properly ?

You would need to create a Formula field with the parsed Location field information and sort by the formula field. This formula works...

 

let myAddress := text(Location);
let addressRegex := "(\d+) (.+), (\d{5}).";
let myZip := extractx(myAddress, addressRegex, "$3");
let myStreetName := extractx(myAddress, addressRegex, "$2");
let myStreetNumber := extractx(myAddress, addressRegex, "$1");
myZip + myStreetName + myStreetNumber

The code in my previous post is a poor representation of the concise power of Regular Expressions and the functions that use them. The following line does the same thing...

 

extractx(text(Location), "(\d+) (.+), (\d{5}).", "$3$2$1")

@Julien,

You can extract the ZIP Code and copy it to a field using a button or a trigger. Without modifying the regex pattern you can simply use "$3" instead of "$3$2$1". I should have some time tomorrow (for me) to look into extracting just the street name. If you have some time you might want to look at this site for information on Regular Expressions...

 

https://www.rexegg.com/regex-quickstart.html

Julien,

This is one solution. I don't know if it's possible to use regex exclusively.

 

switch contains(text(Location), text(Location)) do
case contains(text(Location), "Bou"):
extractx(text(Location), "(\d+) Boulevard de (.+), (\d{5}).", "$3$2$1")
case contains(text(Location), "Qua"):
extractx(text(Location), "(\d+) Quai de la (.+), (\d{5}).", "$3$2$1")
case contains(text(Location), "Rue de"):
extractx(text(Location), "(\d+) Rue des (.+), (\d{5}).", "$3$2$1")
case contains(text(Location), "Rue du"):
extractx(text(Location), "(\d+) Rue du (.+), (\d{5}).", "$3$2$1")
end

 

You will need to add cases for street types that aren't there.

Yes, it can be done with regex exclusively...

 

extractx(text(Location), "(\d+) (?:Boulevard de|Quai de la|Rue des|Rue du) (.+), (\d{5}).", "$3$2$1")

Julien,

You can do it a couple of different ways. In my tests, I just added a formula field to the table and entered the formula there. You can also put the formula in the "Trigger after update" of the address field and copy the result of the formula to a text field.

Sean

 

I wasn't aware of all the possible prefix names. It is easily fixed though. Move all single word prefixes to the end of the list of compound prefixes that begin with the same word. For example, move "Boulevard" after "Boulevard des" and "Rue" after "Rue aux", etc. By default, regex reads from left to right.

Hi Julien -

 

The sorting is normal text sorting. Computers sort text by sorting by the first character of a string, then the second, etc. So with the following text:

 

112 Rue

18 Rue

110 Rue

 

A computer looks at the three text fields, sees that they all have 1 as their first character and doesn't do anything. Then it looks at the 2nd character and sees 1, 1, 8. So it sorts the 1's together then the 8.

 

112 Rue

110 Rue

18 Rue

 

Then looks at the third character and sees 0 and 2 and put the text with 0 first then 2. Which then changes the order to:

 

110 Rue

112 Rue

18 Rue