Arcade script to add unique value for duplicate field entries

[JustinBernard1886]

Hi everyone, 

I am casual user of arcade scripting and I wonder if anyone can help me with some script?

I have a simple script where when someone enters a value into a field, the script will populate another field with "1" at the end. 

What I need help with is when someone enters the same value again, I would like the duplicate value to have a "2" and increase for every subsequent duplicate value.  

Here is the script that I wrote initially.

var FieldName = $feature.StopID_text;
return Concatenate ($feature.StopID_text + "-" + "BB" + "-" + "1") ;

Thanks for the help!

Cheers,

Justin

[AlfredBaldenweck]

Basically you need to get a list of everything already in Field2.

Fair warning, this is my second time on this. The first time I crashed Pro when I added a new record and had to rewrite. Idk why but the rewritten version seems to work just fine.

// Get list of all the current values in Field
var self = FeatureSetByName($datastore, "test")
var field2List = []
for (var s in self){
    Push(field2List, s["Field2"])
}
// Create a counter and your output string
var counter = 1
var out = Concatenate($feature.Field1, "-BB-", counter)
// Be careful with the while loops. 
// Had to nuke Pro twice because I got stuck forever while writing this.
// No getting stuck on deployment, though
while (Includes(field2List, out)){
    // Update counter and the output so you can escape the loop.
    counter+=1
    out = Concatenate($feature.Field1, "-BB-", counter)
}
// Return the output
return out

[KenBuja]

I was looking at it another way, incrementing the counter for the unique values of `${$feature.Field1}-BB-`

I haven't tested it, but was thinking along these lines

var fs = FeatureSetByName($datastore, "yourLayer");
var sql = `${$feature.Field1}-BB-`;
var filtered = Filter(fs, "Field2 LIKE @sql");
if (Count(filtered) == 0) return `${$feature.Field1}-BB-1`;

var list = [];
for (var f in filtered) {
  Push(list, f["Field2"]);
}

function reverseSort(a, b) {
  if (a < b) return 1;
  if (a > b) return -1;
  return 0;
}

var highest = Sort(list, reverseSort)[0]
return `${$feature.Field1}-BB-${Number(Split(highest, "-BB-")[1])+ 1}`

In line 3, the FeatureSet is filtered to only show the existing values for that field. If there aren't any, it returns the string with the counter = 1 in line 4.

Lines 6-9 create a list of the values in Field2. Lines 11-15 are a reverse sorting function (taken from the Sort example) to get the highest value in line 17. Line 18 returns the string and the highest value incremented by one.

[AlfredBaldenweck]

I'd be interested in finding out how our two methods compare, performance-wise. I think yours may scale better?

[JustinBernard1886]

Thanks so much guys! 

I'll try both out and let you know how it goes.

[JustinBernard1886]

All right,

I;m trying the second code, and I am getting a "invalid where clause." on Line 4.

Here is the code:

var fs = FeatureSetByName($datastore, "GIS_Miway_LandingPad");
var sql = `${$feature.StopID_text}-LP-`;
var filtered = Filter(fs, "LIKE AssetID @sql");
if (Count(filtered) == 0) return `${$feature.StopID_text}-LP-1`;

var list = [];
for (var f in filtered) {
Push(list, f["AssetID"]);
}

function reverseSort(a, b) {
if (a < b) return 1;
if (a > b) return -1;
return 0;
}

var highest = Sort(list, reverseSort)[0]
return `${$feature.StopID_text}-LP-${Number(Split(highest, "-LP-")[1])+ 1}`

Thanks,

Justin

[KenBuja]

When posting code, use the "Insert/Edit code sample" button.

The syntax is incorrect on the Filter's sqlExpression

var filtered = Filter(fs, "AssetID LIKE @sql");

[JustinBernard1886]

Ahhh. 

Thanks.

The code almost works. The field populates but returns a duplicate value.

var fs = FeatureSetByName($datastore, "GIS_Miway_LandingPad");
var sql = `${$feature.StopID_text}-LP-`;
var filtered = Filter(fs, " AssetID LIKE @sql");
if (Count(filtered) == 0) return `${$feature.StopID_text}-LP-1`;

var list = [];
for (var f in filtered) {
  Push(list, f["AssetID"]);
}

function reverseSort(a, b) {
  if (a < b) return 1;
  if (a > b) return -1;
  return 0;
}

var highest = Sort(list, reverseSort)[0]
return `${$feature.StopID_text}-LP-${Number(Split(highest, "-LP-")[1])+ 1}`

[KenBuja · Accepted solution]

My apologies. I forgot to add a wildcard in the sqlExpression. Here's the tested code that works

var fs = FeatureSetByName($datastore, "GIS_Miway_LandingPad");
var string = `${$feature.StopID_text}-LP-`
var sql = `${string}%`;
var filtered = Filter(fs, "AssetID LIKE @sql");
if (Count(filtered) == 0) return `${string}1`;

var list = [];
for (var f in filtered) {
  Push(list, f["AssetID"]);
}

function reverseSort(a, b) {
  if (a < b) return 1;
  if (a > b) return -1;
  return 0;
}

var highest = Sort(list, reverseSort)[0]
return `${string}${Number(Split(highest, "-LP-")[1]) + 1}`

In my test data, I used the Habitat field

 

[JustinBernard1886]

That works!

Thank you so much Ken! ❤️