topic Re: ATTRIBUTE RULE SAVE BUG ISSUE SQL FILTER STATEMENT in Attribute Rules Questions

ATTRIBUTE RULE SAVE BUG ISSUE SQL FILTER STATEMENT

OliverSandoval_p — Thu, 25 Aug 2022 18:07:53 GMT

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.

Re: ATTRIBUTE RULE SAVE BUG ISSUE SQL FILTER STATEMENT

HusseinNasser2 — Thu, 25 Aug 2022 18:20:30 GMT

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

Re: ATTRIBUTE RULE SAVE BUG ISSUE SQL FILTER STATEMENT

OliverSandoval_p — Thu, 25 Aug 2022 18:38:24 GMT

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

Re: ATTRIBUTE RULE SAVE BUG ISSUE SQL FILTER STATEMENT

HusseinNasser2 — Thu, 25 Aug 2022 20:10:43 GMT

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.

Re: ATTRIBUTE RULE SAVE BUG ISSUE SQL FILTER STATEMENT

OliverSandoval_p — Thu, 25 Aug 2022 21:07:57 GMT

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.

Re: ATTRIBUTE RULE SAVE BUG ISSUE SQL FILTER STATEMENT

HusseinNasser2 — Thu, 25 Aug 2022 21:13:34 GMT

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 ,

Re: ATTRIBUTE RULE SAVE BUG ISSUE SQL FILTER STATEMENT

OliverSandoval_p — Thu, 22 Sep 2022 22:24:08 GMT

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.