Widget choices with number values in string fields

[RichardFisher]

Widget (filter) will not provide expression choices found for numeric data. Feature table has length, height in feet but are stored as strings not numbers. This causes the widget to provide string expression options and not  numeric options such as greater than. One can choose a pull-down list of values to choose from by not show me all items less than 20 feet for example as you would see if they were number values in the data. However a arcgis rest service query in the Where clause 'VERTICAL_CLR_RD1_FEET > 18 AND VERTICAL_CLR_RD1_FEET < 22' will be able to process the string values as numbers and return the correct results! Is there a way to create a custom Widget that will consume such a defined ArcGIS REST Service Query and the expression source and deliver the data needed? Example: https://myportal/rest/services/BRIDGES/FeatureServer/0/query?where=VERTICAL_HEIGHT_FEET+%3E+18&objec...

The only alternative is to create a version of the feature table and service adjusted so that the fields in question are of a numeric type and not a string type in the database. We frequently run into this and are not often able to alter the table or service being used.

It is also interesting the functions like CAST()  or in SQL TO_NUMBER() and examples online tend to only deal with converting numeric to strings and not how to do the reverse string to number, and Arcade also seems to lack this angle on conversions. 

[jcarlson]

Arcade does have a conversion using the Number function. Not that it's particularly helpful in this specific instance.

There's no way to force a non-numeric field to be treated like a numeric field, though. You could write some clever string-based filter expressions that could approximate a numeric filter, but they wouldn't be user-configurable.

Your best bet is to just create new fields and calculate their contents to be the converted number of the corresponding string fields.

[RichardFisher]

Thank you Josh and for the Arcade tip, I agree this is the most common solution as is having a table with the correct field types so this is not an issue and one most likely to be the fix for this.

I find it interesting that the rest query does work for this and still wonder if there is a way even if not for this solution to allow widgets to consume such a query and resulting json as the data source rather than the table directly as the source, kind of a way to pre-process the data the widget would see.