Relating Table A to Table D via Tables B and C

396
2
Jump to solution
05-18-2021 10:08 AM
Labels (2)
AlfredBaldenweck
Occasional Contributor III

Hey all, 

I'm trying to relate a series of tables to each other.

  • Table A is a list of documents
  • Table B is these documents and which plots of land they're relevant to (a many:many relationship)
  • Table C is the plots of land
  • Table D is the actual shapes for the land. Some parcels have been merged (So Parcel X and Parcel Y are in the same shape).

My strategy is to use Table B as the relationship class between A and C, then to relate C and D. 

Step 1 works, Step 2 is where this falls apart.  I looked at it again and I got the relationships working correctly.(5/19/21)

Is there a way to streamline the process so when I click on record in Table A, I get taken to the appropriate records in Table D?

Ideally I'd be able to also click on the plots of land (Table D) and have the list of associated documents (Table A) appear in the pop-up as well.

If there are any tips or ideas, I'd really appreciate them.

Thank you!

 

0 Kudos
1 Solution

Accepted Solutions
JohannesLindner
MVP Regular Contributor

Try selecting this option in all tables:

JohannesLindner_0-1624947507632.png

 

To show the relevant documents in the popup, you could use something like this expression:

// load the tables
var table_a = FeatureSetByName($datastore, "TableA")
var table_b = FeatureSetByName($datastore, "TableB")
// I think you don't need C
//var table_c = FeatureSetByName($datastore, "TableC")

// Get the document ids
var plot_id = $feature.PlotID
var filtered_b = Filter(table_b, "PlotID = @plot_id")
if(filtered_b == null || Count(filtered_b) == 0) {
  return "No documents"
}

var doc_ids = []
for(var b in filtered_b) {
  Push(doc_ids, b.DocumentID)
}

// get the document paths
var filtered_a = Filter(table_a, "DocumentID IN @doc_ids")
if(filtered_b == null || Count(filtered_b) == 0) {
  return "No documents"
}

var docs = []
for(var a in filtered_a) {
  Push(docs, a.DocumentPath)
}

// return paths
return Concatenate(docs, TextFormatting.NewLine)

 


Have a great day!
Johannes

View solution in original post

0 Kudos
2 Replies
JohannesLindner
MVP Regular Contributor

Try selecting this option in all tables:

JohannesLindner_0-1624947507632.png

 

To show the relevant documents in the popup, you could use something like this expression:

// load the tables
var table_a = FeatureSetByName($datastore, "TableA")
var table_b = FeatureSetByName($datastore, "TableB")
// I think you don't need C
//var table_c = FeatureSetByName($datastore, "TableC")

// Get the document ids
var plot_id = $feature.PlotID
var filtered_b = Filter(table_b, "PlotID = @plot_id")
if(filtered_b == null || Count(filtered_b) == 0) {
  return "No documents"
}

var doc_ids = []
for(var b in filtered_b) {
  Push(doc_ids, b.DocumentID)
}

// get the document paths
var filtered_a = Filter(table_a, "DocumentID IN @doc_ids")
if(filtered_b == null || Count(filtered_b) == 0) {
  return "No documents"
}

var docs = []
for(var a in filtered_a) {
  Push(docs, a.DocumentPath)
}

// return paths
return Concatenate(docs, TextFormatting.NewLine)

 


Have a great day!
Johannes
0 Kudos
AlfredBaldenweck
Occasional Contributor III

Thanks for replying to this! 

It took some figuring out, but this worked great.  A lesson for anyone else who has this problem in the future: your fields must be numbers, not strings, for the filters to work.

I'm running into the problem of when it tries to draw from a field with hyperlinks, it won't return anything. I'm going to make a separate question for that, though.

Thanks again!

Edit 7/22/21: Solved: Re: Using Arcade to return a hyperlink from anothe... - Esri Community

Edit 8/12/21: Sorting the output by Parcel Name 

0 Kudos