Attribute rule not working with merge to update table

[DeanAnderson2]

I am using ArcPro/Fabric 3.4 in a geodatabase.   When a parcel is made historic (and no longer exists in current) I want add that parcel number to a cancelled number table.  My three use cases are as follows. Two work well but I can not get the third to work.  Any help would be greatly appreciated. 

1. If  you set a Taxlot (our name for parcel) historic it should automatically update the cancelled number table. (this works) 

2. If you duplicate a Taxlot and set it historic it will not update the cancelled number table. (this works) 

3. If you merge two Taxlots and keep original features on so the "new" taxlot has the attributes of one of the parents then the cancelled number table should have one record in the cancelled number table for the taxlot that has been retired and no longer exists in current. .  (this does not work - I get two records in the cancelled number table one record for both parents). 

My code for the attribute rules (executes on update trigger, exclude for application eval, and trigger field is RetiredByRecord) is as follows: 

// If a taxlot is set historic and no longer exists in current taxlots then the taxlot and maptaxlot are added to the cancelled number table.

var fsTaxlot = FeatureSetByName($datastore, "Taxlot ['MapTaxlot','RetiredByRecord','CreatedByRecord'],false)

var where = Concatenate(['MapTaxlot = ',"'", $feature.MapTaxlot,"'"," and RetiredByRecord is Null"])

var fsTaxlotFilter = filter(fsTaxlot,where)
var firstFilter = First(fsTaxlotFilter)

if (!isempty(firstFilter)) return

//if (!isempty(filter(fsTaxlot,where))) return

return {
"edit": [{
"className": "CancelledNumbers",
"adds": [{
'attributes':
{
"Taxlot": $feature.Taxlot,
"MapNumber":$feature.MapNumber
}
}]
}]
}

[RPGIS]

Hi @DeanAnderson2,

A couple of things.

1. You have a missing double quotes in the feature class name but here is a more simplified version of your code.
2. Please use the  when adding code so it makes is easier for others to see what your code is supposed to look like.

var Taxlot = $feature.MapTaxlot
var fsTaxlot = FeatureSetByName($datastore, "Taxlot", ['MapTaxlot','RetiredByRecord','CreatedByRecord'],false)

var where = Concatenate(["MapTaxlot = @Taxlot AND RetiredByRecord is Null"])

var fsTaxlotFilter = First(filter(fsTaxlot,where))
if (TypeOf(fsTaxlotFilter) == 'Feature'){
	return {
		"edit": [{
			"className": "CancelledNumbers",
			"adds": [{
				'attributes':
					{
					"Taxlot": $feature.Taxlot,
					"MapNumber":$feature.MapNumber
					}
				}]
			}]
		}	
	}

[DeanAnderson2]

Thanks for the code and sorry I always forget to set the formatting correctly.  The code provided does not work for my first use case.  When I select a taxlot and set it historic it does not update the cancelled number table.  By changing the conditional to be "if (TypeOf(fsTaxlotFilter) != 'Feature')" it works great for my first two use cases but again did not work when using the merge tool.  It also did not work with Merge Tool when I left it at "if (TypeOf(fsTaxlotFilter) == 'Feature')"

[RPGIS]

With that information it makes it easier to help you get to what your after. So there are a couple of ways to go about it. 

1. Modify the code to only update the table when a field is changed to whatever value to indicate which parcel is deprecated to trigger the update to the table and then merge the two layers afterward.
2. Modifying the same code above but set it to filter the featureset( $featureset ). If the count for the filtered result is zero then set to update using the original ID and whichever values you specify. 

[AmirBar-Maor · Accepted solution]

@DeanAnderson2 

I presume you use this option when you merge (with an active record)

This 'preserves' the attribute of parcel 'A' but retires the original parcels 'A' and 'B'.

Your code will be triggered twice, for the historic A and B.

The issue is the 'order of operations': the parcels are marked historic before the new parcel 'A' is committed to the table. This is the reason you are getting 2 cancelled taxlots instead of the desired outcome of a single canceled parcel 'B'.

We could look into changing this in the tool itself, but the new order of operation might not work for someone else (e.g. - making sure there are not 2 parcels with the same name).

The only solution I can think about is to add another rule that is triggered on insert of a new parcel. If the new the new parcel name exists in the historic parcels, delete it from the cancelled parcel name table.

[DeanAnderson2]

That makes sense.  My tests with just insert as the trigger adds the record.  I have been working on a rule that deletes the record in the second table if it should not be.  I was worried that this was kind of messy but that sounds like the best way to go.   Thanks ! 

[DeanAnderson2 · Accepted solution]

This worked (I had to learn how to create a rule to delete a record in another table).  As I have learned there are probably better ways to do this but it works, so that is a relief.  Thanks for the help! 

For reference here are the two rules... 

Rule 1:  Update cancelled number table on retire

var Taxlot = $feature.MapTaxlot 
var fsTaxlot = FeatureSetByName($datastore, "Taxlot", ['MapTaxlot','RetiredByRecord','CreatedByRecord'],false)

var where = Concatenate(["MapTaxlot = '", $feature.MapTaxlot,"' AND RetiredByRecord is Null"]) 
var fsTaxlotFilter = First(filter(fsTaxlot,where)) 

if (TypeOf(fsTaxlotFilter) != 'Feature'){ 
return { "edit": [{ "className": "CancelledNumbers", 
"adds": [{ 'attributes': 
        { "Taxlot": $feature.Taxlot, "MapNumber":$feature.MapNumber } }] 
}] 
}
}

Rule 2: Delete In Cancelled Number Table 

var fsTaxlot = FeatureSetByName($datastore, "Taxlot", ['MapTaxlot','RetiredByRecord','CreatedByRecord'],false)
var fsCanNums = FeatureSetByName($datastore, "CancelledNumbers", ['MapNumber','Taxlot','GlobalID'],false)

var whereT = Concatenate(["MapTaxlot = '", $feature.MapTaxlot,"' AND RetiredByRecord is Null"]) 
var whereC = Concatenate(["MapNumber = '", $feature.MapNumber,"' AND Taxlot = '",$feature.Taxlot,"'"]) 

var fsTaxlotFilter = First(filter(fsTaxlot,whereT)) 
var fsCanNumsFilter = First(filter(fsCanNums,whereC)) 

if ((TypeOf(fsCanNumsFilter) == 'Feature') && (TypeOf(fsTaxlotFilter) == 'Feature')) { 
var GlobalIDToDelete = [{'GLOBALID': fsCanNumsFilter.GlobalID}]

return {
"edit": [{
    "className": "CancelledNumbers",
     'deletes': GlobalIDToDelete
    }]
}
}

[AmirBar-Maor]

@DeanAnderson2  - well done

Yes - Attribute Rules can also delete rows in another table.

This is an interesting use case (keeping a list of cancelled parcel numbers). I wonder if this is applicable to many other counties in the US.

Maybe something to consider @ChrisBuscaglia 

[RPGIS]

Hi @DeanAnderson2,

The workflow you are showcasing is a bit odd. Not saying that it is a bad workflow but it seems like you are updating a table with an ID to only delete the same ID. Is the ID of any value or what is the ultimate reason for updating the table of retired IDs?

[DeanAnderson2]

Yep it is strange. The second rule does delete a record in the cancelled number table.   It does this because of how the MERGE tool works (as explained in Amir's response)  there is no way for me to update the cancelled number table correctly in one rule.  Based on the merge option selected, which I need to use, it will always incorrectly adds a record for one of the parents that will still exist in active/current and is also retired.  So, the second rule fixes this problem.  Fortunately, the second rule does not interfere with my other use cases so it works out ok.   I appreciate you checking and help with the code.  Its kind of a strange problem. 

As to why this is needed....  In Oregon, taxlot numbers are sequentially assigned per taxlot map.  The combination of the taxlot number and map number make a unique key that is used to link the map to the Assessment and Taxation System.  The table of cancelled numbers contains both the mapnumber and taxlot. When a cartographer creates a new taxlot on a map they do not want to use a number that has been previously  cancelled as this taxlot number (and associated taxlot account) still exists as a historical record in the Assessment and Taxation System. Now that I can maintain this table accurately, I will see if the cartographers want an insert attribute rule for taxlots that checks this table automatically, rather then them checking manually.  It would be pretty simple to do.  

The reason taxlot numbers are not always deleted (replaced) when this type of action occurs dates back to when we had a manual system.  Every time a taxlot was created staff had to create a new taxlot packet (a big job).  It was  whole lot faster to keep a taxlot (and its packet)  and just update the information in the packet that needed to be updated.  In the 80's-90's when A&T computer systems were made this practice was kept because of time and space savings on those expensive computer disks.  The process is pretty engrained so doing what we do now is a standard business practice that won't be changed anytime soon (we still need those historical taxlot accounts to support a number of business activities). 

Sorry for the complicated answer.