Use Distinct in Arcade Expression to return unique rows from table

[DonSjoboen]

Hello,

I'm trying to use DISTINCT in my Arcade expression (see below) in my web map popup, but am not getting the expected results.

I'm trying to return the unique route for each customer.  I'm relating this table to a point feature class.  I published my map service (to our ArcGIS Servers) with the point feature class and the two view tables, and using the FeatureSetByName $datastore for the var tbl.

When I Console(customers) to view the results, I get 'Object, FeatureSet'?  Does Distinct work on feature classes only or does it work with tables?

Here's my Arcade expression...

var tbl = FeatureSetByName($datastore,"SWM Routes Table");
Console(Count(tbl));
var id = $feature["CO_ID"];
Console(id);
var sql = "CONNECTION_OBJECT = '" + id + "'";
Console(sql);
var customers = Distinct(Filter(tbl, sql), "ROUTE");
Console(customers);
var cnt = Count(customers);
Console(cnt);
var result = "";
if (cnt > 0) {
for (var customer in customers) {
result += TextFormatting.NewLine + "Route: " + customer.ROUTE +
" / " + customer.Supervisor + " / " + customer.COLLECTION_DAY +
/* TextFormatting.NewLine + "Customer: " + customer.BP_Name +
TextFormatting.NewLine + "Business Partner: " + customer.BP_Num +
TextFormatting.NewLine + "Contract Account: " + customer.ContractAcct +*/
TextFormatting.NewLine;
}

} else {
result = "No customer data"
}

Console(result);
return result;

Anyone have experience with using Distinct in a related (1:M) table or have suggestions?

Thanks,

Don

[XanderBakker]

Hi DSJOBOEN@ci.tacoma.wa.us_tacoma ,

A quick question: is you field "CONNECTION_OBJECT" string o numeric? If numeric, you should change the sql you use in the Filter (take out the quotes).

[DonSjoboen]

Hello Xander Bakker,

The fields [CO_ID] & [CONNECTION_OBJECT] are integers.  I was able to get my Arcade expression to work with the Distinct function and still using double quotes for [CO_ID] and CONNECTION_OBJECT...

var tbl = FeatureSetByName($datastore,"SWM Routes Table");
Console('$datastore count: ' + Count(tbl));
var id = $feature["CO_ID"];
Console('id: ' + id);
var sql = "CONNECTION_OBJECT = '" + id + "'";
Console('sql: ' + sql);
var customers = Filter(tbl, sql);
var cnt = Count(customers);
Console('customers count: ' + cnt);

var fields = ["ROUTE", "Supervisor", "COLLECTION_DAY"];
customers = Distinct(customers, fields);
Console('customers distinct count: ' + Count(customers));
var result = "";
if (cnt > 0) {
for (var customer in customers) {
result +="Route: " + customer.ROUTE +
" / " + customer.Supervisor + " / " + customer.COLLECTION_DAY +
TextFormatting.NewLine;
}

} else {
result = "No customer data"
}

Console(result);
return result;

[XanderBakker]

Hi Don Sjoboen ,

So, what I deduce from your answer is that the fields are numeric, and yet it works when you create a query as if the field is a string. I think I have seen this go wrong in the past and I would recommend against it, since it is not guaranteed that it will work in the future. I think it might even be possible to avoid this by using the query as:

var sql = "CONNECTION_OBJECT = @id";‍

[DonSjoboen]

Xander Bakker‌ I will give that a go and see what happens.

On another note, although I am using Console() in my arcade expression to check my logic, sometimes it returns as I would expect or not expect, meaning that I always don't get a function error, or invalid parameters message.  So, to me I think "okay this works" or "why is field not found?"... but, when I go thru the steps to view in the map popup window only to find out that there is no data returned!?  I wish there was a way the Arcade console could tell you (in the supported platforms) if the data result is NULL or invalid (i.e. no data returned) or to help you troubleshoot further.  For example... when using Distinct, if I only have the one field it says field not found and yet I get no data when viewing the popup, although it found the 3 distinct records... I wish it would tell me that I need ALL fields referenced in the related table.  I am new at Arcade and am learning quick, but it was a little frustrating.

var fields = ["ROUTE"];
customers = Distinct(customers, fields);
Console('customers distinct count: ' + Count(customers));

 

Perhaps a more detailed error like; please add all related fields or not all fields found or something to help indicate what the issue is...

var fields = ["ROUTE", "Supervisor", "COLLECTION_DAY"];
customers = Distinct(customers, fields);
Console('customers distinct count: ' + Count(customers));

 

Do you have any suggestions or is that something ESRI team is aware of and working on improving?

[XanderBakker]

Hi Don Sjoboen ,

I assume that when you create the result after distinct with 1 field, you only try to retrieve the ROUTE field and not the other fields, right? The fact that it shows the "customer distinct count: 3" implicates that the Distinct was executed correctly. A console statement that should be executed after an error occurs does not get executed. 

I agree that there is room for improvement when debugging an expression, but using these console statements help to determine where an error occurs. 

[DonSjoboen]

Xander Bakker‌

No.  My for loop included all fields that I want returned or displayed in the popup when I referenced the ROUTE field.

var result = "";
if (cnt > 0) {
for (var customer in customers) {
result +="Route: " + customer.ROUTE +
" / " + customer.Supervisor + " / " + customer.COLLECTION_DAY +
TextFormatting.NewLine;

[XanderBakker]

Hi Don Sjoboen ,

Then that's where I think the error refers to.  I assume it will not any information on the fields that are not included in the Distinct fields. Can you return the customers just after the Distinct to verify?

[DonSjoboen]

Xander Bakker‌

You are correct, I still get the error and the count results are still the same...

 

[DonSjoboen]

Xander Bakker‌

Using the following seemed to work better, as I could see the message returned from the Console('sql: ' + sql);