0

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

null
    • Sean
    • 4 yrs ago
    • Reported - view

    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

    • Sean
    • 4 yrs ago
    • Reported - view

    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.1
    • 4 yrs ago
    • Reported - view

    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

    • Sean
    • 4 yrs ago
    • Reported - view

    @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

    • Sean
    • 4 yrs ago
    • Reported - view

    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.

    • Sean
    • 4 yrs ago
    • Reported - view

    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.1
    • 4 yrs ago
    • Reported - view

    Thank you very much @Sean for all this help

    I appreciate a lot !

    • Julien.1
    • 3 yrs ago
    • Reported - view

    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

    • Sean
    • 3 yrs ago
    • Reported - view

    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

    • Julien.1
    • 3 yrs ago
    • Reported - view

    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

    • Sean
    • 3 yrs ago
    • Reported - view

    Screen Shot 2020-04-23 at 8.31.02 AM

     

    Screen Shot 2020-04-23 at 8.31.26 AM

    🤷‍♂️

    • Julien.1
    • 3 yrs ago
    • Reported - view

    Strange it works mostly, but not perfectly with me. Have I done something unproper ?

    • Sean
    • 3 yrs ago
    • Reported - view

    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.

    • Julien.1
    • 2 yrs ago
    • Reported - view

    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,

    • Julien.1
    • 2 yrs ago
    • Reported - view

    An other sorting "problem", the adresses street numbers come in a strange crescent order (check pic below).

    Would you suggest something ?

    Gratefully yours,

    • Julien.1
    • 2 yrs ago
    • Reported - view

    (same sorting appears when applying Crescent or the "Tri" Colomn)

    • Fred
    • 2 yrs ago
    • Reported - view

    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

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    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")

      • Julien.1
      • 9 mths ago
      • Reported - view

       Thanks Alain !
      As I get the idea, but don't really understand the syntax, how to add spaces between "75004Morland00016" and restrict to 3 digits street number ?

      Moreover, can I ask how to only display "# rue Y" without the Zip code + city, please ?

      Looking forward to hear from you,
      Best regards,

      • Alain_Fontaine
      • 9 mths ago
      • Reported - view

       First question:

      extractx("000" + trim(text(Localisation)), "0*(\d{3})[\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+)?)?(.+?)[\s,]+(\d{3,5})", "i", "$3 $2 $1")

      Second question, if I understand what you are asking :

      extractx("000" + trim(text(Localisation)), "0*(\d{3})[\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+)?)?(.+?)[\s,]", "i", "$1 $2")

      • Julien.1
      • 9 mths ago
      • Reported - view

       Thank you for your kind help.
      Fist formula works perfectly, a bug remains with Bis and Ter number. Is it solvable ?

      Regarding my second request, I'd like to display STREET NUMBER (without any 0) + ", " + STREET NAME (the previous second formula cuts multiple words street's name), without Zip Code or City Name.

      Looking forward to hear from you, and being at your disposal.
      Regards

      • Julien.1
      • 9 mths ago
      • Reported - view

       Moreover, a 3rd formula displaying only "Zip Code" + "City" would be necessary to fill 3rd line adress on enveloppes. I tried ... without success 😢
      Looking forward, and thanking you in advance 🙏

    • Alain_Fontaine
    • 9 mths ago
    • Reported - view

    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>")

      • Sean
      • 9 mths ago
      • Reported - view

       It’s nice to see that Ninox supports named capturing groups now. I tried it in the past and it didn’t work.

      • Alain_Fontaine
      • 9 mths ago
      • Reported - view

       One word of caution: on the iOS version, strangely, it works with replacex, but NOT with extractx. Go figure…

Content aside

  • 9 mths agoLast active
  • 26Replies
  • 2598Views
  • 3 Following