Community

How to return distinct values from a related table?

234
1
03-06-2024 05:00 AM
DaveK
by
Occasional Contributor
‎03-06-2024 05:00 AM

Hello! 

I have an Arcade expression which returns values from a related table within a popup. It works great except that it returns every value in the 1:M relationship, so there are many duplicates. Is it possible to modify my expression to only return the distinct values from the related records? Any help is appreciated. 

My expression 

var fs = FeatureSetByName($map, "RELATED TABLE" , ['*'], false);
var result = "";
var cnt = Count(fs);
if (cnt > 0) {
    // start loop through related records
    for (var f in fs) {
        // for each f (=feature) in related features, add attendee to the result
        result += TextFormatting.NewLine + f.FIELDRETURNED;
    }    
} else {
    // overwrite result with text to indicate that there are no attendees
    result = "";
}

return result;

Thanks!

0 Kudos
1 Reply
RoryMcPherson
by
New Contributor III
‎03-07-2024 03:14 PM

There is a Distinct function available in Arcade you can use to do this, however, you need to have an Array to return the unique values from. If you populate your result values into an array, you can use the Distinct function to return the unique results.

If you are happy with returning an array, then you can use the script below:

 

var fs = FeatureSetByName($map, "returnDistinct" , ['*'], false);

var fsArray = Array(1);
var fsCount = Count(fs);

// check for features
if (fsCount > 0) {
    for (var f in fs) {
        // for each feature, add to the array
        Push(fsArray, f.name)
    } 
} else {
    return "No records available";
}

// return distinct values
return Distinct(fsArray);

 

 

If you prefer to return a text string instead, I modified the script slightly so that you can do that too. Just noting however that the first line returned is a "null" value creating an empty first line, which is why I've also added an Erase function to remove the 0 index.

 

 

var fs = FeatureSetByName($map, "returnDistinct" , ['*'], false);

var fsArray = Array(1);
var fsCount = Count(fs);

// check for features
if (fsCount > 0) {
    for (var f in fs) {
        // for each feature, add to the array
        Push(fsArray, f.name)
    }
} else {
    return "No records available";
}

// return distinct values
// return Distinct(fsArray);

// return distinct values text
Erase(fsArray, 0); // remove 0-index "null" field
return Concatenate(Distinct(fsArray), TextFormatting.NewLine);

 

 

I hope that helps!

0 Kudos