Dynamics Community Forum Thread Details

Can I skip certain rows in my CSV detail source in Integration Manager?

I have a single CSV file serving as my header source and detail source for a General Journal integration, and it is working well. However, I would like to improve the integration. See the below table from my CSV. Is there a way to tell Integration Manager to "skip" the "detail" rows that have "Posted" in the "Status" column?

Transaction Type

Transaction Date

Source Document

Reference

Blank

Account

Debit

Credit

Status

Notes1

Notes2

Notes3

Notes4

Notes5

7/1/2020

Reclassify Expenses

01-000-5531

1.01

Posted

7/1/2020

Reclassify Expenses

01-000-5470

1.01

Posted

7/2/2020

Reclassify Expenses

01-000-5531

1.01

7/2/2020

Reclassify Expenses

01-000-5470

1.01

I have tried setting a filter on my detail source in the source properties. The criteria that I used was "Status Not Like Posted." But it didn't work. I have also tried setting a filter on my header source, which I don't think is what I want to do, but that didn't work either.

Here is the error that I get:

Opening source query...
Establishing source record count...
Beginning integration...
DOC 1 ERROR: Field 'Reference' does not have a default value.
DOC 2 ERROR: Field 'Reference' does not have a default value.
ERROR: Max Errors Exceeded at 2, Integration Canceled
ERROR: Integration canceled during document integration.
Integration Failed
Integration Results
2 documents were read from the source query.
2 documents were attempted:
0 integrated without warnings.
0 integrated with warnings.
2 failed to integrate.

Categories:

Microsoft Dynamics GP

Hello,

Adding a filter would be the right way to go. After you add the filter, and then right click on your source for your detail in GL and choose Preview, does the data preview properly and exclude the transactions that you are expecting to exclude?

If not, I would make sure to refresh your columns on both the header source and detail source, and recreate your Query Relationships to make sure they are all up to date with your new posted field. It may be best to even have the posted field be Y or N so that you can filter by Y and N rather than having blank space for the data that has not been integrated.

Setting up an advanced ODBC source to your excel file might give you more control over your filters as you can essentially write a SQL query rather than using the filter tab under properties.

These are a few of the areas that i would recommend checking in this scenario. If you get too stuck or run out of ideas it may be simplest to open a support case directly with us so we can stream in to see the issue first hand.

Thanks,

Isaac Olson

Microsoft Support

Quick Links

Leaderboard