Account Schedules for Custom Chart of Accounts

(1) Share

Report

Posted on by Colin Pich

We have a new client who we brought onto D365 Finance & Operations Business Edition. The client requested a custom Chart of Accounts. I was able to implement that Chart of Accounts without issue into each of the client’s 4 companies using the Edit in Excel feature.

However, when attempting the configure the Account Schedules to run an Income Statement and Balance Sheet against this new Chart of Accounts I found that there was no Edit in Excel feature. Therefore I created a custom Account Schedule for the Income Statement manually yesterday for one company. It seems that this Account Schedule has been overridden. I left the new Account Schedule in as the default in General Ledger Setup, but I do not recall clicking the Generate Account Schedules button.

Is there a different process that I could be following to modify the Income Statement and Balance Sheet of the four companies? Or is this the only method available to me? Is my issue that I left the new Account Schedule as the default in General Ledger Setup overnight? Will I have to advise the client that he will have to take it out after generating his report each time, or he risks the possibility that the new Account Schedule will become over-ridden?

Thanks,

Colin

Categories:

Reports and Business Intelligence

I have the same question (0)

All responses (12)

Answers (3)

Verified answer

Mustufa Shehreyar 1,070 on at

Like (0)

Report

@Colin: You can use the Excel Add-in to edit account schedules from excel. The Table to select will be Account_Schedules.

Note: Rename your Account Schedule Code to something like "10" as when you pull the table in excel it will only show the Account Schedule which was on top in the list.

Apart from that I'm not sure what caused the overwrite/refresh as only the default ones are refreshed not the custom ones.

Was this reply helpful? Yes No
Colin Pich on at

Like (0)

Report

Hi Mustufa,

Thanks for the reply. However, I am not sure how to access the table Account_Schedules. When I click on the Edit In Excel button on the ribbon it just brings up a spreadsheet that allows me to edit the listing of Account Schedules, not the actual Account Schedules themselves. Please see screenshots.

Please let me know if i'm on the right track.

Thanks,

Colin

Was this reply helpful? Yes No
Verified answer

Mustufa Shehreyar 1,070 on at

Like (0)

Report

@Colin: In order to be able to upload anything into D365 from Excel follow the following steps:

Step 1: Open Microsoft Excel and Navigate to the “Insert” Tab and Click on “My Add-ins”

Note: For the add-in to be available in excel you must have used the "edit in excel" functionality once.

Step2: Click on the Microsoft Dynamics Office Add-in.

Step3: You will see the following Add-in in excel: (It might ask you to sign-in the credentials will be the same which you use for D365)

Step 4: Click on Add Server Information and paste the following link into the Server URL then Click “OK”

https://<tenant url>.financials.dynamics.com/

Step 5: Click on the Settings button.

Step 6: Select the Company ID after selecting Click “OK”.

Step 7: Now Click on Design

Step 8: Click on Add Table

Step 9: Select the Table you wish to select.

This list will contain all tables in D365, some commonly used tables are:

1.       Account Schedule

2.       General Journal

3.       Customer Card

4.       Vendor Card

Step 10: After you select the table, you will be required to select the fields.

Step 11: After Selecting all the fields click on Refresh to update the existing data present in the table.

Step 12: The Data present will automatically be reflected.

If you have entered new data and accidently click refresh you might lose the newly entered lines.

Conclusion: This feature can be a little tricky to use but once perfected can be used for mass entry of data in any tables, there are some limitations with respect to non-editable fields. Also I usually enter one record manually into a table then refresh to see which fields I need to use.

Was this reply helpful? Yes No
Colin Pich on at

Like (0)

Report

Wow Mustufa,

Thanks so much for the very detailed instructions, it is greatly appreciated. I have followed them through, however when I get to the add table step Account_Schedule is not an available option.

I am wondering, is your solution for Business Edition, or Enterprise Edition? Because in this particular case I am working with Business Edition.

Thanks,

Colin

Was this reply helpful? Yes No
Verified answer

Mustufa Shehreyar 1,070 on at

Like (0)

Report

@Colin: You will need to create a Page in web services in order for it to appear in the list of tables in excel.

Was this reply helpful? Yes No
Colin Pich on at

Like (0)

Report

Hi Mustufa,

I have been attempting to create the Account_Schedules Page in web services. However, the Account Schedule Object Name isn't available for me, as I can only choose an Object Name that has an Object Subtype of List.

Thanks,

Colin

Was this reply helpful? Yes No
Colin Pich on at

Like (0)

Report

Hi Mustufa,

After further investigation I have found that after clicking on the Insert G/L Accounts button I am able to add on multiple accounts to the Account Schedule. I can do this by clicking on the "..." button and choosing the Select More option, then I choose each account I wanted to add onto the Account Schedule and added them all at once. Previously I was adding an account one by tedious one.

This has significantly reduced the time it takes to create an Account Schedule from scratch. I feel so silly that I did not figure this out earlier. Thanks for all of the help in modifying Account Schedules using the Excel Add-In.

Regards,

Colin

Was this reply helpful? Yes No
MCarrero 774 on at

Like (0)

Report

Hi Mustufa, I am curious as to how to accomplish this. Can you elaborate?

Was this reply helpful? Yes No
Suggested answer

Mustufa Shehreyar 1,070 on at

Like (0)

Report

@Mary: It is as I mentioned in my previous posts.

If you have any confusion please share your e-mail address or send me a Private Message.

Was this reply helpful? Yes No
MCarrero 774 on at

Like (0)

Report

I was able to get the table and fields into my spreadsheet with this method...but it won't publish an account schedule back in. How do you do that?

Was this reply helpful? Yes No