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.

Snapshot of a Relationship Class.PNG

JohnGoat_0-1716464705240.png

JohannesLindner

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 = <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/28818">@doc</a>_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 = <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/28818">@doc</a>_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;