CData connection to MS Dynamics 365 Business Central issues with "lines" tables

Question

I am connecting to MS Dynamics 365 via CData to get our data tables. I can access most tables, BUT any of the lines tables (salesinvoicelines, journalLines, etc.) are not returning data, as they usually want some predefined ID to pull line specific data. For example, for sales invoice lines, I would need the specific document ID to pull data. The lines table will NOT run wide open, and so far, I can only get it to return data if I do a pre-SQL statement such as "WHERE documentId IN (x,y.z). Basically, I have to predefine the specific document ID's, but can't just bump the sales lines table against my sales orders for the month and join the data together to return lines where the document ID matches. So far, only the WHERE....IN statement is all that works.

Having this issue with journal lines also: it wants the journal batch ID or journal ID to pull the line detail.

Any thoughts on how to just run the data and get line-item detail without jumping through SQL hoops to get there?

MrJWoo · Answer

Raj, I did try doing a join, for a specified period and the Alteryx workflow ran and ran but would never finish. I mean the referential join is the most intuitive, like when using access queries. When I narrowed down to just a few ID's and put them in a pre-sql "where...in" statement I could get those specific lines to join...but just joining the tables to reference the ID from one to the document ID in the other...it was just futile. I had our SQL guru look at it and he was baffled as well.

The other similar issue I ran into was with the lines data for the GL entries. I could run GL entries from the GL table, but they lacked the department dimension (why department is not an actual field in BC is beyond me). I tried using the GL ID to join against a dimensionsetlines table ID field, but the workflow kept erroring out.

Raj · Answer

To get data from the line tables, you need to give a specific ID of the transaction, like the specific sales invoice or journal. You mentioned that you can only get data from these tables by giving a list of specific IDs, but you want to get all the data for a certain period of time.

One way to do this is to join the line table with the general information table, using the ID of the transaction as the connection. This way you can get all the data for a certain period of time from the general information table and match it with the corresponding data from the line table. Another way is to use a subquery, where you first get the list of specific ID's from the general information table and then use those ID's to get the data from the line table.