Return a value depending on if record in related table matches parent table

[chill_gis_dude]

I would like to return the values, good or bad, depending on if a value of a field in the related table matches a value in a field in the parent table. The two values are pressure in PSI and I am using the field maps web designer. Here is my code so far below

// load the related rows, using the name of the relationship class
var related_rows = FeaturesetByRelationshipName($feature, "GIS.SDE.tbl_Valve_FieldPressure")

// get the latest survey
var latest_survey = First(OrderBy(related_rows, "created_date DESC"))

// return a default value if no survey was found
if(latest_survey == null) {
    return "Not surveyed yet"
}

// return good or bad depending on PSI
if(latest_survey.InputPostValvePressure == latest_survey.TargetPSI) {
    return "Good"
}
return "Bad"

Essentially, surveyors are entering an input PSI in the related table, and then I want a calculated field in the parent layer to grab the latest survey's input PSI and compare it to a field in the parent layer called Target PSI which already has a value in it. If the value in input PSI matches Target PSI return good, if they don't match return bad.

The code seems to work in the web designer. but when I go in field maps to test it just says failed to calculate. Anyone have any idea?

[DougBrowning]

Could be two things.

1.  There was a bug for awhile with having a . in the relationship name when using FeaturesetByRelationshipName. I thought it was fixed but seeing others post on it.  (Is your Field Maps up to date?)   Try Filter(FeatureSetByName()) instead and see if that works.

2.  If you get nothing back from FeaturesetByRelationshipName then var latest_survey = First(OrderBy(related_rows, "created_date DESC")) will fail.  You should check if related_rows is empty before calling First not after.

Hope that does it

[chill_gis_dude]

Thank you Doug for your helpful comments. I've talked with others and it seems the . in the relationship name is still an issue in 11.2, which we are on. I did get it to work using the FeatureSetByName but it returns the latest related record of any feature rather than the one you interact with, so I am adding a filter where the parent id == child id. I'll post my code when I'm done.

[chill_gis_dude · Accepted solution]

Here is the finished code that works for me:

// Load the related rows using the FeatureSetByName function
var related_rows = Filter(FeatureSetByName($map, "Valve Field Pressure"),"Parent_GlobalID = '" + $feature.GlobalID + "'")

// Get the latest survey (sorted by created_date DESC)
var latest_survey = First(OrderBy(related_rows, "created_date DESC"))

// Check if PSI fields are valid (parent)
if($feature.TargetPSI == null) {
    return "No Target PSI in Valve Layer"
}

// Check if PSI fields are valid (child)
if(latest_survey.InputPostValvePressure == null) {
    return "Not Surveyed Yet"
}

// Return good or bad depending on PSI
if(latest_survey.InputPostValvePressure == $feature.TargetPSI) {
    return "Good PSI"
}
return "Adjust Pressure - See PDF for Instructions"

[DougBrowning]

I would still check related_rows for a count > 0 instead.  First() can fail on you here.

You also have 3 different return statements which would look better as elsif or something like that.

Also seems like this check should be first.  Why do a lookup if you may not need it.  Can get slow.

// Check if PSI fields are valid (parent)
if($feature.TargetPSI == null) {
return "No Target PSI in Valve Layer"
}