Bringing up selected records in a popup, sorted by another table (Arcade)

[AlfredBaldenweck]

This is a follow-up to this post. 

I have a 4 tables.

• 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 a list of plots of land
• Table D is the actual shapes for the land. Some parcels have been merged (think Vermont, New Hampshire, and Maine are all one shape now.)

The code provided in that solution works great, but I was wondering if I could take it further.

Is it possible to sort the output by which parcels are present in each shape?

For example, if the Declaration of Independence is relevant to Maine and New Hampshire, but not Vermont, could I have the output be something like:

• Maine: Declaration of Independence | Maine Constitution |
• New Hampshire: Declaration of Independence |
• Vermont: No Documents

Thanks!

[JohannesLindner]

It should be possible. I guess you edited the code given in the original question rather heavily. Could you please post the code you're using right now?

Expand the comment toolbar, click on "Insert/Edit code sample", choose Javascript as language.

[AlfredBaldenweck]

Honestly I barely touched it; the only thing I did was change the table and fieldnames, as well as added some comments. (I also credited you at the end of the code).

This sample includes Table C because I was trying to make this current idea work; but all I've done is set it up to be brought in (Line 4).

Okay, I've rerouted the filter through the Parcel ID, rather than the Shape's ID. Currently working on sorting the output.

Table C has a column for the parcel names, as well.

// load the tables
var table_a = FeatureSetByName($datastore, "Document_List")
var table_b = FeatureSetByName($datastore, "Relationship_table")
var table_c = FeatureSetByName($datastore,"Parcels_full_list")

//grab Parcels to sort the docs by.
var plot_id = $feature.ObjectID
var filtered_c =Filter(table_c, "Shape_ID_No = @plot_id")

var parcel = []
for(var c in filtered_c) {
  Push(parcel, c.Parcel_ID_No)
}

// Get the document ids
// All variables must be numbers, NOT text

var filtered_b = Filter(table_b, "Parcel_ID_No = @parcel")
if(filtered_b == null || Count(filtered_b) == 0) {
  return "No documents"
}

// All variables must be numbers, NOT text
var doc_ids = []
for(var b in filtered_b) {
  Push(doc_ids, b.BOOK_ID_No)
}

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

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

//Make sure your hyperlinks have an alias or else this doesn't work.
var out_str = ''

for(var d in docs){
    out_str += docs[d] + ' | ' //or the separator of your choice
}

return out_str

[JohannesLindner · Accepted solution]

OK, so instead of filtering all documents at once, you need to cycle through the parcels and filter all documents for each parcel. I can't test this code, but it should be close:

// load the tables
var table_a = FeatureSetByName($datastore, "Document_List")
var table_b = FeatureSetByName($datastore, "Relationship_table")
var table_c = FeatureSetByName($datastore, "Parcels_full_list")

// create output array
var output_lines = []

// filter parcels
var plot_id = $feature.ObjectID
var filtered_c =Filter(table_c, "Shape_ID_No = @plot_id")

// loop through parcels
for(var c in filtered_c) {
  parcel_id = c.Parcel_ID_No
  parcel_name = c.Parcel_Name
  // get document ids for this parcel
  var filtered_b = Filter(table_b, "Parcel_ID_No = @parcel_id")
  if(filtered_b == null || Count(filtered_b) == 0) {
    Push(output_lines, parcel_name + ": No documents")
    continue
  }
  var doc_ids = []
  for(var b in filtered_b) {
    Push(doc_ids, b.BOOK_ID_No)
  }
  // get the document paths
  var filtered_a = Filter(table_a, "OBJECTID IN @parcel_ids")
  if(filtered_a == null || Count(filtered_a) == 0) {
    Push(output_lines, parcel_name + ": No documents")
    continue
  }
  var docs = []
  for(var a in filtered_a) {
    Push(docs, a.Hyperlink)
  }
  // format the output line for this parcel
  Push(output_lines, parcel_name + ": " + Concatenate(docs, " | ")
}
// concatenate output_lines
return Concatenate(output_lines, TextFormatting.NewLine)

As a side note: In my experience, it's very bad to rely on ObjectID as primary key. If for any reason you need to copy a feature or the whole feature class, the new ObjectIDs will be different, destroying your relationships. I've had this happen to me, recreating the relations was painful.

I recommend:

• add manual primary key fields to your tables
• create database sequences for each primary key (Create Database Sequence)
• add this calculation rule to the primary key field of each of your tables:

// field empty: return next sequence value
// else: return field value
return IIF(IsEmpty($feature["KeyField"]), NextSequenceValue("KeySequence"), $feature["KeyField"])​

[AlfredBaldenweck]

Thank you for the response. I knew I had to start a For loop but wasn't sure where to start it (or how to chain it together).

The code works great. I modified it to sort the out put by the document's names (line 33), and for formatting (Line 16, Line 47).

I did some searching regarding the formatting, and found that HTML does not like TextFormatting.NewLine at all, so I used "<br>" instead. "<p>" works as well, but adds more space between the lines. 

This is the final result. 

Depending on how our data ends up looking, I might further edit it for readability. With further styling, edited in Lines 40 and 47  (Blank spaces are ignored, so I had to use text)

Thanks so much for your help with this!

Thank you also for the database sequence how-to; I've wanted to learn how to do that for a while.

// load the tables
var table_a = FeatureSetByName($datastore, "Document_List")
var table_b = FeatureSetByName($datastore, "Relationship_table")
var table_c = FeatureSetByName($datastore,"Parcels_full_list")

// create output array
var output_lines = []

// filter parcels
var plot_id = $feature.Shape_ID_No
var filtered_c =Filter(table_c, "Shape_ID_No = @plot_id")

// loop through parcels
for(var c in filtered_c) {
  var parcel_id = c.Parcel_ID_No
  var parcel_name = '<b>'+ c.Parcel_Name + ": " +'</b>' //Parcel name, formatted in HTML, returns as (bolded) [Parcel_Name]:

  // get document ids for this parcel
  // All variables must be numbers, NOT text
  var filtered_b = Filter(table_b, "Parcel_ID_No = @parcel_id")
  if(filtered_b == null || Count(filtered_b) == 0) {
    Push(output_lines, parcel_name + "No documents") // In case of no matching data, returns a blanket message.
    continue
  }

  // All variables must be numbers, NOT text
  var doc_ids = []
  for(var b in filtered_b) {
    Push(doc_ids, b.BOOK_ID_No)
  }

  // get the document paths
  var filtered_a = OrderBy(Filter(table_a, "BOOK_ID_No IN doc_ids"), "Document_Name") //Orders the documents by their names, rather than the URL
  if(filtered_a == null || Count(filtered_a) == 0) {
    Push(output_lines, parcel_name + "No documents")
    continue
  }
  var docs = []
  for(var a in filtered_a) {
    Push(docs, a.Hyperlink)
  }
  // format the output line for this parcel
  Push(output_lines, parcel_name + Concatenate(docs,  "; "))
}

// Concatenates the output for each Parcel in the shape in a reader-friendly format
return Concatenate(output_lines, '<br>')  //TextFormatting.NewLine does not work in any capacity with HTML. Use <br> instead

[JohannesLindner]

Glad to help.

I did some searching regarding the formatting, and found that HTML does not like TextFormatting.NewLine at all

Good to know.

(Blank spaces are ignored, so I had to use text)

You should be able to use non-breakable spaces. Just chain some "&nbsp;" together.