BP cat nr and price in query

David Deleu

Hi Philippe,


I try to create a layout on the BP screen which should show all the items and their price from the picelist linked to that BP and also the BP Catalog number and oitm.itemname for these items

My queries are:

Select * from OITM T0 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode WHERE T1.PriceList = [%ListNum]

and

SELECT * FROM OSCN T0 WHERE T0.CardCode = '[%CardCode]'

I have created the parameters ListNum and CardCode and added the 2 queries to layoutdefinition. But when i try to preview nothing!

What am i doing wrong?

kind regards,
David

Philipp Knecht

Hi David

I can not argue on your queries because there exists multiple ways in B1 to determine the Price.
But to get the Prices and Items behind a BusinessPartner together with BP Catalog Nr i would use one query like:

[quote]SELECT T0.CardName, T1.ItemCode, T2.Substitute, T1.Price FROM OCRD T0
INNER JOIN ITM1 T1 ON T0.ListNum = T1.PriceList
LEFT OUTER JOIN OSCN T2 ON T0.CardCode=T2.CardCode AND T1.ItemCode = T2.ItemCode
WHERE T0.CardCode = '[%CardCode] ORDER BY T1.ItemCode '

hth