Edit a query layer that includes a join

DavidSnell

I have a query layer that is this:

select r.addr_id, r.roll_num, r.addr_line, r.poly_id, a.poly_cnt, a.shape from [DATA].[GIS].[roll_addr] r

inner join [DATA].[GIS].[parcel_poly] p on p.roll = r.roll_num

where r.poly_id is null

 

I am unable to edit it at all in ArcGIS Pro 2.7 nor in ArcPortal when I publish this query layer. 

The work flow here is that I want to be adding the parcel poly_id to the address table where it doesn't have one. This works best when I can highlight them on a map. 

 

Any help would be apricated. 

JohannesLindner

If you mean you can't edit feature attributes or add/delete features, then yes, that is impossible, because query layers and database views are readonly.

If you have to do your workflow manually, you can add this query layer to your map to show you where problematic features are, but you have to edit the original feature class.

If you don't have to do it manually, it seems that this can be easily done with the Calculate Field tool. Use Arcade as language, edit and copy this code:

// if poly_id is already there, just return it
if($feature.poly_id != null) {
    return $feature.poly_id
}

// load the parcel_poly fc
var fs_parcel_poly = FeatureSetByName($datastore, "DATA.GIS.parcel_poly", ["roll", "correct_id_field"], false)
// filter parcel_poly by "roll = roll_num"
var roll_num = $feature.roll_num
fs_parcel_poly = Filter(fs_parcel_poly, "roll = @roll_num")

// if there is a related parcel_poly, return its parcel id
var parcel_poly = First(fs_parcel_poly)
if(parcel_poly != null) {
    return parcel_poly.correct_id_field
}

// else return null
return null