topic Re: Finding the records that participate in a relationship in Data Management Questions

Finding the records that participate in a relationship

HanliePretorius — Thu, 01 Oct 2020 14:16:27 GMT

Hi,

I had to replace one FC with another and at the same time I created another relationship class.

I started with old_property_fc and old_valuation_table with 1:M old_property_valuation_relationship.

Then I created new_property_fc and new_valuation_fc and 1:M new_property_valuation_relationship.

The trouble is that some of the features in new_valuation_fc seem to be related to features in old_property_fc. I think I know how it happened - operator error! - but now I want to find all the features in new_valuation_fc that are related to old_property_fc records.

Is there some way of doing this? Both FCs have 40 000+ records, so doing it manually will be an arduous task.

I'm comfortable with SQL if that helps.

Thanks

Re: Finding the records that participate in a relationship

DanaNolan — Mon, 26 Oct 2020 17:37:06 GMT

It sounds like you can join-by-attribute the old property records to the new feature class table, assuming the field that is used in the relationships to link tables is usable. When you start a join from the many side (table), it should find all the one side (feature) records that match.

If the link field cannot be used to match the records, you will have to look at other fields and/or clean up the data until you get a single field that can match old records to new features.

Re: Finding the records that participate in a relationship

HanliePretorius — Tue, 27 Oct 2020 06:00:29 GMT

Hi Dana,

Thanks for the reply.

In the end I used something similar to what you describe, except I did

it straight in SQL.

This is the code I used:

select vs.PropertyID

, vp.PROPERTY_ID as VALUATIONPROPERTY

, p.PROPERTY_ID as PROPERTY

from new_valuation_fc vs

left outer join

old_propery_fc vp

on vp.PROPERTY_ID = vs.PropertyID

left outer join

new_property_fc p

on vs.PropertyID = p.PROPERTY_ID

where p.PROPERTY_ID is null

That gave me everything in the new_valuation_fc linked to PropertyIDs

that do not exist in the new_property_fc.

I had some help from a colleague with this and it seemed to do the trick.

Cheers

Hanlie

2020-10-26 19:38 GMT+02:00, Dana Nolan <geonet@esri.com>:

Dana Nolan
replied
to the discussion
"Re: Finding the records that participate in a relationship"
To view the discussion, visit:
https://community.esri.com/message/961154-re-finding-the-records-that-participate-in-a-relationship?commentID=961154&et=watches.email.thread#comment-961154