Create a relationship between tables on a "LIKE" basis

Question

Hello,

I am starting this discussion just to see if you guys have any ideas on how to implement the  following scenario.

I have points layer “Locations” these points represent light poles in real life, in some cases a single pole is connected to several projects, and to reflect that I use commas to add more than project in the  Project ID field, this was I populate multiple project id’s for the same pole.

On the other hand, I have Projects table that stores specific data about each project.

Now the challenge is that I need to relate the 2 tables together so whenever a user selects a point on the map he can get a list of all projects connected to this point with their related data.

Here is an example:

for the Point #5: its referencing project B,D,C how do I relate this data to the highlighted rows in the Project tables. is there any workaround to do this?

Thanks a lot,

Ahmad

AhmadSALEH1 · Answer

Richard,

Thank you for the informative and detailed answer, I was able to figure this out using based on your answer. here is my method for future reference.

1- I created a third table, and started dumping the project ID and the object ID from the points layer, I did that using python, and striping out the project based on the comma.

2- Joint between the new table and the project table.

3- relationship between the new table and the points.

Thanks a lot,

Ahmad

RichardFairhurst · Answer

Your data is in a Many to Many relationship and you need to create a Many to Many relationship class to do this properly.  You would not store the relationship in either feature class, instead the Many to Many relationship class creates an intermediate table where each related project and pole are listed out like a normal One to One relationship (one record holds exactly one project related to one pole, so there are 4 records with a ProjectID of "A,B,C,D" with each pole in only one of the 4 records).  The intermediate table is held inside of the relationship class object in ArcCatalog, and you can drag that object into the map to see the intermediate table.  When you select any pole (say pole "A") and activate the relate you will get all of the related projects (Projects "A,B,C,D", "A,C" and "A,D").  For the data you have shown there would be 13 intermediate table records to relate the 6 projects to 4 out of the 7 poles (A,B,C and D).

Populating of the intermediate table is done through the Attribute pane.

A script could be written to create a standalone table with all of the records required for the intermediate table and then you could use that table to create and populate the relationship class with the Table to Relationship Class tool.

I believe you would then be free to use a truly unique ID for each project (say like an ObjectID, GlobalID, Attribute Assistant Generate ID or anything you like such as PROJ-2018-01, PROJ-2018-02, etc) as your Project ID that has nothing to do with the poles and still populate a list field on each project of the related poles (or a list on the poles of the related projects) derived from the relationship class table using a script.