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
Solved! Go to 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.'
}
A couple of points:
// 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.'
}
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?
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.
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?
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)
}
}
}
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.
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.'
}