Delete All Sales Quotes within Date Range

(0) Share

Report

Posted on by Community Member

I've recently been wanting to clear down thousands of sales quotes in our NAV 2015 installation, but from reading up there is not a built-in routine to do this.

Initially I saw some articles surrounding modifying the Delete Archive Sales Quote versions report but I'm not too knowledgeable in that area.

Instead I thought about doing it via a couple of SQL Queries - I'm wondering if this is fine to do or would this cause problems?

1st Query - Delete from Sales Header

DELETE
FROM [Sales Header]
WHERE [Document Type] = '0' AND [Document Date] < '12/31/2017'

This worked perfectly and cleared down the Sales Quotes in the RTC

2nd Query - Delete Orphaned Sales Lines

DELETE
FROM [Sales Line]
WHERE NOT EXISTS (SELECT No_ FROM [Sales Header]
WHERE [Sales Line].[Document No_] = [Sales Header].[No_])

Any help would be much appreciated.

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Stefano Demiliani 37,166 Most Valuable Professional on at

Like (1)

Report

Why not directly using C/AL?

A simple codeunit that loops through Sales Header table, apply filters and triggers a DELETE(TRUE) operation. This will automatically deletes the lines and all the other related tables.

However, the SQL code works. If you want to be more clean, you should also delete Sales Comment Line table.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Stefano, Thanks for your reply - unfortunately my knowledge on C/AL isn't that great so I wouldn't be sure where to start. Do you know where I could learn to do this? Would I need a specific license for developing a codeunit?

Thanks!

Was this reply helpful? Yes No
Suggested answer

Stefano Demiliani 37,166 Most Valuable Professional on at

Like (1)

Report

You can create a new codeunit with a free ID (for example 50000) and in the OnRun trigger write this code:

MyDate := DMY2DATE(31,12,2017);

SalesHeader.SETRANGE("Document Type",SalesHeader."Document Type"::Quote);

SalesHeader.SETFILTER("Document Date",'..%1',MyDate);

SalesHeader.DELETEALL(TRUE);

where:

SalesHeader DataType: Record Subtype: Sales Header

MyDate DataType: Date

Then just run that codeunit.

Was this reply helpful? Yes No
Suggested answer

Suresh Kulla 50,245 Super User 2025 Season 2 on at

Like (1)

Report

You could import this tool and delete the records from table 36 and 37

www.olofsimren.com/record-deletion-tool-for-dynamics-nav-2015

Was this reply helpful? Yes No