Our posts about making the most of data in F&O (via the interface only) have been getting a lot of love from the community, with “Filtering in D365 F&O” and “If you can’t beat ’em, get joining some tables!” proving to be among our most popular posts to date.
The Advanced filter or sort form (known as SysQueryRangeUtil to AX2012 users) brings more controls like:
- table joins (already covered in If you can’t beat ’em, get joining some tables!)
- filtering by multiple columns with advanced syntax
- multiple level sorting, i.e.
- saved queries (not to be confused with saved views)
We’ll cover the remainder of these in this post. First a quick reminder – you can find the Advanced filter or sort form in most views, under Options in the Action pane:
Filtering
The ability to filter on a column is fine, if you’re just looking for one value, in one field. But what if you’re trying to find all Purchase orders, (i) created for any supplier containing the word “Hardware”, (ii) created in the past 8 years, with (iii) a status of Invoiced and (vi) for the Operations department? Time for an advanced filter!
And just like that, here it is…
You’ll see that our custom filters are defined on the Range tab. You can choose to add or remove fields from the full range available (or join more, if you need to), and define the criteria for each:
- To find any supplier containing “Hardware”, we need to use *Hardware*. Other similar shortcuts are listed here.
- To find the Operations department financial dimension (assuming we don’t have an encyclopedic memory), we can click the ++ icon to look it up.
- You might struggle to remember the exact name of a field – in which case you can use the standard column search feature to find it, by clicking on the column header “Field”, as shown in this animation:
- Lastly, for the previous 8 years, we can use an advanced date query. More of these can be found in the table below:
| To achieve… | …use this | For example… |
|---|---|---|
| A date relative to now | Day (_relativeDays=0) | For Tomorrow enter (Day(1)) or for Yesterday enter (Day(-1)) |
| A range of days, relative to current day | DayRange (_relativeDaysFrom=0, _relativeDaysTo=0) | For Previous 60 days and next 30 days enter (DayRange(-60,30)) |
| A range of months, relative to current month | MonthRange (_relativeFrom=0, _relativeTo=0) | For the Previous one month enter (MonthRange(-1,0)) |
| A range of years, relative to current year | YearRange (_relativeFrom=0, _relativeTo=0) | For the Previous two years enter (YearRange(-2,0)). |
| Any date/time before a relative date | LessThanDate (_relativeDays=0) | For all dates more than a week ago, enter LessThanDate(-7)). |
| Any date/time after a relative date | GreaterThanDate (_relativeDays=0) | For all dates more than a week ahead, enter GreaterThanDate(7)). |
| Any past dates/times | LessThanUtcNow () | |
| Any future dates/times | GreaterThanUtcNow () |
Saving queries
We can save a custom query – such as the one we’ve just made – in one of two ways.
- Either click OK and save as a Saved view or,
- Stay in the SysQueryForm, pick “Query used” from the Select query dropdown, click Modify… and Save as, then type a Name and click OK:
Sorting
Now let’s say we want to sort a view by more than one column. For example, we want to sort our Purchase orders by the date they were created (in descending order) and then by Supplier name, in ascending order.
Under the sorting tab, you can add all the fields that you wish to sort by, defining the sort/search direction, and re-ordering as necessary to set your priorities. Nice!
Conclusion
Whilst it’s easy enough to Export to / Open in Excel from D365 F&O, and manipulate your data there every time you need it, why not save yourself some time by creating and saving your commonly used custom queries in the application itself?
Remember, you can also add saved views to any out-of-the-box or a custom workspace – you really can make the system work for you!
