How to Sort Localisation fields properly ?
Hi,
How can I sort my localisation fields in a proper order Zip Code > Street Name > Street Number , please ?
Currently, it runs following the Google Map french order : Street Number > Street Name > Zip Code, as the picture shows. I.E. I'd like the 2 blue framed entries to follow.
Looking forward to hear from you,
Regards
26 replies
-
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")
-
Thanks a lot @Sean
What if I want to extract the ZipCode into a separated field ?
Beside, is it possible to sort the location by the "street name only" (without considering if it's a street, a avenue, an alley, or else ...) ?
Looking forward to hear from you,
Regards
-
@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...
-
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")
-
Thank you very much @Sean for all this help
I appreciate a lot !
-
Hi Sean,
I'm confused to come back at you regarding this topic, but I've not found the way to / the field to insert your magic formula. Is in somewhere inside my Location Field, or should I create a filter, or what ?
Looking forward to hear from you.
Best Regards,
Julien
-
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
-
Hi Sean,
Thanks to you and your patient help, It works !
I still struggle with 2 things :
- the "rue de X", "rue de la X", "boulevard de X" and so... are displayed "de X", despite the formula with (?:rue de|rue de la). How can these prefix be "excluded" ?
- "bis numbers" adresses (1 rue X; 1 bis rue X) are not displayed at all.
Looking forward to hear from you,
Best Regards
-
-
Strange it works mostly, but not perfectly with me. Have I done something unproper ?
-
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 Sean,
I'm just discovering your answer, my apologie.
I'm not sure to understand what's the order requested to set the differents prefix right.
Does, let's say, " Allée de|Allée du|Allée de la|Allée des|Allée d'|Allée|" OK ?
Regards,
-
An other sorting "problem", the adresses street numbers come in a strange crescent order (check pic below).
Would you suggest something ?
Gratefully yours,
-
(same sorting appears when applying Crescent or the "Tri" Colomn)
-
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
-
You have got a nice explanation of the issue you encountered, now what about an actual solution? Here is my take at the magical one-liner (even as it is so long that it takes several lines to display it):
extractx("00000" + trim(text(Localisation)), "0*(\d{5})[\s,]*(?:(?:(?:avenue|boulevard|cité|impasse|passage|place|quai|rue|ruelle|square)\s+)(?:de la\s+|de l['’]|d[’']|de\s+|du\s+|des\s+)?)?(.+?)[\s,]+(\d{5})", "i", "$3$2 $1")
-
replacex("000" + trim(text(Localisation)), "^0*(?<num>\d{3})(?:\s*(?<bis>[a-z]|bis|ter)\s)?[\s,]*(?:(?:(?:avenue|boucle|boulevard|carrefour|chemin|cité|clos|cortil|cour|cours|drève|fond|galerie|impasse|montagne|passage|place|quai|rampe|rue|ruelle|sentier|square|terrasse|tienne|traverse|venelle|voie)\s+)(?:de la\s+|de l['’]|d[’']|de\s+|du\s+|des\s+)?)?(?<str>.+?)[\s,]+(?<zip>\d{5}).*", "i", "$<zip> $<str> $<num>$<bis>")
replacex(trim(", " + text(Localisation)), "^(?:(?<nsp>, )(?<num>\d+)|, )(?:\s*(?<bis>[a-z]|bis|ter)(?<bsp>\s))?[\s,]*(?<str>(?:(?:(?:avenue|boucle|boulevard|carrefour|chemin|cité|clos|cortil|cour|cours|drève|fond|galerie|impasse|montagne|passage|place|quai|rampe|rue|ruelle|sentier|square|terrasse|tienne|traverse|venelle|voie)\s+)(?:de la\s+|de l['’]|d[’']|de\s+|du\s+|des\s+)?)?.+?)[\s,]+(?<zip>\d{5}).*", "i", "$<num>$<bsp>$<bis>$<nsp>$<str>")
replacex(trim(text(Localisation)), "^\d*.+?[\s,]+(?<zip>\d{5})(?:[\s]+(?<cit>[^,]+))?.*", "i", "$<zip> $<cit>")
Content aside
- 1 yr agoLast active
- 26Replies
- 2627Views
-
3
Following