Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Is there any query tha...
Microsoft Dynamics RMS (Archived)

Is there any query that can do this?

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello,

1. Is there any query that can do the following please? I need to select some items with 11 digit lookup code from a certain supplier and add a zero in front of all the selected lookup codes to make it 12 digits with a leading zero.  I must have messed up when I was working on the excel as the lookup codes in excel deleted all leading zeros before importing.  I have to do this at both HQ and Stores. 

2. If the above could not be done, then I would have to just delete everything and try the import process again.  What would be a query to delete all items from a certain supplier? I have no issue with deleting because I haven't started running the POS system yet.  I only tested ringing just few items so I'm thinking it would be fine to delete items.  Is that right?

Also, I would really hope to work with #1 method because my import tool only works for store; For HQ, it's not able to import data for store prices tab, store costs tab, and store quantities tab.

Thank you.

Al 

*This post is locked for comments

  • TheNeos Profile Picture
    TheNeos 1,335 on at
    RE: Is there any query that can do this?

    Glad to know that everything is fine.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Is there any query that can do this?

    Hi, I just ended up deleting all items in the database and re-imported everything.  It looks like everything is fine now.  Thank you for your help!

  • TheNeos Profile Picture
    TheNeos 1,335 on at
    RE: Is there any query that can do this?

    Do the items have multiple suppliers?

    I have not come across this so I can not say.  

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Is there any query that can do this?

    Hello,

    After following your suggested query, I initially thought all items from the selected supplier were deleted.  However, when I go to Database->Supplier->Items Supplied, it still shows that there are 2635 items (the total number of items of the supplier that I tried to delete) and the descriptions and item look up codes shown as "<unknown>".  What other further steps do I have to take to completely delete the items 100% from the database? I think there are still some remnants left in the system.

    Thank you.

    Al

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Is there any query that can do this?

    Thank you very much!

  • Verified answer
    TheNeos Profile Picture
    TheNeos 1,335 on at
    RE: Is there any query that can do this?

    This post edited to reflect more realistic approach to this problem as the original post did not take into account the consequences of deleting items from RMS Administrator. Added text is in blue.

    Before starting Back Up. Take serious note that deleting items from Administrator will result item LookUp Codes being deleted but not the whole item. Remember that items are attached to Assemblies, Matrixes, Transactions and others which will still hold the Item Id and may create problems.

    If the above don't matter and still need to delete items, they can be deleted through SQL Studio, not Administrator.

    Finally deletion from Manager will do the deletion properly but of course one by one.

    Easiest and safest way to take them out of sight is to make them inactive using Manager Inventory wizard work sheet.

    .

    Look in suppliers and note the Supplier ID that is of interest.

    Query to look for Supplier Id:

    select ID, SupplierName from Supplier

    .

    In the two queries bellow Substitute the 1 with your ID preserving the single quotes.

    .

    Query to look that your selection is what you want:

    select ItemLookupCode, description, supplierID from item where supplierId='1'

    .

    Query to delete:

    Delete item where supplierId='1'

    .

    Please note that deleting all items of a supplier may delete the supplier as well so check and make sure this supplier exist before re-importing

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Is there any query that can do this?

    Thank you for your answer. Yes, as you said, the excel formatting probably messed up the upc codes.

    Do you know which query to use to delete all items from a certain supplier at HQ and Store? I have to delete the items before importing again.

    Thank you.

  • Suggested answer
    TheNeos Profile Picture
    TheNeos 1,335 on at
    RE: Is there any query that can do this?

    If you still have the original .csv file that holds the items in question you can fix this using Excel.

    1 - Change the .csv extension of the file to .txt (this will give you the opportunity to preserve zeros),

    2 - Open it in excel and select all columns or at least the ILUC, to be as text, this will preserve leading zeros, edit if needed,

    3 - Save this file as .csv UTF-8 (this can also be done in Notepad),  

    4 - Open it with Notepad and check that zeros are there, if there, close and import as you did.

    Good luck

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,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans