Allow SQL, Calculated Fields for Hosted Feature Layer View

06-07-2018 05:49 AM
Status: Open
New Contributor III

I would like the ability to expose and edit SQL for hosted feature layer views.  This would allow much greater customization and control over the views.  An example where this can be used is when you want to filter the layer based on something dynamic.  For example, if you have a feature layer that has CreationDate and EditDate attributes and you want to filter for records where the EditDate is within 14 days of the CreationDate:

select * from [FeatureLayer] where EditDate <= DATEADD(day,14,convert(date,CreationDate))

Another example is where you want to have a dynamically generated attribute on the view that is perhaps a concatenation of two attributes in the feature layer:

Select [FeatureLayer].*, concat([Status] + '-' + [Priority]) as [Defect_priority]

from [FeatureLayer]

Or even Case statements:

Select [AssetID],

case when [EditDate] > DATEADD(day,14,convert(date,CreationDate)) then 'Overdue'

when [EditDate] <= DATEADD(day,14,convert(date,CreationDate))  then 'OnTime'

when [EditDate] is null then 'Open'

Else '' end as [CurrentStatus]

All of this can be done in an RDBMS such as SQL Server, however it could be useful to have this functionality for organizations that are using AGOL only.

Tags (2)
1 Comment

I agree this would be great!  I could also see arcade being used to allow for the generation of this kind of dynamic content (as can already be done in web map pop-ups).  There would be a lot of value to be able to generate dynamic information at the attribute level through SQL, Arcade, or other means.