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 DavidI 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
0
Please sign in to leave a comment.
Comments
0 comments