How to Extract Related Tables from a Related Table with Arcade in Pop-Ups

[MagdaUsarek-Witek]

I created a relationship class in ArcPRO and shared the the feature class and tables as a web layer in ArcGIS Online. 

The relationship class goes as follows:

Main Table is connected to Activity class through a field called PI_ID

The Following tables are connected to Activity class through a field called INT_Doc_ID.

The tables are listed and working the $datastore in the arcade pop-up function. 

The Aracade code that I modified to access the activity class is:

var tbl = FeatureSetByName($datastore,"ActivityClass");
var codigo = $feature["PI_ID"];
var sql = "PI_ID= '" + codigo + "'";
Console(sql);
var mantenimientos = Filter(tbl, sql);
var cnt = Count(mantenimientos);
var historia = "";
if (cnt > 0) {    historia = cnt + " Record(s):";    
for (var mantenimiento in mantenimientos) {   
var txt_ActivityNum = Text("Activity Number: " + mantenimiento.ACTIVITY_NUM);
var txt_fecha = txt_ActivityNum + "- Doc Status Date: " + Text(mantenimiento.DOC_STATUS_DATE, '(Y/MM/DD) - ');        
var txt_docdes = txt_fecha + "Status: " + mantenimiento.DOC_STATUS_DESC;        
historia += TextFormatting.NewLine + txt_docdes;    }} 
else {    historia = "No Records";}
return historia;

 The result was successful:

2 Record(s): Activity Number: 80001- Doc Status Date: (2013/11/03) - Status: Expired Activity Number: 80001- Doc Status Date: (2013/11/03) - Status: Expired

Is there a way to modify the code to access the other tables?  I attached a diagram of the geodatabase relationship class.

[JohannesLindner]

Sure. Load the related tables at the start of the script. In your loop, get the INT_Doc_ID and filter those tables.

var tbl = FeatureSetByName($datastore,"ActivityClass");
var tbl_subjectitem = FeatureSetByName($datastore, "SubjectItem");
var tbl_contact = FeatureSetByName($datastore, "Contact");

var codigo = $feature["PI_ID"];
//var sql = "PI_ID= '" + codigo + "'";
// Arcade has a shortcut for constructing SQL queries that will take care of the type-relevant stuff (like apostrophes):
var sql = "PI_ID = @codigo";
Console(sql);
var mantenimientos = Filter(tbl, sql);
var cnt = Count(mantenimientos);
var historia = "";
if (cnt > 0) {
  historia = cnt + " Record(s):";
  for (var mantenimiento in mantenimientos) { 
    // ...
    var doc_id = mantenimiento.INT_DOC_ID
    var subjectitems = Filter(tbl_subjectitem, "INT_DOC_ID = @doc_id")
    for(var si in subjectitems) {
        // ...
    }
    var contacts = Filter(tbl_contact, "INT_DOC_ID = @doc_id")
    for(var c in contacts) {
        // ...
    }
  }
} 
else {
  historia = "No Records";
}
return historia;

[MagdaUsarek-Witek]

Thank you! that brought me much closer but I am still having a little trouble pulling the records.  It won't pull anything in (filter(tbl,sql) and I tried Frist(Filter(tbl,sql) and somethign pops up but it repeats the same record.  Which I think has something to do with FIRST, but it doesn't work without the First(Filter...etc)

// Write a script to return a value to show in the pop-up.
// For example, get the average of 4 fields:
// Average($feature.SalesQ1, $feature.SalesQ2, $feature.SalesQ3, $feature.SalesQ4)

var tbl = FeatureSetByName($datastore,"ActivityClass");
var tbl_subjectitem = FeatureSetByName($datastore, "SubjectItem");
var tbl_contact = FeatureSetByName($datastore, "Contact");

var codigo = $feature["PI_ID"];
//var sql = "PI_ID= '" + codigo + "'";
// Arcade has a shortcut for constructing SQL queries that will take care of the type-relevant stuff (like apostrophes):
var sql = "PI_ID = @codigo";
Console(sql);
var mantenimientos = Filter(tbl, sql);
var cnt = Count(mantenimientos);
var historia = "";
if (cnt > 0) {
  historia = cnt + " Record(s):" + TextFormatting.NewLine;
  for (var mantenimiento in mantenimientos) { 
    // ...
    var doc_id = mantenimiento.INT_DOC_ID
    var subjectitems = FIRST(Filter(tbl_subjectitem, "INT_DOC_ID = @doc_id"))
    for(var si in subjectitems) {
        var txt_SubjectItemDesc = Text("Subject Item: " +  subjectitems.subject_item_description );
        var tableresults = txt_SubjectItemDesc; 
        tableresults += TextFormatting.NewLine + tableresults;
    }
    var contacts = Filter(tbl_contact, "INT_DOC_ID = @doc_id")
    for(var c in contacts) {
        // ...
    }
  }
} 
else {
  historia = "No Records";
}
return historia + tableresults;

I attached the results.

Thank you!

[JohannesLindner · Accepted solution]

You have several problems in your code:

• "subjectitems" is a dataset with the filtered results of tbl_subjectitems. the individual subjectitems are named "si" (see line 23 in your code).
• you define tableresults inside the loop (line 25). for each found subjectitem, you overwrite the results of the previous ones.
• you append tableresults to itself (line 26), that's why you get two identical lines.
• tableresults is created for each mantenimiento, but you only append it to historia (results of all mentnemientos) at the very end (line 37), so you will only see the tableresults of the last processed mantenimiento.

Try this:

// Write a script to return a value to show in the pop-up.
// For example, get the average of 4 fields:
// Average($feature.SalesQ1, $feature.SalesQ2, $feature.SalesQ3, $feature.SalesQ4)

var tbl = FeatureSetByName($datastore,"ActivityClass");
var tbl_subjectitem = FeatureSetByName($datastore, "SubjectItem");
var tbl_contact = FeatureSetByName($datastore, "Contact");

var codigo = $feature["PI_ID"];
//var sql = "PI_ID= '" + codigo + "'";
// Arcade has a shortcut for constructing SQL queries that will take care of the type-relevant stuff (like apostrophes):
var sql = "PI_ID = @codigo";
Console(sql);
var mantenimientos = Filter(tbl, sql);
var cnt = Count(mantenimientos);
var historia = "";
if (cnt > 0) {
  historia = cnt + " Record(s):" + TextFormatting.NewLine;
  for (var mantenimiento in mantenimientos) {
    // from your original post
    var txt_ActivityNum = Text("Activity Number: " + mantenimiento.ACTIVITY_NUM);
    var txt_fecha = txt_ActivityNum + "- Doc Status Date: " + Text(mantenimiento.DOC_STATUS_DATE, '(Y/MM/DD) - ');        
    var txt_docdes = txt_fecha + "Status: " + mantenimiento.DOC_STATUS_DESC;        
    historia += TextFormatting.NewLine + txt_docdes;
    // filter tbl_subjectitems
    var doc_id = mantenimiento.INT_DOC_ID
    var subjectitems = Filter(tbl_subjectitem, "INT_DOC_ID = @doc_id")
    // define tableresults outside of the loop avoid overwriting it for each subjectitem
    var tableresults = ""
    for(var si in subjectitems) {
        // subjectitems is the filtered dataset. the individual features are called "si".
        var txt_SubjectItemDesc = "Subject Item: " +  si.subject_item_description;
        // append to tableresults
        tableresults += TextFormatting.NewLine + txt_SubjectItemDesc;
    }
    // do the same for tbl_contacts
    var contacts = Filter(tbl_contact, "INT_DOC_ID = @doc_id")
    for(var c in contacts) {
        // ...
    }
    // append tableresults (output of the filtered subjectitems & contacts for one mantenemiento) to historia (output of all mantenemientos)
    historia += TextFormatting.NewLine + tableresults
  }
} 
else {
  historia = "No Records";
}
return historia;

[MagdaUsarek-Witek]

Hi Johannes, 

Would you know if there is a way to pull related records of a related table from FeatureSetByRelationshipName($feature...) vs FeaturesetByName($datastore...)? 

The way our file was published was through layer files instead of file geodatabase so I wanted to see if there is a way to pull the information from layer files since a datastore option might not be available.

Thank you!

Thank you!

[JohannesLindner]

If you have a relationship class connecting the tables, then you can use FeaturesetByRelationshipName, which will give the same result as doing it manually (FeatureSetByName -> Filter).

// get related rows by relationship name
// only if there is a relationship class defined between the tables
var mantenimientos = FeatureSetByRelationshipName($feature, "RelationShipName")

If you have no relationship classes and can't use $datastore, you have to publish all tables. Then you can use one of the other FeatureSetBy* functions (especially FeatureSetByPortalItem):

https://developers.arcgis.com/arcade/function-reference/data_functions/#featuresetbyid 

[MagdaUsarek-Witek]

Thank you! Your notes were very orgnized and helped me understand how to write it!

I am posting the final script I modified to extract all the info I wanted in case someone wanted to see some options for grouping information. 

// Write a script to return a value to show in the pop-up.
// For example, get the average of 4 fields:
// Average($feature.SalesQ1, $feature.SalesQ2, $feature.SalesQ3, $feature.SalesQ4)

// Write a script to return a value to show in the pop-up.
// For example, get the average of 4 fields:
// Average($feature.SalesQ1, $feature.SalesQ2, $feature.SalesQ3, $feature.SalesQ4)

var tbl = FeatureSetByName($datastore,"ActivityClass");
var tbl_subjectitem = FeatureSetByName($datastore, "SubjectItem");
var tbl_contact = FeatureSetByName($datastore, "Contact");

var PI_ID = $feature["PI_ID"];
//var sql = "PI_ID= '" + PI_ID + "'";
// Arcade has a shortcut for constructing SQL queries that will take care of the type-relevant stuff (like apostrophes):
var sql = "PI_ID = @PI_ID";
Console(sql);
var PI_Records = Filter(tbl, sql);
var cnt = Count(PI_Records);
var Record_List = "";
if (cnt > 0) {
  Record_List = cnt + " Record(s):" + TextFormatting.NewLine;
  for (var PI_Record in PI_Records) {
    // from your original post
    Var PI_Num = text($feature["pref_id_num"])
    var ActivityNum = Text(PI_Record.activity_class_code + PI_Record.ACTIVITY_NUM);
    var Doc_Status_Date = "Doc Status Date: " + Text(PI_Record.DOC_STATUS_DATE, 'MM/DD/Y');        
    var Doc_Status = "Status: " + PI_Record.DOC_STATUS_DESC;        
    Record_List += TextFormatting.NewLine + "File # " + PI_Num + " - " + ActivityNum + TextFormatting.NewLine +  Doc_Status_Date + TextFormatting.NewLine + Doc_Status + TextFormatting.NewLine + TextFormatting.NewLine  + "Subject Item(s): ";
    // filter tbl_subjectitems
    var doc_id = PI_Record.INT_DOC_ID
    var subjectitems = Filter(tbl_subjectitem, "INT_DOC_ID = @doc_id")
    // define tableresults outside of the loop avoid overwriting it for each subjectitem
   
    var tableresults = ""
    for(var si in subjectitems) {
        // subjectitems is the filtered dataset. the individual features are called "si".

        var txt_SubjectItemDesc = si.subject_item_description;
        // append to tableresults
        tableresults += txt_SubjectItemDesc + TextFormatting.NewLine ;
    }
    // do the same for tbl_contacts
	var contacts = Filter(tbl_contact, "INT_DOC_ID = @doc_id")
	var contactresults = ""
    for(var c in contacts) {
        // subjectitems is the filtered dataset. the individual features are called "si".
		var PM = c.int_doc_id1;
		var null_PM = IIF(PM == "null", 'No Data', IIF(PM == 0, 'No Data', PM))
		var PE = c.int_doc_id1;
		var null_PE = IIF(PE == "null", 'No Data', IIF(PE == 0, 'No Data', PE))
		var Applicant = c.int_doc_id1_1;
		var null_Applicant = IIF(Applicant == "null", 'No Data', IIF(PM == 0, 'No Data', Applicant))
		var Agent = c.int_doc_id1_12;
		var null_Agent = IIF(Agent == "null", 'No Data', IIF(PM == 0, 'No Data', Agent))
        // append to tableresults
        contactresults += TextFormatting.NewLine + "Project Manager: " + null_PM + TextFormatting.NewLine  + "Project Engineer: " + null_PE + TextFormatting.NewLine + "Applicant: " + null_Applicant + TextFormatting.NewLine + "Agent: " + null_Agent ;
    }
    // append tableresults (output of the filtered subjectitems & contacts for one mantenemiento) to historia (output of all mantenemientos)
    Record_List += TextFormatting.NewLine + tableresults + TextFormatting.NewLine + "Contact Information: " + contactresults + TextFormatting.NewLine
  }
} 
else {
  Record_List = "No Records";
}
return Record_List;