Using NextSequenceValue to restart based on Intersecting feature

[ChrisGAEG]

Hi there! I understand NextSequenceValue needs a database sequence, but what if I want the count to restart based on where points are placed? So 3 points are placed in polygon A, I will have A1, A2, A3. And if 3 points are placed in polygon B, I will have B1, B2, B3 and so on.. Will I have to create a database sequence for every polygon I want to have a sequential count in? 

[JohannesLindner · Accepted solution]

Yes, you will have to create different database sequences.

Then you can do something like this:

var polygons = FeatureSetByName($datastore, "polygons", ["Name"], true)
var polygon = First(Intersects(polygons, $feature))
if(polygon == null) {
  return null
}
if(polygon.Name == "A") {
  return "A" + NextSequenceValue("SequenceA")
}
if(polygon.Name == "B") {
  return "B" + NextSequenceValue("SequenceB")
}
return null

[ChrisGAEG]

Johannes, 

Thanks for the reply! So far I am getting 'Arcade error: General evaluation error'.  Here is what I have - 

var polygons = FeatureSetByName($datastore, "Proposed_OLTLCP_Boundaries_copy", ["cab_id"], true)
var polygon = First(Intersects(polygons, $feature))
if(polygon == null) {
  return null
}
if(polygon.cab_id == "MQT-C05") {
  return Text(polygon.cab_id) + NextSequenceValue("MQT-C05")
}
return null

I'm not sure what the general error is but I have a feeling it's related to my return concatenation. 

[JohannesLindner]

Do you actually have a database sequence called "MQT-C05"?

https://pro.arcgis.com/de/pro-app/latest/tool-reference/data-management/create-database-sequence.htm

[ChrisGAEG]

I had left out the '-' in the sequence. Thanks a lot for your help!

[QuantitativeFuturist]

@JohannesLindner I am trying to adapt your code for my use case - I have 50 different polygons to grab the name from and then I have 2 separate sequences for each polygon, one for each subtype. I'm trying to do it dynamically so I don't have to code an if statement for each case. Any ideas?

var polygons = FeatureSetByName($datastore, "Metros", ["MetroCode"], true);
var polygon = First(Intersects(polygons, $feature))
if(polygon == null) {
  return null
}
if(polygon != null) {
var metcode = polygon.MetroCode
var seq = metcode+"_spec"
var id = NextSequenceValue(seq)
  return id+metcode
}
return null

Also many thanks for your contributions to the attribute rules community, almost every question I have you have a post with the solution. 

[JohannesLindner]

With so many sequences it will probably easier to use another approach:

// get intersecting polygon
var polygons = FeatureSetByName($datastore, "gewaesserkataster_sde.GWK_SDE_ADMIN.TestPolygons", ["TextField", "IntegerField"], true)
var poly = First(Intersects(polygons, $feature))
if(poly == null) { return null }

// get all points that intersect the same polygon, load the sequence field
var points = FeatureSetByName($datastore, "gewaesserkataster_sde.GWK_SDE_ADMIN.TestPoints", ["TextField"], true)
points = Intersects(points, poly)
points = Filter(points, "TextField IS NOT NULL")  // use only points with a sequence value

// get the next sequence count for this polygon (name and subtype)
// here, I chose "Name-Subtype-123" as schema
// edit the code according to your specification
var numbers = []
for(var p in points) {
    Push(numbers, Split(p.TextField, "-")[-1])
}
// if numbers is empty, max(numbers) returns -infinity
// that's why we have to max that again, so that we get 1
// if this is the first point in the polygon
var next_number = Max(Max(numbers), 0) + 1

// return the new sequence value
// again, edit according to your specification
return Concatenate([poly.TextField, poly.IntegerField, next_number], "-")

polygons.TextField corresponds to your MetroCode, polygons.IntegerField is the subtype field.

points.TextField is the field that stores the sequence values in the point fc.

Pros of this approach:

• easy for large numbers of needed sequences
• flexible: you don't have to change any code to add more polygons or subtypes

Cons of this approach:

• will be slower if you have many polygons or points
• doesn't return unique ids. if you delete the point with the highest sequence number and create a new one, the new will have the sequence value of the old
  • e.g.: delete F-3-2 and create a new point in F3. the new point will again be F-3-2; with a database sequence you would get F-3-3

Also many thanks for your contributions to the attribute rules community, almost every question I have you have a post with the solution.

Thanks, glad to know that it helps people.

[QuantitativeFuturist]

@JohannesLindner  Thanks so much for your reply, I'm afraid that I didn't explain my use case sufficiently.

I have a point feature class with subtypes. For subtype 1, when a point is created inside a polygon with a name "POLYA" I want to set the unique ID on the point to be 001POLYA and then increment 002POLYA, 003POLYA. The same subtype when creating a new feature that intersects with POLYB would create unique IDs like 001POLYB, 002POLYB etc. The polygons are not subtyped, the POLYA, POLYB etc come from a text field with the name of each polygon being read from that field.

Where the other point subtype comes in to play: When the subtype 2 is used to create a point it will have a unique ID set similarly to subtype 1 except the order is switched: If a subtype 2 point is created within POLYA it would receive the ID: POLYA001, POLYA002 (i.e. a new sequence but the concatenation is reversed. so a new point intersecting POLYB would have an id POLYB001, POLYB002.

Where it really get's interesting is if we have a point in POLYA which is subtype 1, it's id would be 009POLYA and there are existing points of subtype 2 also in the polygon with the highest ID being POLYA003. If we switched the subtype of 009POLYA from subtype 1 to subtype 2, we would like the id to change to POLYA004.

I was able to write python code to create all of the database sequences (over 100) to keep track of all of the different subtype/polygon combinations. Not sure if having so many sequences will have a negative effect on the database performance.

I'm not even sure if this is possible and understand that it could be overly complex for this discussion. Even a nudge in the right direction or tell me I'm crazy would be much appreciated!

Thanks again!

[JohannesLindner]

Ah, the points are subtyped, got it.

Well, you apparently already did the hard part of creating the sequences. The Attribute Rule itself is quite easy:

// if we're updating this $feature, but not its subtype field, return
if($editcontext.editType == "UPDATE" && $feature.IntegerField == $originalfeature.IntegerField) {
    return $feature.TextField
}

// get intersecting polygon
var polygons = FeatureSetByName($datastore, "TestPolygons", ["TextField"], true)
var poly = First(Intersects(polygons, $feature))
if(poly == null) { return null }

// get next sequence value based on polygon name and point subtype
// if that sequence doesn't exist, this will give an error!
var seq_name = poly.TextField + "_" + $feature.IntegerField
// get and format next sequence value
var seq_value = Text(NextSequenceValue(seq_name), "000")

// return the id with the order according to the point's subtype
if($feature.IntegerField == 1) {
    return seq_value + poly.TextField
}
return poly.TextField + seq_value

Not sure if having so many sequences will have a negative effect on the database performance.

Me neither. The reason I suggested the approach in my previous post was the work of creating and maintaining the database sequences. The Rule in this post will fail if you create new polygons or new subtypes without also creating the corresponding database sequences: 

[QuantitativeFuturist]

@JohannesLindner Thank you so much, it works perfectly you're a legend!

In the hopes that it might help someone else create database sequences using arcpy here is my code for that:

import arcpy

def main():

    fgdb = r'C:\pathtoyourdb.gdb'

    # Get all of the codes to build the sequences from
    table = r'C:\pathtoyourdb.gdb\yourFeatureClass'
    field = 'YourIDField'

    with arcpy.da.SearchCursor(table, [field]) as cursor:
        myValues = sorted({row[0] for row in cursor})

    print (myValues)

    for value in myValues:
        arcpy.CreateDatabaseSequence_management(fgdb,
                                        value+"_1", 1, 1)

    for value in myValues:
        arcpy.CreateDatabaseSequence_management(fgdb,
                                        value+"_2", 1, 1)

    sequences = arcpy.da.ListDatabaseSequences(fgdb)

    for seq in sequences:
        print('Sequence name: {}'.format(seq.name))
        print('Sequence start value: {}'.format(seq.startValue))
        print('Sequence increment value: {}'.format(seq.incrementValue))
        print('Sequence current value: {}'.format(seq.currentValue))

if __name__ == '__main__':
    main()

Once again, can't thank you enough for your help it is very much appreciated!