topic Re: Use Distinct in Arcade Expression to return unique rows from table in ArcGIS Web AppBuilder Questions

Use Distinct in Arcade Expression to return unique rows from table

DonSjoboen — Tue, 23 Jun 2020 16:27:09 GMT

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

Re: Use Distinct in Arcade Expression to return unique rows from table

XanderBakker — Tue, 23 Jun 2020 20:08:15 GMT

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

Re: Use Distinct in Arcade Expression to return unique rows from table

DonSjoboen — Wed, 24 Jun 2020 14:31:53 GMT

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;

Re: Use Distinct in Arcade Expression to return unique rows from table

XanderBakker — Wed, 24 Jun 2020 19:57:12 GMT

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";‍

Re: Use Distinct in Arcade Expression to return unique rows from table

DonSjoboen — Wed, 24 Jun 2020 21:30:11 GMT

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?

Re: Use Distinct in Arcade Expression to return unique rows from table

XanderBakker — Wed, 24 Jun 2020 21:47:31 GMT

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.

Re: Use Distinct in Arcade Expression to return unique rows from table

DonSjoboen — Wed, 24 Jun 2020 21:51:55 GMT

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;

Re: Use Distinct in Arcade Expression to return unique rows from table

DonSjoboen — Wed, 24 Jun 2020 21:59:11 GMT

Xander Bakker‌

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

Re: Use Distinct in Arcade Expression to return unique rows from table

XanderBakker — Wed, 24 Jun 2020 22:27:04 GMT

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?

Re: Use Distinct in Arcade Expression to return unique rows from table

DonSjoboen — Wed, 24 Jun 2020 23:05:26 GMT

Xander Bakker‌

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

Re: Use Distinct in Arcade Expression to return unique rows from table

DavidColey — Fri, 28 Jan 2022 21:20:26 GMT

Hi @DonSjoboen and @XanderBakker - I know you guys were working on something a little different with this a while back but I wanted to share something similar since this post helped me out:

In my case, click on a parcel, return flood info from the Intersects. Add a TextFormatting.NewLine at where needed.

Thanks-

David

Re: Use Distinct in Arcade Expression to return unique rows from table

XanderBakker — Thu, 03 Feb 2022 19:41:05 GMT

Hi @DavidColey ,

You can do something like this (see below). Remember that the Distinct function returns and array and not a featureset:

Re: Use Distinct in Arcade Expression to return unique rows from table

DavidColey — Mon, 28 Feb 2022 21:13:30 GMT

fyi @XanderBakker , this returns an Illegal Argument error in the Map Viewer Arcade editor. But that's ok because I'm not treating it as an array. However, if you have an idea on how to get DomanName descriptions to return from the Distinct that would help.

Because FeatureSetByName here is using the $map profile, I can't seem to get the Distinct to return coded value descriptions, as if I were using the $feature profile....

Re: Use Distinct in Arcade Expression to return unique rows from table

XanderBakker — Mon, 07 Mar 2022 21:22:44 GMT

Hi @DavidColey

One way to go is to get the code a translate it to the description making use of the domain function. You can use something like the example below to get a dictionary of the domain and use that dictionary to translate the description based on the code.

function GetDomainDct(lay, fldname) { var dom = Domain(lay, fldname); var cvs = dom["codedValues"]; var dct = {}; for (var i in cvs) { var cv = cvs[i]; dct[cv["code"]] = cv["name"]; } return dct; }

Re: Use Distinct in Arcade Expression to return unique rows from table

DavidColey — Tue, 08 Mar 2022 13:41:50 GMT

Ah yes thanks @XanderBakker - I was looking at DomainName, but realized I had no method to get a return of descriptions. I'll work on this between dev conference sessions this week and post back what I can put together.

Re: Use Distinct in Arcade Expression to return unique rows from table

DavidColey — Thu, 17 Mar 2022 20:20:47 GMT

Hi @XanderBakker - here's some of what I came up with:

I was struggling with how to return the function until I realized I needed to set it to a var (via the Arcade Help) and then from that point it was simply a matter of making sure list was set to return an array as well. I have another piece that does not use the centroid, but then removes the self-intersecting zoning type, and returns the types that are adjacent:

The only problem is, is that it has to run things twice. If you have any ideas on how to make this more concise that would be great -