Arcade field calculation

[PeterMilenkovic]

Hi all,

I have a field capture project where I have buildings that contains establishments (businesses or other occupiers). It is conducted every 5 years so there is existing data that is verified as well as new data that is captured. We have a 3 character string field called 'EstablishmentID' businesses use a range from '001' to '499'. The process when adding new businesses is to use the next available number in the range, but when we get to the next available being '500' we have to search from '001' and find the first available within the range.

I managed to do this, but it is not very sophisticated. Just wondering if there is a better way

 // featureset filter to remove non business establishments 
 var business_establishments = filter(building_ests,"(ESTABLISHMENTID Not Like '5%%') and (ESTABLISHMENTID Not Like '6%%') and (ESTABLISHMENTID Not Like '7%%') and (ESTABLISHMENTID Not Like '9%%')")
 
 // calculating next available EstablishmentID number
 var next = number(max(business_establishments,'ESTABLISHMENTID')) + 1

// if next number is 500 then run loop       
if(number(right(concatenate('00',Text(next)),3)) == 500){
            // build array from 1 to 499
			var id = 1
            var ary =[]
            while(id < 500){
				push(ary,id)
				id+=1
            }
			// loop through featureset and remove establishmentids that are in array
            for(var e in rest_data){
				erase(ary,indexof(ary,number(e.establishmentid)))
				}
            // next establishment id is taken from first value in modified array
            var nextestid = right(concatenate('00',Text(first(ary))),3) 
}
// if next number is less than 500 use original next variable 			   
else{
var nextestid = right(concatenate('00',Text(next)),3)
}

Thanks

[JohannesLindner · Accepted solution]

A few notes:

• Line 2:
  • % is a wildcard for multiple characters, you need only one.
  • You forgot to exclude "8%".
  • You have leading zeros, so you can just do a string comparison: "ESTABLISHMENTID < '500'".
• Line 8:
  • next is already a number! you're needlessly converting to text and then back to number.
  • Your conversion is wrong. You always put two zeros before the number, resulting in "004", "0040", "00400".  I overlooked the Right(). Still, it's very cumbersome. You want Text(next, "000").
  • Instead of checking if next is 500, check if next is smaller than 500 and return it straightaway. That save you from writing and reading the big if block.
• Line 10-21: instead of building the array, completely editing it, and then returning the first element, just return the first number not found in the featureset.

Here's a simpler way to do this:

// filter only business establishments
var fs = Filter($featureset, "ESTABLISHMENTID < '500'")
// get the next available id
var next = Number(Max(fs, "ESTABLISHMENTID")) + 1
// if that id is under 500, return it
if(next < 500) { return Text(next, "000") }
// else loop over the ordered featureset
fs = OrderBy(fs, "ESTABLISHMENTID")
var last = 0
for(var f in fs) {
    // if there is a gap between the last checked and the current id, return the last id + 1
    var current = Number(f.ESTABLISHMENTID)
    if(current - last > 1) { return Text(last + 1, "000") }
    last = current
}
// if we land here, there are no gaps. return a default value
return "999"

[PeterMilenkovic]

Excellent thank you Johannes!

I didn't know that i could use Text() that way re number > string format.