D365 Trial Balance Report - Alteryx

[cterrence]

HI All

Has anyone managed to extract the data from D365 trial balance report via Alteryx ? 

Regards

CT

[Raj]

Here's a step-by-step guide on how to extract data from a Dynamics 365 (D365) trial balance report using Alteryx:

Step 1: Install and configure the Dynamics 365 Connector in Alteryx Designer. This will allow Alteryx to connect to your Dynamics 365 instance and extract data.

Step 2: Create a new workflow in Alteryx Designer and add the Dynamics 365 Connector to the canvas.

Step 3: Configure the Dynamics 365 Connector by inputting your Dynamics 365 instance URL, username, and password.

Step 4: Use the Select tool to select the fields from the trial balance report that you want to extract. You can also use the Filter tool to filter the data based on specific criteria.

Step 5: Use the Dynamics 365 Connector to retrieve the data from the trial balance report. The connector will retrieve the data from the specified fields and filter criteria.

Step 6: Use the Output tool to write the extracted data to a file or database.

Step 7: Run the workflow and verify that the data has been extracted correctly.

Note: Make sure that you have the appropriate permissions to access the D365 trial balance report, and that the data is being extracted in compliance with the company data governance policies.

[cterrence]

Thank you Raj for your reply

But I should have given more details

We have connected Alteryx to a copy of the prod DB. And according to Dynamics 365, there is no  report as Trial Balance report in the DB. This is written in x++ and following tables are used to get to the final trial balance report

Tables

DimensionAttributeValueCombination table

DimensionFocusBalance table

GeneralJournalAccountEntry table

LedgerTrialBalanceTmp table

I do not have the x++ query with me and I really dont know what goes behind the query once your select your criteria and run the report in D365. Once the query is ran, I am assuming that data is extracted and sits in LedgerTrialBalanceTmp table and this is where the data is retrieved in D365

Have you extracted the report like the way you mentioned in your reply ? Please let me know

Regards

CT

[Raj]

I have not extracted such files so not sure about all the problems.

[cterrence]

Apologies for using this thread for another question. My question is not appearing in the discussion forum

I am new to Regex. I need to split the bank into different columns

16,399,162435,,DEPOSIT - CASH 66001,,66001_TRAN EFF DATE 230131
16,399,106035,,DEPOSIT - CASH 65601,,65601_TRAN EFF DATE 230131
16,399,237145,,DEPOSIT - CASH 65601,,65601_TRAN EFF DATE 230131
16,399,285640,,DEPOSIT - CASH 65601,,65601_TRAN EFF DATE 230131
16,399,217210,,DEPOSIT - CASH 65601,,65601_TRAN EFF DATE 230131
16,399,217160,,DEPOSIT - CASH 64001,,64001_TRAN EFF DATE 230131
16,399,840663,,DEPOSIT - CHEQUE(S) 64001 #Chq:1,,64001 #Chq:1_TRAN EFF DATE 230131
16,399,234115,,DEPOSIT - CASH AGENT NO 63201,,AGENT NO 63201_TRAN EFF DATE 230131
16,399,142245,,DEPOSIT - CASH 33901,,33901_TRAN EFF DATE 230131
16,399,186515,,DEPOSIT - CASH 31001,,31001_TRAN EFF DATE 230131
16,399,306765,,DEPOSIT - CASH 040501,,040501_TRAN EFF DATE 230131
16,399,162980,,DEPOSIT - CASH ABCD 43001,,SMGB43001_TRAN EFF DATE 230131
16,399,74910,,DEPOSIT - CASH ABCD 43001,,SMGB 43001_TRAN EFF DATE 230131
16,399,31310,,DEPOSIT - CASH ABCD 43001,,SMGB 43001_TRAN EFF DATE 230131
16,399,137195,,DEPOSIT - CASH ABCD 43001,,SMGB 43001_TRAN EFF DATE 230131

With my limited knowledge, I have entered this - (\d{2}),(\d{3}),(\d+),,(\w+-\w+)(\d+),,(\d+)_(\w+)(\d+)

This is throwing empty rows. See attached

 I want the output like this 

16    399      162435       DEPOSIT - CASH                                   66001                             66001                                                TRAN EFF DATE               230131

16    399     137195       DEPOSIT - CASH ABCD                     43001                            SMGB 43001                                TRAN EFF DATE              230131

16    399     840663       DEPOSIT - CHEQUE(S)                       64001                            Chq:1    64001  Chq:1                TRAN EFF DATE             230131
16   399      234115        DEPOSIT - CASH AGENT NO        63201,,                          AGENT NO 63201                         TRAN EFF DATE          230131

[ShankerV]

Hi @cterrence 

One way of doing this.

Many thanks

Shanker V

[Raj]

hi @ShankerV  I think you just missed the last separation (thanks for sharing)

@cterrence find the updated regex

(\d+),(\d+),(\d+),,(\w+\s\-\s.+)\s(\d+\s{0,1}.+),,(\u{0,}\s{0,1}\u{0,}\s{0,1}\d+\s{0,1}.+)\_(.+)\s(\d+)

This might help

[cterrence]

Thank you very  much Shanker/Raj....much appreciated. 

[cterrence]

I ran this query and it worked for all lines except one - 

16,399,162980,,DEPOSIT - CASH SMGB43001,,SMGB43001_TRAN EFF DATE 230131

Please assist