If like me you have got an outstanding submission in the HMRC Basic PAYE tools and it shouldn't be there and there is no way to delete it; then you may have to edit the SQLLite database that it uses. Here are the steps I undertook:
1. Backup the data from within the Basic PAYE tools application.
2. Download the SQLLite tool from here: http://sqlitebrowser.org (the source code can be downloaded here). This will allow you to view and edit the underlying database.
3. Open the sqllite.db in %appdata%\Roaming\HMRC\payetools-rti
4. Look in the bptrti_submissiondirtydata table. You'll see one row that looks like the outstanding submission. Find the "id" column and note down the number for the row.
5. Execute
delete from bptrti_submissiondirtydata where id = <id>
and substitute <id> with the appropriate ID that you took note of in step 4.
Lifesave! Brilliant :) Many thanks
ReplyDeleteThankyou, this has helped me out a lot. Much appreciated!
ReplyDeleteIt was all going well until the last line.....
ReplyDeleteExecute delete from bptrti_submissiondirtydata where id = and enter the appropriate ID.
I don't understand what I need to do with the ID's. Deleting the entries just stops Basic PAYE tools from working. Any help gratefully received.
Sorry for the late reply, I didn't spot the comments in my blog.
ReplyDeleteIn step 3, using the sqlitebrowser tool, you browse the data records in the "bptrti_submissiondirtydata" table.
You'll see one of the rows in that table that looks like the outstanding submission. Find the "id" column and note down the number for that ID for that row.
Then use that noted ID number in the following query which you execute in the SQLLiteBrowser tool:
delete from bptrti_submissiondirtydata where id = [id]
Replace [id] with the number that you noted.
Good luck!
Andrew
ReplyDeleteIt seems that Sourceforge has now ended as I was redirecteed here:
https://github.com/sqlitebrowser/sqlitebrowser
Are you able please to provide updated steps to follow following this change ?
Thanks Brian
Sorry, I'm not good at spotting comments that have been applied. I've updated the main text. You can download the standalone executable from here (http://sqlitebrowser.org) and use it to edit the table.
ReplyDeleteI Cant do it :/ please can you help...on a Mac, failed submission is from a previous year
ReplyDeleteHi Paul I've just managed this myself on a Mac. I downloaded the SQLite DB Browser program and installed, then opened it. Once in the program I clicked 'Open Database' on the top left of the screen, then navigated to the 'HMRC' folder within which was a file called sqlite3.bd, so I opened that file within DB Browser.
DeleteNext go to the 'Browse Data' tab and you'll see a drop down menu called 'Table' where you can select 'bptrti_submissiondirtydata'. This brings up a table below where you can view the information; some of the values under 'description' and 'date created' will match the outstanding submission values that you've been seeing in the basic tools software.
I selected the ones I wanted to delete by clicking the line number on the far left of the table. Once all the data on that line is selected blue, you can click on 'Delete Record' above. I then closed HMRC Tools as it was still running in the background, went back to DB Browser and clicked on 'Write Changes'. When I reopened HMRC Tools the outstanding submissions had gone!!
I'm guessing that it wasn't necessary to close HMRC Tools before and restart but I was just being careful.
You, my friend, are a legend!
DeleteWhich bit are you stuck on?
ReplyDeleteUnfortunately I don't have any Mac devices, so I wouldn't be able to tell you any folders in which to look for files, but the principles should be the same.
Can you locate the sqllite.db?
And can you download a SQL Lite client tool such as sqllitebrowser?
Life saver thank you so much! I just did it on a Mac so will try & list my steps for other people. I am something of a technophobe so I'm glad it worked for me
DeleteHi Andrew, thanks for this, but where do you execute the delete command from? Is that from within SQLlite or from a command prompt?
ReplyDeleteThanks.
Hi Tim, Once I had identified the id number I just deleted the entry using the button at the top right of the browse data tab. I think Andrew wanted us to use the Execute SQL tab and type or paste the delete from bptrti_submissiondirtydata where id = [id] command with the relevant id number then use the run button (like a video player) to execute. Worked fine just deleting the entry from the browse data tab though :)
DeleteSuperb, 3 years I've been ignoring these unsubmitted warnings. All gone now though. Many Thanks
ReplyDeleteGreat this has been bugging me for 18 months!
ReplyDeleteTim and Bertie, thank you so much; this had been bugging me for 5 minutes!
ReplyDeleteThank you. Worked a treat.
ReplyDelete