Expression to update parent record when related table is updated

463
7
Jump to solution
02-05-2024 09:29 AM
MikhaylaB
Occasional Contributor

I have a parent layer called hydrants with a field named "Class" and a related table called Inspections with the relevant field named "GPM". The "Class" field accepts values 'Blue-1500', 'Green-1000-1499', 'Orange-500-999', and 'Red-0-499', and the "GPM" field in the related table accepts integers 0-1500.

 

I need help producing an arcade expression to update "Class" based on the "GPM" value when a new record is added to the related table via an attribute rule. 

0 Kudos
2 Solutions

Accepted Solutions
MikhaylaB
Occasional Contributor

I was unable to use GlobalID to GUID for this, not sure what the hang-up was. If anyone comes across this and has a similar issue, I created a relationship class based on ObjectID to an integer field manually populated with that ID, hydrant ID's in my situation. The relationship forward path was set to my parent class. The rule's field is the integer field. The code is as follows: 

var parent_id = $feature.hydrantOBJID ;


var parent_class = FeatureSetByName($datastore, "Fire.SDE.Hydrants", ["OBJECTID", 'class'], false);
var parent_records = Filter(parent_class, "OBJECTID = @parent_id");

var updates = [];
var i = 0;
var new_value = 'no gpm';

if ($feature.GPM >= 1500) {
new_value = "Blue - 1500 GPM or Greater";
}
else if ($feature.GPM >= 1000) {
new_value = "Green - 1000-1499 GPM";
}
else if ($feature.GPM >= 500){
new_value = "Orange - 500-999 GPM";
}
else {
new_value = "Red - 0-499 GPM";
}

 

for (var row in parent_records) {
// If the parent row is null or has a different value, updated it
if (IsEmpty(row['class']) || row['class'] != new_value)
{
updates[i++] = {
'OBJECTID': parent_id,
'attributes': {"class": new_value}
};
}
}


return {
'result': parent_id,
'edit': [
{'className': 'Fire.SDE.Hydrants',
'updates': updates
}
]
};

View solution in original post

0 Kudos
7 Replies
MikeMillerGIS
Esri Frequent Contributor
0 Kudos
MikhaylaB
Occasional Contributor

That looks similar to the expression I've been cobbling together, do you think this looks almost correct? I'm not good enough with arcade to tell where I'm fumbling. 

MikhaylaB_0-1707157466750.png

 

0 Kudos
MikeMillerGIS
Esri Frequent Contributor

Line 1: You FeatureSetByName should have a list of fields passed as the third param

Line 4:  relatedInspections.map,  I do not know what that dies.  That is a FeatureSet.  You should call first or loop over the featureset and get a list of all the GPM Values.  Then you can call Max on it

Your return statement, just return the value.  If you set the rule on a field, it will map it to the field

0 Kudos
MikhaylaB
Occasional Contributor

I have tried to implement the referenced script, and I don't receive any errors but it doesn't update the parent table. I'm not sure if I've made a mistake in the code or if it is my data setup. I have tried the relation ship class with GlobalID to GlobalID, Global to GUID, and the original ObjectID to hydrantOBJID.

Parent Table:

MikhaylaB_0-1707497093316.png

Related Table:

MikhaylaB_1-1707497198041.png

 

Code:

var parent_id = $feature.parentglobalid ;
parent_id = Upper(parent_id);


var parent_class = FeatureSetByName($datastore, "hydrantCopy", ["GlobalID", 'class'], false);
var parent_records = Filter(parent_class, "GlobalID = @parent_id");

var updates = [];
var i = 0;
var new_value = '';

if ($feature.GPM >= 1500) {
new_value = "Blue - 1500 GPM or Greater";
}
else if ($feature.GPM >= 1000) {
new_value = "Green - 1000-1499 GPM";
}
else if ($feature.GPM >= 500){
new_value = "Orange - 500-999 GPM";
}
else {
new_value = "Red - 0-499 GPM";
}

 

for (var row in parent_records) {
// If the parent row is null or has a different value, updated it
if (IsEmpty(row['class']) || row['class'] != new_value)
{
updates[i++] = {
'GlobalID': parent_id,
'attributes': {"class": new_value}
};
}
}


return {
'result': parent_id,
'edit': [
{'className': 'hydrantCopy',
'updates': updates
}
]
};

 

0 Kudos
MikeMillerGIS
Esri Frequent Contributor

The code is using parentglobalid, but in your screen shots that is blank.  You need to adjust the code and the sql statement in the Filter(FeatureSet...) to match how your data is set up.

0 Kudos
MikhaylaB
Occasional Contributor

Do you have any insight into how I could combine the below code into one attribute rule? I need GPM to be calculated by a value entered into the Flow field on the same table and update the parent class accordingly.

//Calculate GPM

var flow = $feature["Flow"];
var gpm = 0;


if (!isEmpty(flow)) {
gpm = (29.7 * (2.5 * 2.5) * Sqrt(flow) * 0.9)
}
return gpm;

 

//Update Parent Class

var parent_id = $feature.hydrantObjid ;


var parent_class = FeatureSetByName($datastore, "hydrantCopy", ["OBJECTID", 'class'], false);
var parent_records = Filter(parent_class, "OBJECTID = @parent_id");

var updates = [];
var i = 0;
var new_value = 'no gpm';

if ($feature.GPM >= 1500) {
new_value = "Blue - 1500 GPM or Greater";
}
else if ($feature.GPM >= 1000) {
new_value = "Green - 1000-1499 GPM";
}
else if ($feature.GPM >= 500){
new_value = "Orange - 500-999 GPM";
}
else {
new_value = "Red - 0-499 GPM";
}

 

for (var row in parent_records) {
// If the parent row is null or has a different value, updated it
if (IsEmpty(row['class']) || row['class'] != new_value)
{
updates[i++] = {
'OBJECTID': parent_id,
'attributes': {"class": new_value}
};
}
}


return {
'result': parent_id,
'edit': [
{'className': 'hydrantCopy',
'updates': updates
}
]
};

0 Kudos
MikhaylaB
Occasional Contributor

I was unable to use GlobalID to GUID for this, not sure what the hang-up was. If anyone comes across this and has a similar issue, I created a relationship class based on ObjectID to an integer field manually populated with that ID, hydrant ID's in my situation. The relationship forward path was set to my parent class. The rule's field is the integer field. The code is as follows: 

var parent_id = $feature.hydrantOBJID ;


var parent_class = FeatureSetByName($datastore, "Fire.SDE.Hydrants", ["OBJECTID", 'class'], false);
var parent_records = Filter(parent_class, "OBJECTID = @parent_id");

var updates = [];
var i = 0;
var new_value = 'no gpm';

if ($feature.GPM >= 1500) {
new_value = "Blue - 1500 GPM or Greater";
}
else if ($feature.GPM >= 1000) {
new_value = "Green - 1000-1499 GPM";
}
else if ($feature.GPM >= 500){
new_value = "Orange - 500-999 GPM";
}
else {
new_value = "Red - 0-499 GPM";
}

 

for (var row in parent_records) {
// If the parent row is null or has a different value, updated it
if (IsEmpty(row['class']) || row['class'] != new_value)
{
updates[i++] = {
'OBJECTID': parent_id,
'attributes': {"class": new_value}
};
}
}


return {
'result': parent_id,
'edit': [
{'className': 'Fire.SDE.Hydrants',
'updates': updates
}
]
};

0 Kudos