1:M Join to Pull feature with Specific Attribute information

[JustinH]

Hello- 

I hope someone can help us with this data issue we are having. 

I have a Point Layer and a Polygon layer and I need to attach the Polygon Layer Attributes to the Point layer. However, there may be 3 polygons to one point. Currently, when I join the Polygons to the Points, it just randomly chooses one of the polygons to pull data from. We want to have a little more control on what data is being attached. 

We are mostly interested in one of the Fields from the Polygon layer. It is a binary layer basically saying if there is a boundary that was uploaded (Binary). There could be three polygons and they all have different attributes in this field (Ex: 0,0,1) or it could just be one polygon and we are not to worried about it. Our goal is to pull the data from the polygon that has a 1. If it does not have a one, print 0.   

I am having a hard time doing this with a Join, not sure if that is the way to approach this. In my head I want to just say, prioritize the join for when this field is = 1. Is there a way I can do this? 

I made a visual and I hope it is helpful. TIA!  

[JustinH]

Thanks, Bud. If this were to allow the Join field to access the "First" submission, I think it could work. My other work around was to dissolve the occurrences based on the Activity ID and then print the Max value. This has worked well but when I go to join the Data, It creates duplicates. I then delete the duplicates and still end up losing something small like 10 submissions. I am not totally positive how to work around it yet but that seemed to do the trick. 

[JustinH · Accepted solution]

 UPDATE: This works. Not sure what has happened while I was on vacation but it no longer duplicates rows. Dissolving the Polygon layer and summing the stats at Max does work to chose but this only works because I am using a binary field. 

Attached is my current workflow that has gotten me the closest, it does seem to work to make sure the attributes align with whether or not an Activity Id has at least one submission that has a Planned Boundary. The only problem is when I go to Join the tables, it creates duplicates every time. I then delete Identical and I end up losing 20 features in the process. I feel like this is the closest I have gotten. 

[JonathanNeal]

@JustinH Just in case it is needed, we also have the Add Spatial Join tool.  In the Pro 3.3 release we added a check box to permanently transfer the attributes; which seems suitable to your initial comment.

For @Bud's comments (Lots of thanks for continually bringing attention to things; definitely guiding the target we are shooting for.):
The Add Join tool has a long legacy and is quite suitable for the matching data in Pro.  All analysis of those matched data should first be persisted to some form of data storage is the standard expectation.  For any changes to Add Join we need to carefully change anything that many users are counting on for consistent behavior.  Fundamental user scripts could be relying on the "buggy", consistent behavior we have.  (Such as 1:first behavior between different workspaces).
My goals with Add Join are to keep it consistent/documented until the legacy of quirks is controllable with parameters that the users can adjust for themselves to suite.

[Bud]

@JonathanNeal 

“My goals with Add Join are to keep it consistent/documented until the legacy of quirks is controllable with parameters that the users can adjust for themselves to suite.”

Makes sense. For example, we have discussed Join — Control what related record gets used as a “controllable parameter” that would be very helpful.

[Bud]

Idea: Using a single tool: Sort within groupings, join, and fill gaps

[Bud]

@JustinH, I wonder if you could adapt @jcarlson's Arcade field calculator answer to suit your needs:

Field Calculation Between Related Tables - ArcGIS Pro

It shouldn't be too hard. In Arcade, you can access related tables with the function FeatureSetByRelationshipName, and the result of that function is already filtered to the related records. You'd need some way to filter the result to a single feature. Here's me just grabbing the first record in a related table.

var can2 = FeatureSetByRelationshipName($feature, 'Relate1', ['the_field_you_want'], false)

// get the first feature
var can2_feat = First(can2)

// return value
return can2_feat['the_field_you_want']

Just be sure you get the relationship name right in that first line, and ask for whatever fields you need to do your work. Assuming it's just pulling a single value, though, it's a pretty straightforward operation.

Maybe instead of getting the First() related value, you could get the max() related value. Just a blind guess.

[Bud]

Another option for anyone who’s interested:

Export the data to a mobile geodatabase. Create a database view using SQLite SQL to get the result that you want. SQLite is a fully functioning SQL dialect, unlike FGDB SQL.

This Oracle SQL query could be adapted to SQLite SQL:

--Oracle SQL
select
    t2.a2,
    t1.a1,
    case when t1.b1 is not null then t1.b1 else 0 end as b1
from
    table_2 t2
left join
    (select a1, b1, row_number() over (partition by a1 order by b1 desc) as rn from table_1) t1
    on t2.a2=t1.a1
where
    (t1.rn = 1 or t1.rn is null)

Source:  Using a single tool: Sort within groupings, join, and fill gaps