ATTRIBUTE RULE SAVE BUG ISSUE SQL FILTER STATEMENT

[OliverSandoval_p]

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.

[HusseinNasser2]

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

[OliverSandoval_p]

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

[HusseinNasser2]

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. 

[OliverSandoval_p]

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. 

[HusseinNasser2]

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 , 

[OliverSandoval_p · Accepted 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  

• Field Calculate placeholder attributes into the field in order to save the attribute rule,
• Disable the rule
• Then field calculate the correct values back in the field
• Enable rule
• Publish registered feature service
• work fine in Field Maps

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.