Hello Everyone,
I'm having a real strange issue with pro, Pro doesn't let me save an attribute rule with correct sql syntax in a filter statement,
// Only can save the attribute rule using the dates in this format
var d_start_date = Date(this_year,03,16,07,0,0,0)
var d_end_date = Date(this_year,08,30,30,59,59,999)
// instead of correct sql filter syntax for filter below
var d_start_date_sql = Text(Date(this_year,03,16,07,0,0,0))
var d_end_date_sql = Text(Date(this_year,08,30,30,59,59,999))
var filter_statement = 'guid = @CB_gid AND cleaning_now_or_later = @clean_now AND inspection_date >= @d_start_date_sql AND inspection_date <= @d_end_date_sql'
// however, for the statement below using these variable is fine, and can save no issue
var year_beginning_date = Text(Date(this_year,00,02,-16,00,00,00))
var year_end_date = Text(Date(this_year,11,31,31,59,59,999))
var filter_statement = 'guid = @CB_gid AND cleaning_now_or_later = @clean_now AND inspection_date > @year_beginning_date AND inspection_date < @year_end_date'
This is the error I get in pro when trying to save with the sql syntax
And when I test the attribute rule from registered feature service, I get this error in the server logs when the incorrect sql syntax is used, so I know its those incorrect variables
I would love to get your input Hussein Nasser, Xander Bakker or Aaron Pulver! But it looks like I cant tag you unless you've replied.
Solved! Go to Solution.
After lots of trials and error, I was able to narrow it down to a bug within ArcGIS Pro,
For our niche very unique script, Pro bugs out when trying to save the attribute rule with Text(date()) variables in a SQL Filter statement. It only lets me save the rule when the feature class has no features with any of the attributes referenced in the script's if statement. Extremely weird and inconvenient lol. The Text(date()) format is required for the rule to work from a registered feature class i.e. when used in Field Maps.
The work around is
Despite this work around, any editing process done in Pro unrelated will not work as the rule will be triggered and only works from a feature service. So additional steps to the work around will need to be planned when working with an enterprise production database.
if you remove "Text" function and send native dates it works for me
when the text function is there the template replacement gets confused with the data type. Let me know if that works
Hi Hussein! Thank you for your quick response! I removed the text function and it allows me to save, however, the attribute rule fails when using a registered feature layer, i get this error message in the arcgis server manager logs next to the native date used in the filter
Hey Oliver ,
can you share the attribute rules with the following information?
Attribute rule script:
Attribute rules properties (whether its excluded from client eval, triggers etc.. )
Pro release:
Server Release:
SQL Server release:
Dataset registration (unversioned/branch/ traditional)
Fiddler logs (if Exclude from client evaluation is disabled (unchecked) )
I couldn't reproduce on my end, creating a feature with the rule succeeds on my sqlserver instance with latest software succeeds. It could be a bug in specific version of the software but hard to tell without a specific repro.
Hi Hussein,
I have all of the information except the fiddler logs, Ive been testing in field maps, but ill have to create a web app to edit the associated related table to get the logs. Or is there something else I can do? If not no prob. Ill direct message you all the info you requested.
Yes that would be helpful thanks.
I want to know where the problem actually is and fiddler log will help (fields map might be sending incorrect request and I want to capture that),
try reproducing in Pro, do the same edit there and see if you see the behavior.
Try excluding from application eval too and test again in fields map ,
After lots of trials and error, I was able to narrow it down to a bug within ArcGIS Pro,
For our niche very unique script, Pro bugs out when trying to save the attribute rule with Text(date()) variables in a SQL Filter statement. It only lets me save the rule when the feature class has no features with any of the attributes referenced in the script's if statement. Extremely weird and inconvenient lol. The Text(date()) format is required for the rule to work from a registered feature class i.e. when used in Field Maps.
The work around is
Despite this work around, any editing process done in Pro unrelated will not work as the rule will be triggered and only works from a feature service. So additional steps to the work around will need to be planned when working with an enterprise production database.