Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Import balance account...
Small and medium business | Business Central, N...
Answered

Import balance accounts using an excel file

(0) ShareShare
ReportReport
Posted on by Gianluca Natili 71

Hello ALL,

I'm new on BC implementation.

I'm going to analyze the steps to load the opening balance accounts on my new Business Central. I see the documentation on this topic and I believe that I need to use the Configuration Package.

The table ID of my interest should be the n. 81, but I see there are a lot of Fields Available. I need an help to choose those of my interest.

On the excel to import, I immagine to have the account code source, the account code target and the balance value to load. If I need this, what are the Fields to use among those available?

Thanks for any help.

Gianluca

  • Gianluca Natili Profile Picture
    Gianluca Natili 71 on at
    RE: Import balance accounts using an excel file

    Right, I corrected my batch and now it is ok.

    Thank you very much

  • Suggested answer
    Dallefeld Profile Picture
    Dallefeld 11,425 User Group Leader on at
    RE: Import balance accounts using an excel file

    You cannot use the document number on multiple lines. Every line should have its own document number.

    You also should not have a GL account after every line but rather one line at the end for the total of the AP you are trying to import. This gives you the ability to proof and reconcile what you are importing.

  • Gianluca Natili Profile Picture
    Gianluca Natili 71 on at
    RE: Import balance accounts using an excel file

    Hi Kim,

    I'm working on your suggestions about loading subledger AP balance. I'm able to load the excel file but I have problem with the posting from PURCHASES JOURNAL page.

    It seems that if there are more than one document number, an error message appears: "Purchase Invoice xxx already exists." and this is the second document number on the batchprurchases-journal.PNG

    the message told me that invoice n. 107002 already exists, but is not true

    If I try to load single document number I'm able to post, but if there are more than one document number on the batch then the sytem gives me the error.

    Do you know where is the problem ?

  • Verified answer
    Dallefeld Profile Picture
    Dallefeld 11,425 User Group Leader on at
    RE: Import balance accounts using an excel file

    Yes, you are on the correct path.

  • Gianluca Natili Profile Picture
    Gianluca Natili 71 on at
    RE: Import balance accounts using an excel file

    Thank you very much, now I'm able to load data on subledger. I see result on PURCHASE JOURNAL page and I'm able to post it.

    I expect that for AR balance I can use the same approach, just use the journal template SALES and the same fields used for PURCHASES.

    Very useful help!

  • Suggested answer
    Dallefeld Profile Picture
    Dallefeld 11,425 User Group Leader on at
    RE: Import balance accounts using an excel file

    You should have the following fields, Posting Date, Document Type (Invoice, Credit Memo, Payment), Document Date, Due Date, Account Type = Vendor, Account No. = Vendor Number, External Document No. (enter the vendor invoice number), Currency Code where different from company currency, amount.

    You can import a balance account but I would suggest bring in all your AP, check your total and enter an additional line to balance the journal. Since the vendor lines will post to the AP account in vendor posting setup, this total line should offset that account. If you have more than one AP account, I suggest importing, balancing and posting one at a time.

  • Gianluca Natili Profile Picture
    Gianluca Natili 71 on at
    RE: Import balance accounts using an excel file

    If I would like to migrate an historical Credit AP on the Vendor xxx I suppose to handle the AP subledger.

    e.g. € 1000 AP historical balance with a due date 31/03/2022. I'm using the configuration package, table ID 81 with Journal template PURCHASES but I'm not sure about the fields to use or if I'm doing the right thing or if it's possible to do what I have in mind. I don't want to load the historical invoice, just an AP balance and when to pay it.

    I thought to add to the list above the field "ID Vendor" and "Due Date", I don't know if it is correct. I don't see error but I'm not able to see the record on database.

    So, my question is: if I need to load an AP balance on a specific vendor, Do I have to use the table ID 81 and the fields mentioned above? I have some doubt if I'm doing the right thing.

    Thanks

  • Dallefeld Profile Picture
    Dallefeld 11,425 User Group Leader on at
    RE: Import balance accounts using an excel file

    Yes, there is a Due Date field. There is also document type (invoice, credit memo, etc), currency code, currency factor.

  • Gianluca Natili Profile Picture
    Gianluca Natili 71 on at
    RE: Import balance accounts using an excel file

    Is it also possible to load a "payment due date" for an AR/AP balance? i.e. I would like to load 1000 EURO debit for a Vendor id and the payment due date for that

  • Verified answer
    Dallefeld Profile Picture
    Dallefeld 11,425 User Group Leader on at
    RE: Import balance accounts using an excel file

    So when you import, there are 3 fields that are required the Journal Template, Journal Batch and line No. For general ledger balances, I use GENJNL as the template, for AR I use SALES and for AP I use PURCHASES. I use DEFAULT as the Batch.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans