Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / update work order usin...
Microsoft Dynamics RMS (Archived)

update work order using sql

(0) ShareShare
ReportReport
Posted on by moshe 90

hi i am making a application that will handle work orders. and i need a way to commit a item in a work order and some times even change the price.

so first i was thinking that i can just do a simple update like this

Update OrderEntry Set QuantityOnOrder = 0,QuantityRTD = 1,Price = 12.99,Taxable = 1 Where ID = 20144

but my question is what else needs to be updated? 

i want the inventory to update, and the customers balance to update, and everything that would of updated when i do it trough RMS

is there a documentation on how to do such things?

and if sql updates is not the way, but rather some kind of add-on or a dot net dll that can do it, where do i find that

*This post is locked for comments

  • moshe Profile Picture
    moshe 90 on at
    RE: update work order using sql

    what i am building is a software that will connect to my customers web site, my customer creates a work-order (for a customer called web) for all items he wants to sell on the web, my software takes this work-order and uploads it to the web, when a order comes in on the web i want my software to commit the item sold on the web, and it will need to process 40 to 60 orders a day

    i dont need each order in RMS, i have one work order and commit each item for every order.

    in RMS i would look-up the work-order and commit an item.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: update work order using sql

    Moshe it sounds like you are looking to build an application that does what RMS POS already does (commit an item from a WO, change the price, update the inventory and customer balances, etc). Don't take this the wrong way but why rebuild the wheel? Either A) spend 1200 USD or whatever it is for another POS license, or B) connect to  a register that already has a POS license with freeware such as REAL VNC and remote control that POS machine to do your workorder updates. The price of a POS license will be much, much lower than the price you may end up paying for and supporting any issues that arise from the application you are trying to build. Just my $0.02.

  • Suggested answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: update work order using sql

    Hmmm, can't really tell You exactly... I think You are doing all correct, but...

    Try activating everything and the descart one by one... Can You define the exact process You are doing on the POS, step by step and maybe we can see when there is a SELECT or UPDATE or other query....

    Regards, A.

  • moshe Profile Picture
    moshe 90 on at
    RE: update work order using sql

    hi i am back again.

    i am running a trace while i am updating a work-order in the RMS application, but all i get is select statements, there is no update or insert

    and i selected this events  

    RPCStarting,SQLBatchStarting,SQLStmtStarting,SPStarting,SPStmtStarting,SQLFullTextQuery

    is there something i am doing wrong?

  • moshe Profile Picture
    moshe 90 on at
    RE: update work order using sql

    thanks for your answer.

    i already made a lot of programs using sql and i also have a little experience with RMS and i already made a few programs that are getting reports from the RMS database,

    how ever this is the first time i will do an update to the database, that's why i wanted to make sure that there is nothing out there that will do it, but using SQL Prifiler is a grate idea , thanks for pointing me in the right direction

  • Verified answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: update work order using sql

    Hi Moshe.

    First of all, doing direct SQL changes into RMS database is not recommended if You are not an expert of RMS and SQL also. So, I woudl suggest You first contact your partner for help, tips or ...

    Anyway, assuming you are working in a test environment, if you want to do it with SQL sentences, what I would do is install an SQL Prifiler (from Microsoft or some free ones, I have used AnjLab SQL Profiler), create a session, do the standard RMS Work Order procedures (new, update, delete, etc., etc.,..) and capture the SQL quieries used by the product. This quieries will give You an idea of what You need to do and what tables have to be updated...

    At the end, my recommendation would be You contact a Partner. I'm not aware of any add-on for this, but there are many partners that have different add-ons and there are many partners that could do this for You...

    If you need more help, do not hesitate in contacting me... Best regards, A.

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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans