HI All
Has anyone managed to extract the data from D365 trial balance report via Alteryx ?
Regards
CT
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.
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
I have not extracted such files so not sure about all the problems.
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
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
Thank you very much Shanker/Raj....much appreciated.
User | Count |
---|---|
52 | |
27 | |
25 | |
24 | |
21 |