Dynamics Community Forum Thread Details

Bulk delete quotes

Hello, is there a way to bulk delete / void old quotes by user, by year?

thanks

Categories:

Hi Virgilio,

There's not a way of doing this that I'm aware of. Of course, you could go one-by-one assigning the ones you want to delete to a specific batch and then delete the batch, but I'm guessing this isn't feasible due to the number of quotes at play and is why you're asking the question. You can go the SQL route if you're comfortable doing so. This will be a bit more involved. At a high level, you're removing records at least from SOP10100 and SOP10200 and possibly other tables too; for example, SOP10106 if you have these transactions have user-defined field values or SOP10202 if you have line level comments etc. Since you'd be removing directly via SQL you'd also need to do batch cleanup in SY00500 (I believe just NUMOFTRX and BCHTOTAL).

In SQL, you'd probably want YEAR(DOCDATE) and/or USER2ENT to determine year quote was entered and/or what user originally entered the quote.

In SmartList, this would be the Document Date and/or User To Enter fields filtered on SOP Type=Quote.

At a high level, getting started on SQL route could look something like this for a year cutoff approach.

DECLARE @YearCutoff INT=2021

UPDATE BatchHeader
SET
   NUMOFTRX-=QuoteSumByBatch.NUMOFTRX,
   BCHTOTAL-=QuoteSumByBatch.DOCAMNT_SUM
FROM SY00500 BatchHeader
   INNER JOIN (
       SELECT BACHNUMB,COUNT(*) NUMOFTRX,SUM(DOCAMNT) DOCAMNT_SUM
       FROM SOP10100
       WHERE SOPTYPE=1 AND YEAR(DOCDATE)<=@YearCutoff
       GROUP BY BACHNUMB
   ) QuoteSumByBatch ON BatchHeader.BACHNUMB=QuoteSumByBatch.BACHNUMB

WHERE BatchHeader.BCHSOURC='Sales Entry'

DELETE SOPLine
FROM SOP10200 SOPLine
INNER JOIN SOP10100 SOPHeader ON SOPLine.SOPNUMBE=SOPHeader.SOPNUMBE AND SOPLine.SOPTYPE=SOPHeader.SOPTYPE
WHERE SOPHeader.SOPTYPE=1 AND YEAR(SOPHeader.DOCDATE)<=@YearCutoff

DELETE SOPUserDef
FROM SOP10106 SOPUserDef
INNER JOIN SOP10100 SOPHeader ON SOPUserDef.SOPNUMBE=SOPHeader.SOPNUMBE AND SOPUserDef.SOPTYPE=SOPHeader.SOPTYPE
WHERE SOPHeader.SOPTYPE=1 AND YEAR(SOPHeader.DOCDATE)<=@YearCutoff

DELETE SOPLineComments
FROM SOP10202 SOPLineComments
INNER JOIN SOP10100 SOPHeader ON SOPLineComments.SOPNUMBE=SOPHeader.SOPNUMBE AND SOPLineComments.SOPTYPE=SOPHeader.SOPTYPE
WHERE SOPHeader.SOPTYPE=1 AND YEAR(SOPHeader.DOCDATE)<=@YearCutoff

DELETE SOP10100
WHERE SOPTYPE=1 AND YEAR(DOCDATE)<=@YearCutoff

This works in my test environment. If you decide to try this, exercise extreme caution and run an isolated test environment first.

Let me know if any questions.

Thanks,

Lance Brigham

Principal Consultant

Velosio

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL