395
I need to delete a work order with no customer attached. Since there is no customer attached hence I cannot recall work orders on POS to delete the order. How can I find all work orders that do not have any customers attached, then create a dummy customer and attach it to work orders and then recall the work order and delete them manually? Or can that work order be deleted with sql command?
Thanks
*This post is locked for comments
how about:
update o set o.customerid=(select max(id) from customer) from [order] o
left join customer on customerid=customer.id where customer.id is null
which will set the customer to your last created account, or you could pick the id of some favorite customer to use instead.
Thanks Matt, I have successfully executed the command however item committed on item properties still shows more quantity than total work order quantity. Eg itemcode "A" properties>inventory shows item committed 6 but if I run a report to show order/quotes, thee are only 2 quantity shows on work order. Where are the remaining 4 must have gone? before the execution of the query, report use to show there 4 quantity with valid work order number but with no customer attached. Now after the query that work order is not available in a report. but item committed still shows the item quantity properties. I think the best way to remove those item quantity is to find all work orders that do not have any customers attached, then create a dummy customer and attach it to work orders and then recall the work order and delete them manually. How can I do this?
I already gave a query that would do that. Please take my suggestion to contract with an experienced DBA or your MS Provider to perform these changes, thanks.
I want to remove work orders with no customer attached entirely. But before that I need to find work orders with no customer attached. The query you gave me before doesnot seems to manipulate item code nor work order number. I have managed to find few work order with no customer attached through reports>order/quotes>work order detail. What is the query to execute on 51 to delete that particular work order? Sorry to be pain.
If you need the workorder removed from the store, then send in a worksheet 51 which will also delete at hq the next time 401 worksheet sycronizes. It sounds like your select query turned up more results than just the one order you wanted to remove. You may want to be more specific in your statement so you don't remove more entries than you had intended.
Thanks Matt for your help. Query seems to be correct. However on closed column most of them are ticked and some are unticked. Will it make a difference if I run the query you have stated on this situation? Where do I run the query to delete these records? on Administrator or run Worksheet style 51 from HQ Manager? Do I use both the queries at the same time or run at a time? Appreciate your help. Sorry for being a novice...
This query will find orders without valid customer records:
select * from [order] left join customer on customerid=customer.id where customer.id is null
If those records are the ones you want deleted, you can do that with these two queries:
delete e from orderentry e join [order] o on e.storeid=o.storeid and e.orderid=o.id
left join customer on customerid=customer.id where customer.id is null
and
delete o from [order] o
left join customer on customerid=customer.id where customer.id is null
#1
André Arnaud de Cal...
291,431
Super User 2024 Season 2
#2
Martin Dráb
230,503
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
