We have a GI with a calculated date field that returns no results when we apply a quick date filter to it.
Any suggestions on how to get the filter to work? Thank you!
Best answer by Gabriel Michaud
View originalQuick Date Filter in a GI
Userlevel 5
+1
Userlevel 7
+10
Formula-based columns can’t be filtered on through OData, and even though it’s generally possible from the user interface I suspect there are additional restrictions in the user interface. Looking at the trace might help us understand what’s going on.
Userlevel 1
Hi
If your calculated date field returns a null (empty) for any of the records, you will still get a results table that can be sorted, and some of the filters seem to work fine. However, filtering by a single date (or between with that date entered twice) will yield no results.
If this is your case substituting the null with any “filler” date that can be easily filtered away or ignored should get your quick filter working.
Hope this helps!
Userlevel 7
+10
Issue found -- LastModifiedDateTime is a date-time field, but ARTran.TranDate used as schema field which is a date-only field. Behind the scenes, Acumatica generates a SQL query that tries to compare 12/08/2022 00:00:00 with a record that might have 12/08/2022 11:59:30. The values are NOT equal and the record doesn’t show up.
The solution is to either strip the time portion from LastModifiedDateTime in the formula, or use “Is Between” as comparison operator.
I ran into this issue as well. However I am having issues striping the time portion from the date/time field in a GI. I am able to pad it but it then treats it as a string and I am unable to filter it as I would a date field using the Today() function. Does anyone know how to remove the time portion in a GI and still keep it as a date type field? TYI
Userlevel 1
I am sure this is not the most efficient, but I have tested it working in 2021R1.
In the Data Field use:
= CDate(Concat(CStr(Year([ARInvoice.LastModifiedDateTime])),'-',CStr(Month([ARInvoice.LastModifiedDateTime])),'-',CStr(Day([ARInvoice.LastModifiedDateTime]))))
Creating the appropriate date (without time) as a string converting back to date.
In the Schema Field use:
ARTran.TranDate (or other date only field from the schema).
Use appropriate Caption to overwrite caption from ARTran.TranDate
Hope this helps!
Thank you
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.