Inventory Item User Categories

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (4)

Answers (0)

Praveen Kumar RR 1,552 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Justin Thorp 2,265 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No