How to filter query by page filter?

Business Central/NAV
1

I have a query with a filter field called PostingDate. I had to make it a filter field because otherwise the grouping in the query wouldn’t have worked properly. The query populates a table, that is then shown on a page. Now when a user enters a date or daterange in the page filters, I want my query to be filtered. How can I do that?

Best regards

2

To filter the query based on user input in the page filters, you can follow these steps:

  1. In the Power Query Editor, create a parameter for the date or daterange that the user will enter. Go to the “Manage Parameters” section in the “Home” tab, and create a new parameter with the appropriate data type and default value.
  2. In the query that populates the table, replace the hard-coded filter for PostingDate with a reference to the parameter you just created. You can do this by selecting the “Replace Values” option in the “Transform” tab, and selecting the column where PostingDate is located. Replace the existing filter with the parameter, like this: [PostingDate] >= #"& parameterName &"#". Make sure to include the # symbols, as they indicate that the parameter is a date.
  3. Save and close the Power Query Editor, and go to the page where the table is located. Add a visual-level filter for PostingDate, and set it to use the page filter that the user will enter. This will allow the user’s filter to be passed through to the query.

Now, when a user enters a date or daterange in the page filter, it will be passed through to the query as a parameter, and the query will be filtered accordingly.

3

Hi @Scarlett_Jonathon, please, stop doing this!!! I’m pretty shure you are using ChatGPT to generate this kind of responses, but in most cases this doesn’t help and generate confusion.

4

Hi @OK365,

First, in the page you have to create a text var and a new field (with the text var) over the repeat group in the page. When opens the page, get the filter on PostingDate and left to this new var. On the OnValidate trigger in this new control, make all you need: run the query and populate new data to the table.

Considerations: new var is a text field, you must apply the filter to PostingDate filter and then get the filter back and update the new var… customer can partially input the date and de system will fill the rest of the date.

1 Like
5

Hi pjllaneras,
thanks for your answer. I got this to work now.