It is possible to use advanced date queries in views and entities as described here. However, these date queries only make sense when they are evaluated dynamically: in the current solution, the date queries are stored as a static date that is determined during the database synchronisation (typically as a part of the installation of a release).
It would be good if this behaviour was changed to use dynamic dates instead, based on the current datetime (and the timezone).
An example of a view that is defined as "all invoices with an invoice date in the future":
In the current solution, the view looks like this:
select INVOICEDATE from CUSTINVOICETABLE
where INVOICEDATE > '2022-08-17 06:35:15'
With a dynamic date determination, it could look like this:
select INVOICEDATE from CUSTINVOICETABLE
where INVOICEDATE > SYSDATETIME()
Thus, the change should take place in the database synchronisation, where the views/entities are created.
Comments
It is possible to use advanced date queries in views and entities, as described here. However, the result is a static date comparison that is determined during the database synchronisation (typically during an install of a new release). That way, it is not really suitable for proper date selection.Instead, the date in the view can be determined dynamically, based on the current datetime (and the timezone). That way it can be used for proper date selection in entities (including virtual entities).
Category: General
It is possible to use advanced date queries in views and entities as described here. However, these date queries only make sense when they are evaluated dynamically: in the current solution, the date queries are stored as a static date that is determined during the database synchronisation (typically as a part of the installation of a release).It would be good if this behaviour was changed to use dynamic dates instead, based on the current datetime (and the timezone).An example of a view that is defined as "all invoices with an invoice date in the future":In the current solution, the view looks like this:select INVOICEDATE from CUSTINVOICETABLE where INVOICEDATE > '2022-08-17 06:35:15'With a dynamic date determination, it could look like this:select INVOICEDATE from CUSTINVOICETABLE where INVOICEDATE > SYSDATETIME()Thus, the change should take place in the database synchronisation, where the views/entities are created.
Category: General
It would be great to have such enhancement in the current design by eliminating such limitation. I totally agree that it would make a lot of sense to have dynamic date filtering.
Category: General
Jason Green (administrator) on 3/18/2024 2:31:30 PM
This is already possible using the advanced query syntax, specifically using the "T" operator for today's date or the syntax that allows SysQueryRangeUtil methods. See https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/fin-ops/get-started/advanced-filtering-query-options for more details.