# Arcade field calculation

474
2
Jump to solution
03-13-2023 02:41 PM
Occasional Contributor

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

Tags (4)
1 Solution

Accepted Solutions
MVP Frequent Contributor

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

Have a great day!
Johannes
2 Replies
MVP Frequent Contributor

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

Have a great day!
Johannes
Occasional Contributor

Excellent thank you Johannes!

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