Skip to Content
0

Query for Price List With Item Properties

Jun 21, 2017 at 07:36 AM

109

avatar image

Hi Guys,

Is It possible to create a query that can generate a price list with it's ITEM Properties?

Can you help me with this

Thanks,

Soc

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Balaji Selvaraj Jun 21, 2017 at 09:01 AM
1

Hi Socrates,

You can try standard report , " Price report " under Inventory Report. Where you can choose Price list based on your Item properties.

Else

Try this you need to add additional properties with Union all for remaining Item properties.It will result based on your number of price list

select Distinct T0.ItemCode,T0.ItemName, T1.ItmsGrpNam,T2.[PriceList], T3.[ListName], T2.[Price], T2.[Currency] from OITM T0 CROSS JOIN OITG T1 Left join Itm1 T2 on T0.ItemCode = T2.ItemCode Left JOIN OPLN T3 ON T2.PriceList = T3.ListNum where T0.QryGroup1 = 'Y' and T1.ItmsTypCod = 1

Union all

select Distinct T0.ItemCode,T0.ItemName, T1.ItmsGrpNam,T2.[PriceList], T3.[ListName], T2.[Price], T2.[Currency] from OITM T0 CROSS JOIN OITG T1 Left join Itm1 T2 on T0.ItemCode = T2.ItemCode Left JOIN OPLN T3 ON T2.PriceList = T3.ListNum where T0.QryGroup2 = 'Y' and T1.ItmsTypCod = 2

Union all

select Distinct T0.ItemCode,T0.ItemName, T1.ItmsGrpNam,T2.[PriceList], T3.[ListName], T2.[Price], T2.[Currency] from OITM T0 CROSS JOIN OITG T1 Left join Itm1 T2 on T0.ItemCode = T2.ItemCode Left JOIN OPLN T3 ON T2.PriceList = T3.ListNum where T0.QryGroup3 = 'Y' and T1.ItmsTypCod = 3

Union all

select Distinct T0.ItemCode,T0.ItemName, T1.ItmsGrpNam,T2.[PriceList], T3.[ListName], T2.[Price], T2.[Currency] from OITM T0 CROSS JOIN OITG T1 Left join Itm1 T2 on T0.ItemCode = T2.ItemCode Left JOIN OPLN T3 ON T2.PriceList = T3.ListNum where T0.QryGroup4 = 'Y' and T1.ItmsTypCod = 4

Regards:

Balaji.S

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Thank This really works

0

but how can I sort it with specific price list only?

0

Use Order By T2.[PriceList] in the query.

If you double click on price list column it will get sorted

0
Johan Hakkesteegt Jun 21, 2017 at 08:12 AM
0

Hi Soc,

It is complicated, but possible. However, a price list contains one price per item, but one item can contain between zero and 64 properties.

That means that the result of such a query would be very confusing to read, as some expected items may not show because they have no properties set, and others may be on the list multiple times, but varying per item.

Also, the number of rows returned by such a query would potentially be between zero and 64 x [number of items with a price]. So if you have, let us say, 150 items you could end up with a list with 9600 rows. This assumes that you run the query for a single price list only.

So I would recommend taking another look at the question / problem, and/or rethinking the reason you used item properties, and/or rethinking the query / report.

Regards,

Johan

Share
10 |10000 characters needed characters left characters exceeded
Socrates Ariola Jun 24, 2017 at 02:00 AM
0

Thanks for this

Share
10 |10000 characters needed characters left characters exceeded