using the SQL item type as a view
- 6 years ago
Hi Paul
You can use below method to get the required view. I used item_number of NCMR as ID is unique for each item type
Innovator inn = this.getInnovator();
//Verify the Item type name in system
var ncmrItem = inn.newItem("NCMR MANAGEMENT", "get");
ncmrItem.setAttribute("select", "id,item_number");
ncmrItem = ncmrItem.apply();
if (ncmrItem.isError())
{
return inn.newError("No NCMR Item Found");
}
else
{
var ncmrIdList = new List < string > ();
for (int i = 0; i < ncmrItem.getItemCount(); i++)
{
var ncmrOneItem = ncmrItem.getItemByIndex(i);
//I'm using item_number here as ID is unique for each item type and we might not have same ID used for Part and NCMR. If you want to use ID, then change item_number to id
ncmrIdList.Add("'" + ncmrOneItem.getProperty("item_number", string.Empty) + "'");
}
var partView = inn.newItem("Part", "get");
partView.setAttribute("select", "id,item_number");
partView.setProperty("is_current", "1");
partView.setProperty("state", "Released");
partView.setProperty("item_number", string.Join(",", ncmrIdList)); // Change it to ID if you want to use ID instead item_number
partView.setPropertyAttribute("item_number", "condition", "not in"); // Change it to ID if you want to use ID instead item_number
partView = partView.apply();
if (partView.isError())
{
return inn.newError("No Part item found using this criteria");
}
else
{
return partView;
}
}We can SQL also but it is good practice to use AML as SQL directly interacts with tables and other server events will not be triggered.
Views in database is created for Poly Item and are system created. If you want to use SQL for any complex get query, you can use stored procedure and call the SQL Process in your method