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

Producer, Manager
Jan_Inge_Iversen
3 yrs ago
6replies

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

- Paul_Chappell
- 3 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
  
  3 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
  
  3 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
- 3 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
- 3 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
  
  3 yrs ago
  
  Reported - view
  Jacques TUR
  
  Thanks, I will check that out!