Dear @RobertU 

What connector are you using for Business Central?

It is in ODX Server so i don't have any adapters there. It is the CDATA ADO.NET Provide for Microsoft Dynamics 365 Business Central 2023 

Okey. Can you show me the data selection rule you’ve created?

You can also try to create a Query Table. Pick a the table that you want, write the query and put in the where:

SELECT
*
FROM [TableName]
WHERE [Posting Date] >= '2024-01-01’

Sync the data source and create a transfer task with just that table and run it.
Sometimes the CDATA connectors are a bit iffy. They might not convert the query to correct ‘Microsoft Business Central Webconnector’ language so to say. (I've had this with a DB2 connector)

What version of BC are you running? Maybe you can connect directly to the SQL Server instance?

The bottom data source is the one coming from ODX Server. The other ones are NAV adapters in the ODX busines unit that use a custom filter. As custom filters are not allowed on ODX server i tried working around it with a normal data selection rule

I can't use the query table as i only need to do this for our Development environment. Test and Production need all entries. I use a product variable to determine that.

The question seems to be about DSA filtering and that is called a Data Selection rule.

However if you want to do the filtering of the rows in the ODX itself, which is what @danielalludes to. The following is the guide

You can’t do dynamic date filtering when applying a rule on a table mapped from an ODX, it will have to be a fixed range.

No i don't want to filter in the ODX server because that is the source for all my environments. I only want to filter in a specific environment

What I want is to limit the amount of entries coming to the DEV environment. For TST and PRD i need all the entries. 

Hi @RobertU 

If you applied the rule after a while having this table, you will need to run a full load to see the effects of the filter.

Yes, but when i try to load the table i get the error:

Conversion failed when converting date and/or time from character string.

@RobertU ,

I'm looking into it now, but using en enviromental variable to dynamically switch the filter between the DEV or TST or PRD enviroment is not possible in the new version.
I might be wrong though...

We use the legacy version of TX

@Thomas Lind ,
I believe this is between the ODX and the DSA. So we have one ODX which provides data for the DEV, TST and PRD enviroment. Robert needs a filter which dynamically switches from Posting_Date > '2021-01-01’ to ‘No Filter’ when the instances is tranferred to the next step in the release pipeline (So from DEV to TST)

That would indeed be what we need however right now i cannot even get the normal datefilter working without any environment variable

Can you show us how the filter is set up?
Maybe you are using quotes in the input box which you do not need?
 

You can also check in the ODX server what the Date Type of the Posting_Date is? Maybe the interpretation of the date type is not correct?

It is setup the same way as i would normally do this. 

It is setup as date in ODX server

@RobertU I've tried it in my test enviroment. The only difference I see is that my table uses a datetime and not just a date. Don't think that is the issue.
Can you load the table without the filter? Maybe the issue is not in the filter but in getting a Posting_Date value in a DATE data type? 
Are there any other transformation which might through this error?

The table loads fine without the filter. The filter works fine if the data source is a business unit date or datetime. I tried both and that works. It just won't work if the datasource is ODX server

@RobertU 
For the dynamic switch of the filter I use this:
Create a Project Variable:
 

Add a new one with the name Enviroment - type: system - Value: Enviroment name
 

Right click on your filter and choose: Add Usage condition

Select the Enviroment project Variable and set it so DEV (or whatever the Enviroment is called)
 

Yes, the dynamic switch for Environment we already use a lot and works. I also checked  this when ODX server is used and that works also.

That part is not the problem, the problem is with the notation of the date

@RobertU Which TimeXtender LTS version are you using?

I tested the following for my BC data source in 20.10.45 and did not experience any issues 

can you please try creating a new table with only the following mapping and test the filter again?

also is DWH_GLEntry the original table name in Business Central? I cannot find a table with this name in my BC environment, is it a custom table? Can you please share the name of the ODX table shown in the below screenshot in the data source explorer

We use version 20.10.45.64

The DWH_GLEntry is part of an extension that has been created for us to get all the columns we need from the BC system. The standard microsoft stuff is not enough.

I tested creating a new project and then do the exact same thing with just the one mapping.

Now it works. 

So now the question is, why does it work with in this project with only the one mapping and not in the other project with multiple mappings.

When i try to run this one i get:

Creating a new table in our main project also works btw with just the one mapping