web
You’re offline. This is a read only version of the page.
close
Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / SQL to find work order...
Microsoft Dynamics RMS (Archived)

SQL to find work orders on MS Dynamics RMS

(0) ShareShare
ReportReport
Posted on by nazza 403

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

I have the same question (0)
  • Suggested answer
    nazza Profile Picture
    nazza 403 on at
    Re: SQL to find work orders on MS Dynamics RMS

    Smashing, did the trick. Thanks very much...

  • Verified answer
    Community Member Profile Picture
    Community Member on at
    Re: SQL to find work orders on MS Dynamics RMS

    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.

  • nazza Profile Picture
    nazza 403 on at
    Re: SQL to find work orders on MS Dynamics RMS

    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?

  • Community Member Profile Picture
    Community Member on at
    Re: SQL to find work orders on MS Dynamics RMS

    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.

  • nazza Profile Picture
    nazza 403 on at
    Re: SQL to find work orders on MS Dynamics RMS

    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.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    Re: SQL to find work orders on MS Dynamics RMS

    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.

  • Suggested answer
    nazza Profile Picture
    nazza 403 on at
    Re: SQL to find work orders on MS Dynamics RMS

    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...

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    Re: SQL to find work orders on MS Dynamics RMS

    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

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

News and Announcements

Dynamics 365 Community Calls Return
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Mansi Soni – Community Spotlight

We are honored to recognize Mansi Soni as our August 2025 Community…

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans