Using a Relationship to intersect layer and return to table

amarcz_cc

Hello,

I am attempting to subvert creating an extra Feature Class & manual manipulation of an Excel sheet to create a Street Index table for my maps.

My data consists of a RoadName table, a RoadCenterline fc (line) and a Grid fc (poly). The Roadname and RoadCenterline are related via a unique ID, and I am attempting to use an Attribute Rule in the RoadName table to ask the RoadCenterline fc to intersect with the Grid fc and return the results, concatenated and ordered into the RoadName table field of grid.

The data is housed in the same datastore, and I am using ArcPro 2.8.

Any help or advice would be much appreciated, 

Thank you, 

Alex

 

edit: I am not sure if this is possible through Arcade, but worth asking.

 

Update:

I have changed my methodologies to the following. 

Added a Grid field into the Road Centerline with an intersect + array with the following script to Sort & remove duplicates on each line segment:

 

var arr = [];
var count = 0;
var i = Intersects(FeatureSetByName($datastore,"Grid"), $feature);

for (var f in i){
   arr[count] = (f.PAGE);
   count += 1;
}
var results = Distinct(arr);
var results = Sort(results);

return results

 

With some extra lines, it formats the array into the fields such as below: 

 

P6, Q6

 

My next and last step is to use a sql statment inside the code to match the foreignkey in the RoadCenterline fc to the primarykey in the MasterRoadName table, with a Relationship class.

I am able to use the following code to get data over,

 

// first read out the Foreign Key of the Destination Table (Feature Class)
var fcID = $feature.roadnameid
// access the Orgin table (Table)
var tbl = FeatureSetByName($datastore, 'MasterRoadCenterline');
// create a sql expression to query on Primary Key
var sql = "F_roadnameid = '" + fcID + "'";
var related_data = Filter(tbl, sql);
// count the resulting records
var cnt = Count(related_data);
// initiate a variable to hold the result
var arr = [];
var count = 0;
var i = related_data;

for (var f in i){
   arr[count] = (f.GRID);
   count += 1;
}

var results = Trim(arr);
var results = Distinct(results);
var results = Sort(results);

return results

 

but end up with 

 

P6, P6, Q6, Q6

 

This is picking up all three segments and seeing them as Distinct, because

seg1 Grid = P6

seg2 Grid = P6, Q6

seg3 Grid = Q6

 

I am assuming I need to take the array that is in the RoadCenterline.Grid field, and break that into a string, so there are no values that are Grid= 'P6 , Q6' and then put that back into an array and Distinct & Sort it?

I am not sure if that's the best way or not.

Thank you for anyone looking at this, the help would be appreciated. If I get it to work I will post a detailed workflow.

amarcz_cc

Update: With help from multiple contributors, here is a way to achieve what I set out to.

Layers included: (must be in same datastore)

1. Grid (polygon fc)
2. RoadCenterline (line fc) related to RoadName via uniqueID
3. RoadName (table)

Starting with RoadCenterline

Help in this section came from @KenBuja in the thread  Arcade - Intersecting Polygons - return multiple answers if multiple.

1. Add new Field 'Grid' to accommodate Grid (polygon fc) string input
2. Attribute Rule pointing to above new field 'Grid'

 

var arr = [];
var count = 0;
var i = Intersects(FeatureSetByName($datastore,"Grid"), $feature);

for (var f in i){
   //f.PAGE is the field name in feature Grid
   arr[count] = (f.PAGE);
   count += 1;
}
var results = Distinct(arr);
var results = Sort(results);

return results

 

Next in the RoadName table

Help in this section comes from @jcarlson and the following thread: Arcade: Array output from related table help 

 

1. Add new Field 'Grid'
2. Attribute Rule pointing to 'Grid'

 

// Output array
var grid_arr = []

// Sort function for later
function grid_sort(a, b){
    if (a > b){
        return 1
    } else {
        return -1
    }
}

// Get related records
var related_data = FeatureSetByRelationshipName(
    $feature,
    //Add reltionship class name below
    'RelationshipName',
    ['Grid'],
    false
)

// Check if related records exist
if(Count(related_data) > 0){
    
    for (var row in related_data){
        
        // split value by commas
        var items = Split(row.Grid, ',', -1, true)
        
        for (var i in items){
        
            // trim any whitespace    
            var grid_value = Trim(items[i])
            
            // Check if grid value already in array
            if (Find(grid_value, grid_arr) == -1){
            
                // Add to array
                Push(grid_arr, grid_value)
            }
        }
    }
    
    // Sort grid_arr with custom sort function
    var sorted = Sort(grid_arr, grid_sort)

    // Return concatenated string of array values
    return Concatenate(sorted, ', ')
} else {
    return 'No related records.'
}

 

I hope this helps you all in your endeavors as a way to automate the creation of a Street Index Grid using relationship classes & Attribute Rules!