Hi Naveen,
You can use the following code piece. listWhere is the implicit variable to be used with retrieve list event. The where clause below is inspired from Vendor Items button.
from psdi.mbo import SqlFormat
ownerMbo = mbo.getOwner()
if ownerMbo is not None and ownerMbo.isBasedOn("PR") and not ownerMbo.isNull("VENDOR"):
whereClause = "(itemnum, itemsetid) in (select itemnum, itemsetid from invvendor where vendor = :vendor and orgid = :orgid and (siteid = :siteid or siteid is null) and itemnum not in (select itemnum from item where status not in (select value from synonymdomain where domainid ='ITEMSTATUS' and maxvalue in ('ACTIVE','PLANNING','PENDOBS'))))"
sqlf = SqlFormat(ownerMbo, whereClause)
listWhere = sqlf.format()
------------------------------
YALCIN KUMBASAR
------------------------------
Original Message:
Sent: Mon April 17, 2023 10:46 AM
From: Naveen Yadav
Subject: Filter Retrieval list of Items at PR line
Hello ,
I need to filter the ITEM list in the PR line level based on the value selected in the Vendor field at the PR level . I am using a Attribute launch point with the "Retrieve list" list event and below is the code that I am using .
owner = mbo.getOwner()
if owner.getString("VENDOR"):
itemSet = mbo.getMboSet("ITEM")
itemSet.setWhere("VENDOR = '" + owner.getString("VENDOR")+ "'")
itemSet.reset()
I understand that VENDOR is not a field in the ITEM object and exists in the INVVENDOR object but I am not sure how to write the sql here so that the itemset gets filtered based on the Vendor field which exists in the INVVENDOR . Any help is appreciated.
------------------------------
Naveen Yadav
------------------------------