A common question customers ask is how to move edits between two geodatabase tables with attribute rules so both tables are in sync with each other. For example:
- If you insert a row in table 1, the row is also inserted in table 2.
- If you update a row on table 2, the attributes are populated for the same row in table 1. Same goes with deletes.
Of course, your specific conditions might different from this simple example. Attribute rules are flexible enough to allow that.
In this blog, I’ll walk through how to implement this logic and explain the process along the way. To skip ahead to the result, you can download the file geodatabase which contains the final rules authored below.
Here is what we are building.
Insert logic
To correctly apply changes and sync both tables, we need to uniquely identify the rows. Identifying unique rows in both tables is the main challenge of this task. Unfortunately, using the GlobalId or ObjectId won’t work because they are not under our control in Arcade and attribute rules.
The best route at out disposal is to create a unique user-defined ID field and populate it with a database sequence. We can then create an attribute rule that queries a sequence to get a unique value and store it in a user-defined ID field. The rule we create lives on both tables to guarantee the creation of unique rows on each insert and across both tables.
Let us call this the “sequence” rule. To re-iterate, this rule must exist on both tables and must query the same database sequence. With this rule, created rows are globally unique to both tables and can be identified with the ID field, which is important for the next step. We can use the NextSequenceValue to get a unique sequence.
return NextSequenceValue("seq")
With this, we can now write the logic to copy the rows to the other table. The logic to copy the rows is as follows:
- When we insert a row in table 1, we want to take the row and trigger an insert in the table 2 and vice versa.
Here is the problem however; the moment the second insert is triggered, it will turn around and insert the same row in the original table causing an unending loop. Attribute rules thankfully detect this type of issue and prevent an infinite loop, but we still need to address it in our Arcade logic.
This can be fixed by querying the other table before inserting the row using the following logic:
- Does the row with ID $feature.id exist in the second table? if not then call the insert by returning the adds dictionary, otherwise we exit the script with a return;.
Of course, the attribute rule on table 1 queries table 2 and vice versa, the attribute rule on table 2 queries table 1. I used the $editContext.editType to know that an insert event is executing.
if ($editContext.editType == "INSERT") {
//we just inserted a new row in this table, lets check if the id exists in the destination first(to prevent an infinite cycle)
var id = $feature.id;
var fs = FeatureSetByName($datastore, "table2", ["globalid"], false);
var destinationRow = first(Filter(fs, "id = @id"));
if (destinationRow != null) return; //the row exists, time to break the loop (additional logic here can be used to update if they are diffrent )
//the row isn't in the other table, lets add it.
return {
"edit": [
{
"className": "table2",
"adds": [{
"attributes": {
"id": $feature.id,
"field": $feature.field,
"field_1": $feature.field_1,
"field_2": $feature.field_2,
"field_3": $feature.field_3,
"field_4": $feature.field_4,
"field_5": $feature.field_5
}
}]
}
]
}
}
With this change, there is still another problem! The ID field will always be overwritten with a new sequence when the row is copied to the target table. This is a side effect from the sequence rule we created.
How do we differentiate an insert that is triggered from attribute rules vs an insert that the user performs? We can assume the user inserts the row without populating the ID field and that information is our key requirement to make this work.
- If the ID field is empty, generate a sequence, otherwise exit the sequence rule.
To further guard against any issues with this logic, the ID field can be set to read only in the project so users don’t accidently edit it.
Let’s update our sequence rule with this change.
//only create a unique id if none is provided
if (isempty($feature.id))
return NextSequenceValue("seq")
else
return;
The insert logic now works, let’s move on to the update.
Update Logic
Now that adding rows works, the attribute rules on both tables will ensure a row with ID X exists on both tables We now want to enable the ability to update an existing row and populate the properties for the same row in the second table. To do this, we can set an update trigger to the rule, and use $editContext to know if we are updating.
Knowing the edited row $feature.id from table 1, we query table 2 to find that row, if we can’t find it, something must have gone wrong so we just fail. This could happen if rows existed before the attribute rule is added. Otherwise, if no failure occurs we send an update dictionary to update the matching row on table 2. Remember, the same compensating attribute rule is present on table 2 also.
if ($editContext.editType == "UPDATE") {
//we are updating the row, in this table, so we need to get the corrsponding row in the second table and update it.
var id = $feature.id;
var fs = FeatureSetByName($datastore, "table2");
var destinationRow = first(Filter(fs, "id = @id"));
//this should not happen but catching it just in case
if (destinationRow == null) return {
"errorMessage": "table out of sync!, we tried to update a row that doesn't exist in the other table.. something is wrong."
} //we can relax that and create the row instead to sync it .
//we need to prevent the update if all fields are the same (otherwise we will be in an infinite cycle
if (destinationRow.field == $feature.field &&
destinationRow.field_1 == $feature.field_1 &&
destinationRow.field_2 == $feature.field_2 &&
destinationRow.field_3 == $feature.field_3 &&
destinationRow.field_4 == $feature.field_4 &&
destinationRow.field_5 == $feature.field_5) return; //no change quit
//else lets sync them!
return {
"edit": [
{
"className": "table2",
"updates": [{
"globalID": destinationRow.globalId,
"attributes": {
"field": $feature.field,
"field_1": $feature.field_1,
c "field_2": $feature.field_2,
"field_3": $feature.field_3,
"field_4": $feature.field_4,
"field_5": $feature.field_5
}
}]
}
]
}
}
Delete logic
This is probably the most straight forward of our tasks. If we delete a row in table 1 we want to also remove it from table 2 and vice versa. To do this we can add a third condition using $editContext
if ($editContext.editType == "DELETE") {
//we are delete the row, in this table, we need to delete the same row in the other table
var id = $feature.id;
var fs = FeatureSetByName($datastore, "table2", ["globalid"], false);
var destinationRow = first(Filter(fs, "id = @id"));
//this should not happen but catching it just in case
if (destinationRow == null) return {
"errorMessage": "table out of sync!, we tried to delete a row that doesn't exist in the other table.. something is wrong."
} //we can relax that and no-op..
//else lets sync them!
return {
"edit": [
{
"className": "table2",
"deletes": [{
"globalID": destinationRow.globalId
}]
}
]
}
}
Summary
In this blog we wrote an attribute rule that ensures all edits in one table are performed on another table and vice versa. You could enhance this logic as needed and add certain conditions to achieve the results you are after. You can also change the rule to work on feature classes by adding the geometry keyword in the return edit dictionary.
I hope this has been helpful. I’ve been asked several times at conferences how to approach this so, I thought it would be good idea to make it into a blog.
Download the file geodatabase with all rules demonstrated.
Article Discussion: