Arcade Data Expression - GroupBy on Non Hosted Feature Layer

160
4
06-24-2022 04:48 AM
Labels (1)
AndrewKesterton
New Contributor III

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:

AndrewKesterton_0-1656071180561.png

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 

0 Kudos
4 Replies
jcarlson
MVP Notable Contributor

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.

- Josh Carlson
Kendall County GIS
0 Kudos
AndrewKesterton
New Contributor III

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. 

 

0 Kudos
jcarlson
MVP Notable Contributor

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

- Josh Carlson
Kendall County GIS
0 Kudos
AndrewKesterton
New Contributor III

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: 

AndrewKesterton_0-1656413941452.png

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

 

0 Kudos