Arcade: Array output from related table help

[Amarz]

Hello all, I am attempting to transfer field data 'Grid' from multiple Centerline fc segments into a RoadName table that takes the values, disolves and sorts them.

The data is connected through a relationship class based on a foreign key in the Centerline fc and a primary key in the RoadName table.

I am able to pass the data through, with an Attribute Rule with the results that looks like:

T8,T8,T7,T8,T8,T8,T8

So when I attempt to pass it through an Array to use Distinct & Sort, it is unable to identify the above correctly and returns the following:

'T8', 'T8, T7', 'T8', 'T8', ....

Here is the code I am using.

// 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, 'dev_Cathedral.DBO.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 results = "";

// check if there are related records found for the current ID
if (cnt > 0) {
// loop through related records
    for (var row in related_data) {
// read some data and create the line you want
        var line = row.Grid;
// add the line to the result
        results +=line;
    }
} else {
    results = "Need Table relate";
}
// return the result
return results

This is in ArcPro 2.8.1 using an attribute Rule connected & relationship class

Thank you for the help

[jcarlson]

A couple of points:

• Could you use FeatureSetByRelationshipName instead? If there's a true relationship class in place, this takes care of the filtering for you.
• Consider using push to build your array, that way you don't have to bother with interpreting a string. You can even check for existing values in your array at the same time.

// Output array
var grid_arr = []

// Get related records
var related_data = FeatureSetByRelationshipName(
    $feature,
    'relationship_name',
    ['Grid'],
    false
)

// Check if related records exist
if(Count(related_data) > 0){
    
    for (var row in related_data){
        
        // Check if grid value already in array
        if (Find(row.Grid, grid_arr) == -1){
            
            // Add to array
            Push(grid_arr, row.Grid)
        }
    }
    
    // You could do some sorting here if you wanted
    // Return concatenated string of array values
    return Concatenate(grid_arr, ', ')
} else {
    return 'No related records.'
}

[Amarz]

Thank you @jcarlson 

This is still producing some duplicates. If all the values that are being passed through the array return

["T8","T8","T7, T8","T8","T8","T8"]

your script still returns 

T8, T7, T8

or ["T8", "T7, T8"] 

Thoughts?

[jcarlson]

The way my expression is written, you're only going to get "T7, T8" as an array item if it is the row.Grid attribute being passed in. That would point to the issue being with the data itself, not the expression. Can you confirm that no such value exists in your related table?

Maybe try adding

Console(Distinct(related_data, 'Grid'))

 at line 11 and see what's in your data.

[Amarz]

Sorry I was not as descriptive in my initial post. There are instances where such an entry can occur. The data is coming from an Attribute Rule in the Centerline.Grid field that intersects a Grid feature class. This rule is taking all the segments with the same ID as in the RoadName table, and adding it to a field Grid in there.

So there may be a line segment that passes only through a single grid (ie T8') or a segment that passes through multiple grids (ie 'T7, T8'). My goal was to take all these possible outcomes, breaking them down into individuals (ie T8, T7, T8) then pass that through an array and Distinct / Sort them to make the outcome the smallest possible.

Not sure if that's possible, or if it requires too much extra scripting?

[jcarlson]

Oh, I see! That shouldn't be too much more work. We just need to split the input value by the commas, then do the check/push on those. Try putting this into the earlier expression in place of lines 15 - 23.

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)
        }
    }
}

[Amarz]

That works beautifully, can I ask where a SORT function would be placed as well? If you can add that in there this will solve all my problems!

Would it be best to reply with a whole script so I can mark as Solution?

Thank you very much! I tried using the Split in my 1st rendition, but wasn't able to get the parameters correct I guess.

[jcarlson · Accepted solution]

No problem. Here's the full expression, complete with a sort:

// 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,
    'relationship_name',
    ['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.'
}

[Amarz]

Thank you very much @jcarlson I really appreciate your time.