News and Announcements icon

Dynamics 365 CommunityForums Microsoft Dynamics RMS (Archived) RMS SQL Query Help

Microsoft Dynamics RMS (Archived)

RMS SQL Query Help

(0) Share

Report

Report

Posted on by Community Member Microsoft Employee

I need to make some items inactive and would like to do it via an sql query. I have items that relates to each other with the Itemlookupcode for example of 20-1234 and 20-1234LG (which is a fixed length of item 20-1234). So I want to make sure both items equal 0 before making them, both inactive. Since someone my buy the item 20-1234LG and ask us the order more - so we would need the item 20-1234 as active.

Logic ( I hope this make sense:

Loop

If ItemLookupcode where Quantity = 0 AND

itemlookupcode+LG as ILCLG where Quantity = 0

Set inactive both Itemlookupcode and ILCLG

Any help would be appreciated.

Dan

*This post is locked for comments

All responses Img

All responses (2)

Answers Img

Answers (0)

Suggested answer

TimB 1,175 on at

Like (0)

Report

Re: RMS SQL Query Help

Matt's queries will work just fine. To see which records will be affected you can first run this query.

select i1.itemlookupcode,i1.description,i2.itemlookupcode,i2.description from item i1 join item i2

on i1.itemlookupcode=i2.itemlookupcode+'lg'

where i1.quantity=0 and i2.quantity=0
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

Re: RMS SQL Query Help

Try this:

update i1 set i1.inactive=1 from item i1 join item i2
on i1.itemlookupcode=i2.itemlookupcode+'lg'
where i1.quantity=0 and i2.quantity=0

update i2 set i2.inactive=1 from item i1 join item i2
on i1.itemlookupcode=i2.itemlookupcode+'lg'
where i1.quantity=0 and i2.quantity=0

Helpful resources

Quick Links

Leaderboard

#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

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans