Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Inventory Item User Ca...
Microsoft Dynamics GP (Archived)

Inventory Item User Categories

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

I've reviewed many questions over the years related to adding the IV40600.UserCatLongDescr field to various reports and/or documents but have not found a resolution. 

I'm trying to accomplish the following:

Create a SmartList, either using Joins or a view, that will include the UserCatLongDescr from table IV40600 based on the values; USCATVLS_1, USCATVLS_2, USCATVLS_3, USCATVLS_4 in table IV00101 for each inventory item. 

First condition:

IV00101.USCATVLS_1 joined to IV40600.USCATNUM = '1'

IV00101.USCATVLS_2 joined to IV40600.USCATNUM = '2'

IV00101.USCATVLS_3 joined to IV40600.USCATNUM = '3'

IV00101.USCATVLS_4 joined to IV40600.USCATNUM = '4'

IV00101.USCATVLS_5 joined to IV40600.USCATNUM = '5'

IV00101.USCATVLS_6 joined to IV40600.USCATNUM = '6'

Second Condition:

If value exists in IV00101.USCATVLS_1 join to IV40600.USCATVAL and display IV40600.UserCatLongDescr

If value exists in IV00101.USCATVLS_2 join to IV40600.USCATVAL and display IV40600.UserCatLongDescr

If value exists in IV00101.USCATVLS_3 join to IV40600.USCATVAL and display IV40600.UserCatLongDescr

If value exists in IV00101.USCATVLS_4 join to IV40600.USCATVAL and display IV40600.UserCatLongDescr

If value exists in IV00101.USCATVLS_5 join to IV40600.USCATVAL and display IV40600.UserCatLongDescr

If value exists in IV00101.USCATVLS_6 join to IV40600.USCATVAL and display IV40600.UserCatLongDescr

Any help appreciated!

Debi

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Inventory Item User Categories

    Thank you Justin.  I will start with this and let you know.

  • Suggested answer
    Justin Thorp Profile Picture
    Justin Thorp 2,265 on at
    RE: Inventory Item User Categories

    Hi,

    There are several ways you could accomplish this.  Below is the start of a select statement that should return the desired results.  Please use it as a basis for creating your view, etc...

    SELECT A.ITEMNMBR,

    A.USCATVLS_1,

    (SELECT UserCatLongDescr FROM IV40600 C WHERE A.USCATVLS_1 = C.USCATVAL AND C.USCATNUM = 1),

    A.USCATVLS_2,

    (SELECT UserCatLongDescr FROM IV40600 C WHERE A.USCATVLS_2 = C.USCATVAL AND C.USCATNUM = 2),

    * FROM IV00101 A

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Inventory Item User Categories

    table IV00101 (Inventory Item Master)

    Column 1: Itemnmbr = PHONE-ATT-5354

    Column 2: Itemdesc = Cordless-Attractive 5354

    Column 3: USCATVLS_1 = ATT

    Column 4: USCATVLS_2 = Standard

    Table IV40600 (Item Category Setup)

    Column 1, Row 1: USCATVAL = Attractive

    Column 2, Row 1: USCATNUM = 1

    Column 3, Row 1: UserCatLongDescr = Attractive Telephone 5354

    Column 1, Row 2: USCATVAL = Contempo

    Column 2, Row 2: USCATNUM = 2

    Column 3, Row 2: UserCatLongDescr = Contempo Model 12896

    In this scenario only 2 or the 6 available fields are used but all 6 could potentially be used.

  • Praveen Kumar RR Profile Picture
    Praveen Kumar RR 1,550 on at
    RE: Inventory Item User Categories

    Hi,

    Can you please provide me sample record out of below query,

    SELECT * FROM IV00101

    SELECT * FROM IV40600

    And what value exists for USCATVLS_1, USCATVLS_2 and other 4 fields.

    Thanks,

    Praveen

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