0

Is it possible to split the Location field to several address formula fields in Ninox?

I want the Location filed to split and copy the address to several fields like Street, ZIP, City etc, is that possible?

6 replies

null
    • Paul_Chappell
    • 2 yrs ago
    • Reported - view

    Yes.  This should put you on the right track.  You just need to split the Location field into an array. To  show how this works create a formula field and expand it over several lines. Then paste this and change Location to your location field:

    let tempArray := split(text(Location), ", ");
    let tempFullAddress := item(tempArray, 0) + "
    " + item(tempArray, 1) + "
    " + item(tempArray, 2) + "
    " + item(tempArray, 3) + "
    " + item(tempArray, 4) + "
    " + item(tempArray, 5);
    tempFullAddress

    You just need to move each array line into a different field for each line of the address.

      • Producer, Manager
      • Jan_Inge_Iversen
      • 2 yrs ago
      • Reported - view

      Paul Chappell 
      Thanks a lot for the replay and the good solution. 馃憤馃檪
      But what do you think is the best way to separate zip from city, cause it seem to come together in this solution?

      • Paul_Chappell
      • 2 yrs ago
      • Reported - view

      Jan Inge Iversen Split the line that contains the city and code using a space. Assuming it is always the same line.  If it's not always the same line then you can't unless you split every line and check if each part is a zip code.  The only proper solution is to use an address API, but that is a completely different world beyond this forum.

    • Ninox partner
    • RoSoft_Steven.1
    • 2 yrs ago
    • Reported - view

    Need one more split() function because the zip and city are separated by a space.

    item(split(text(Location), ","), 0) + "
    " + split(item(split(text(Location), ","), 1)," ",0)+ "
    " + split(item(split(text(Location), ","), 1)," ",1)+ "
    " + item(split(text(Location), ","), 2)

    Steven.

    • Ninox developper
    • Jacques_TUR
    • 2 yrs ago
    • Reported - view

    You can also use the extractx function to retrieve the different valuers.

    Here are the two fields that are in the form: 

    And here is the formula to extract the values and create a JSON variable that contains the results:

    parseJSON(extractx(text(Location), "(.*),(.*\d) (.*),(.*)", "{""address"":""$1"",  ""zipCode"":""$2"", ""city"":""$3"", ""country"":""$4""}"));
// return JSON values : {"address":"56 Av. des Baleares","zipCode":" 66100","city":"Perpignan","country":" France"}

    See the extractx function in the documentation and this tool to create and test regular expressions

      • Producer, Manager
      • Jan_Inge_Iversen
      • 2 yrs ago
      • Reported - view

      Jacques TUR 

      Thanks, I will check that out! 馃憤馃檪

Login to reply

Content aside

  • 2 yrs agoLast active
  • 6Replies
  • 255Views
  • 5 Following