Arcade Data Expression - GroupBy on Non Hosted Feature Layer

[AndrewKesterton]

Hi dashboard community, 

I'm trying out Arcade data expressions in ArcGIS Enterprise 10.9.1 (so Arcade 1.15). 

When looking for statistics/summary values with the GroupBy function on a non-hosted feature layer, the data expression doesn't seem to parse the results of the statistics.  

var portal = Portal('https://myportalurl/portal');
var fs = FeatureSetByPortalItem(
    portal,
    'b10d26459a3e415f856a8310da14accc',
    1,
    [
        'parentrowid',
        'quantity'
    ],
    false
);
return GroupBy(fs, ['parentrowid'], 
[{name: 'count_features', expression: 'quantity', statistic: 'SUM' }]);

Returns:

The same data expression against a hosted feature layer works correctly.  The feature layers support statistics, and I see them returned correctly by the rest service, but they dont appear in my expression. 

Is there a known limitation using Arcade with these feature services? 

Thanks,

Andrew 

[jcarlson]

When you're accessing a non-hosted layer, the SQL expression may need to adjust to work with whatever database engine you're using.

Could you try an alternate approach? Set the expression to '1' and the statistic to 'SUM'. Should come out to the same thing, but maybe the underlying database will like it better.

Also, what's the end goal of the expression? I love using data expressions, but sometimes there are easier ways of accomplishing a goal.

[AndrewKesterton]

Thanks Josh for replying, the expression wasn't to count on objectid, I have updated my pseudocode with a better example.

I'm actually trying to pivot from the repeat table in a Survey123 survey, and sum up different attributes like a quantity that is recorded in the repeat.  We did try to use database views but these have limited support in dashboard - usually problems with the summary statistics being disabled on the resulting feature service, hence trying the expressions approach. 

The postgres db is handling the request well, and I can see the values being returned by the query to the feature layer, it just seems the arcade engine (?) isn't able to parse them when it's from a non-hosted service and I can't see why. 

[jcarlson]

Interesting... So what is being returned from the query, then? Is the quantity field numeric?

[AndrewKesterton]

It's strange, on the response tab I can see the sum's being calculated and returned by number, but somehow they aren't parsed into the featureset.   t3 is the quantity field: 

and the request: 

[{"onStatisticField":"quantity","outStatisticFieldName":"T3","statisticType":"sum"},{"onStatisticField":"objectid","outStatisticFieldName":"T4","statisticType":"min"}]

Comparing the response from the hosted and non-hosted feature services, the non-hosted service includes these additional properties describing the service.  Possibly this is what's causing arcade to have trouble parsing the response - as otherwise the responses are identical from the two services; 

• exceededTransferLimit, 
• globalIdFieldName, 
• objectIdFieldName

[MohammedZaki]

Hi @AndrewKesterton , did you solve it? I think it is a bug with the Arcade executing the expression against the registered Postgres DB. I'm facing the same issue. The exact layer is working fine with SQL server DB.

[AndrewKesterton]

Hi @MohammedZaki , no - I came to the same conclusion that this is a bug but wasn't able to have it logged.  

Good to know it works on SQL Server so the bug report can be targetted. 

Andrew