Skip to main content

Notifications

Small and medium business | Business Central, N...
Suggested answer

Change customer in Posted Sales Invoice Header from SQL Server

Posted on by 5

Hello,

Can I change Customer Number , Customer Name for Posted sales invoice through SQL server in BC database ?

like update 

  • Sales invoice Header
  • Sales Invoice Line
  • Customer Ledger Entry
  • Detailed Customer Ledger Entry
  • G/L Entry
  • VAT Entry
  • Value Entry

Just update statement from customer to another customer 

Regards

Categories:
  • Tech-Lucky Profile Picture
    Tech-Lucky 796 on at
    RE: Change customer in Posted Sales Invoice Header from SQL Server

    fully agree with you @Greg Enns we should not share such solutions publicly where users are also seeing this and demand their partner for that, and maybe if the solutions go into the wrong hand then they can be a disaster too.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: Change customer in Posted Sales Invoice Header from SQL Server

    Yes, and especially when the developers also finds blog post out there telling them how essay it is to change posted data.

    My recommendation will always be to not change posted data beyond what the standard functionality allows you to do  unless you discuss it with your Business Central partner first.  I done my share of modifying posted data over the years, but I have also spent almost every day for the last 30 days working with those tables. So I know those tables better than the map of my home city.

  • Greg Enns Profile Picture
    Greg Enns 1,102 on at
    RE: Change customer in Posted Sales Invoice Header from SQL Server

    I agree. The concerning thing is that with this functionality that MS has created, it makes this kind of change seem like a normal/okay thing to do. More users will request this type of change, and developers will be less hesitant to make the change since the system provided an OOB process for allowing it.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: Change customer in Posted Sales Invoice Header from SQL Server

    Doing these kind of changes have always been available for developers. So that is not anything new.

    The problem is as you describe that a lot of people working with Business Central and doing development around Business Central does not understand the consequences of what they are doing.  In the right hands these tools are handy in some situations - in the wrong hands they can completely ruin your data integrity. Because you can do certain things within Business Central and with the AL language does not mean that it is a smart thing to do it.

  • Greg Enns Profile Picture
    Greg Enns 1,102 on at
    RE: Change customer in Posted Sales Invoice Header from SQL Server

    Zhu--I read your post about customizing this Posted Document Update functionality. That is SCARY what they are opening the system up to for a developer to change. In your example Zhu, you showed we could change Payment Terms. But that will not update the Vendor/Customer Ledger. And that is a minor issue compared to other things users might ask to have changed--like changing CustomerNo as OP has started this conversation with. So many people do not realize all the ledgers and entries that happen behind the scenes during posting. The posted document is just a small final output. Be careful here everyone.

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,378 Super User 2024 Season 2 on at
    RE: Change customer in Posted Sales Invoice Header from SQL Server

    Also, if you're using the SaaS version, you don't have access to SQL Server at all......Of course, you can also modify it directly via customization, but as the experts mentioned above, never do this.

    PS: If you want to modify other data, you can refer to the following solution.

    How to extend the Posted Document Update page (A solution for editing Posted Document)
    https://yzhums.com/24923/

    Thanks.

    ZHU

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: Change customer in Posted Sales Invoice Header from SQL Server

    I agree with all those here who say you should not do this. Make a correction posting instead.

    Even though it is technically possible to change the data through SQL server you will easily end up in a state where you can compromised the integrity of your database.

    Or at least consult someone who is very very experienced with Business Central and have them assist you.

    There is a procedure in the system for renaming customers. Then all the related data is modified in a controlled manner. That function can be replicated but it requires very good knowledge of the system.

  • NAV2BC02 Profile Picture
    NAV2BC02 101 on at
    RE: Change customer in Posted Sales Invoice Header from SQL Server

    I agree and data should not be changed via SQL.  There are a lot of related tables that if missed can cause record locks, and process issues.

    Thanks!

  • Suggested answer
    NAV_with_Narang Profile Picture
    NAV_with_Narang 2,234 Super User 2024 Season 1 on at
    RE: Change customer in Posted Sales Invoice Header from SQL Server

    You should never do such corrections from SQL. In BC/NAV there is an option to reverse your incorrect invoice and process a new invoice with correct details

    learn.microsoft.com/.../sales-how-correct-cancel-sales-invoice

    Doing such reversals from SQL can seriously hamper your financials in BC/NAV and should never be done, even if it is the last resort. In some countries/localizations this activity is termed as a financial fraud

  • Suggested answer
    Tech-Lucky Profile Picture
    Tech-Lucky 796 on at
    RE: Change customer in Posted Sales Invoice Header from SQL Server

    Yes, you can but this is not recommended at all if you have selected the wrong customer please try to post the reversal entry as a Sales Credit memo or Sales Return order.

    Because there are many other tables as well where the customer No and details flow and you might skip them by doing so it may create issues in the future.

    some of the tables I know are, the Sales Shipment header, Sales Shipment Line, and Item Ledger Entries, if any taxes are there then the related taxes ledger entries for VAT or GST accordingly.

    Mark my answer as verified if it helps you

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!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,064 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans