To homepage To homepage
Question

Error using data selection rule in DSA with data source ODX server

  • 22 January 2024
  • 33 replies
  • 241 views
R
Rank

We are just started to use ODX server as a source for our countries that are migrated to Business Central in Azure. On our DEV environment i want to limit the data to only get entries after a certain date. Normally i would use a data selection rule on Posting Date Greater or Equal 2021-01-01

When i want to do that on ODX server as a data source i get:

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

I have tried every date notation i can think of but i always get this same error.

Anyone any idea on how to filter dates from the ODX Server?

33 replies

daniel
Rank
Userlevel 4
Badge +5

Dear @RobertU 

What connector are you using for Business Central?

R
Rank

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 

daniel
Rank
Userlevel 4
Badge +5

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

daniel
Rank
Userlevel 4
Badge +5

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?

R
Rank

 

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

R
Rank

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.

Thomas Lind
Rank
Userlevel 6
Badge +5

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.

 

R
Rank

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

R
Rank

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

Thomas Lind
Rank
Userlevel 6
Badge +5

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.

R
Rank

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

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

 

daniel
Rank
Userlevel 4
Badge +5

@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...

R
Rank

We use the legacy version of TX

daniel
Rank
Userlevel 4
Badge +5

@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)

R
Rank

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

daniel
Rank
Userlevel 4
Badge +5

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?

R
Rank

 

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

 

R
Rank

 

It is setup as date in ODX server

daniel
Rank
Userlevel 4
Badge +5

@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?

R
Rank

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

daniel
Rank
Userlevel 4
Badge +5

@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)
 

 

R
Rank

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

Christian Hauggaard
Rank
Userlevel 6
Badge +5

@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

 

R
Rank

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:

 

R
Rank

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

Reply


Terms and ConditionsCookie settings