xTuple.com xTupleU Blog & News Customer Support

itemCluster

Using Windows 5.1 version

I am having a difficult time displaying any items for the itemCluster when the List menu item is selected.

I even tried removing my extraClause in hope that all items are listed, but none show up.
I am concerned that the item grid does not even show the header row, with the column names.

What could possibly cause the Item List Screen to refuse to list items?

I have the same problem in Purchase Orders, Sales Orders and Invoices.

I took a look at itemCluster.cpp.
Are using Aliases?
the first part uses this if that is the case:
try this and see what you get:
SELECT * FROM (
SELECT item_id, item_number, item_descrip1, item_descrip2,
item_upccode, item_type, item_fractional, item_config, item_inv_uom_id,
item_sold, item_active, NULL AS itemalias_crmacct_id
FROM item
UNION
SELECT item_id, itemalias_number,
CASE WHEN LENGTH(itemalias_descrip1) > 1 THEN itemalias_descrip1 ELSE item_descrip1 END,
CASE WHEN LENGTH(itemalias_descrip2) > 1 THEN itemalias_descrip1 ELSE item_descrip2 END,
item_upccode, item_type, item_fractional, item_config, item_inv_uom_id,
item_sold, item_active, itemalias_crmacct_id
FROM item
JOIN itemalias ON item_id = itemalias_item_id
) AS item
JOIN uom ON (uom_id=item_inv_uom_id) ;

Otherwise it is running this:
Select * FROM item
JOIN uom ON uom_id = item_inv_uom_id
LEFT OUTER JOIN itemalias ON (itemalias_item_id=item_id)
LEFT OUTER JOIN crmacct ON (crmacct_id=itemalias_crmacct_id) ;

If you are having problems on several, check the meta and see if you are using above grade 0.

Thanks Tom

When I run either query I get over 10,000 rows, as expected

we try to filter by classcodes to reduce the returned rows, but no rows are returned in the item cluster list

where item_id in (select item_id from public.item where item_classcode_id in (1,18))
this returns 63 rows are expected in the query

The other odd thing about the Item List is the search box

If I type in 97 ( we have 73 items starting with 97)

and hit Ok,

no list is provided,

the first item in that list of item is returned to the item cluster

The List should , list them out , so the use can select one

Check the database server log for errors, first in the desktop client (System > View Database Log) and then on the Postgres server itself. The item cluster and associated search and list windows don’t always report their SQL errors.

Where we look next depends on what you find.

Gil

Nothing remarkable in xTuple errors Gil. And I am talking to our system admin about the database errors. I dont think we have logging enabled.

The strange issue is that we have several database dev, test and prod.

Dev exhibits this behavior because I have added my custom screens or PR and PO
Test does not yet have this code and if you create a PO on test, the item clusters display the items

But not on dev.

So my code is somehow altering the behavior of the item cluster.

But what could it be?

I removed any extra clauses to no avail.

Gil,

I dont see any reference in our PG logs that mention the item table.

I see the the pohead table query but no item table

Ok… if it’s not the query then it might be the script. Can you post the script in full, including the script name?

Here is the entire script Gil - name trade_POItem see function set_inital_data

debugger;

var mode;
var po_id;
var po_item_id = -1;
var linenum = -1;
var vend_id = -1;
var itemsrc_id = -1;
var itemsite_id = -1;

var po_save = mywindow.findChild("_save");
var po_close = mywindow.findChild("_close");

po_save.clicked.connect(save_data);
po_close.clicked.connect(close_window);

var item_cluster = mywindow.findChild("_item");	
item_cluster.readOnly = false;
item_cluster.setType(3);

mywindow.modal = false;

function set(param)
{
	if ("mode" in param)
	{
		mode = param.mode;
	}

	if ("vend_id" in param)
	{
		vend_id = param.vend_id;
	}

	if ("pohead_id" in param)
	{		
		po_id = param.pohead_id;

		if ( mode == "new")
		{
			set_inital_data();
		}
		
	}

	if ("poitem_id" in param)
	{		
		po_item_id = param.poitem_id;

		if ( mode != "new")
		{
			loadData();
		}
		
	}
	
}

function set_inital_data()
{
	// for a new po item we need to vendor, classcode and project

	var item_cluster = mywindow.findChild("_item");	

	var qry = " select distinct pohead_ext_prj_id, pohead_vend_id ,current_date  " +
		" from ww_billing.pohead_ext " +
		" join public.pohead on pohead_id = pohead_ext_po_id " +
		" join public.vendinfo on vend_id = pohead_vend_id " +		
		" where pohead_id = " + po_id;

	var q = toolbox.executeQuery(qry);

	if (q.size() > 0)
	{
		q.first();

		var project = mywindow.findChild("_project");

		project.setId(q.value(0));		

		var dueDate = mywindow.findChild("_dueDate")

		dueDate.date = q.value(2);

		// ok now the classcodes

		var qry1 = " select distinct classcode_id  " +
		" from ww_billing.pohead_ext " +
		" join public.pohead on pohead_id = pohead_ext_po_id " +
		" join public.vendinfo on vend_id = pohead_vend_id " +
		" join public.itemsrc on itemsrc_vend_id = pohead_vend_id  " +		
		" join public.item on item_id = itemsrc_item_id  " + 
		" join public.classcode on item_classcode_id = classcode_id and classcode_code != 'APPT' " + 
		" where pohead_id = " + po_id;

		var q1 = toolbox.executeQuery(qry1);

		var classcodes = "";		

		while (q1.next())
		{
			classcodes = classcodes + q1.value(0) + ",";
		}

		classcodes = classcodes.substring(0,classcodes.length - 1);

		var extraqry = " item_id in (select item_id from public.item where item_classcode_id in (" + classcodes + "))";							

		item_cluster.addExtraClause(extraqry);
						
	}
	else
	{
		QMessageBox.critical(mywidget, qsTr("PO Item Load Data "), qsTr("PO # " + po_id + " could not load initial data, contact support"));
	}

	var test = "";

	
}

function loadData()
{
		
	var line_number = mywindow.findChild("_lineNumber");
	var po_number = mywindow.findChild("_poNumber");
	var item = mywindow.findChild("_item");
	var qty_ord = mywindow.findChild("_ordered");
	var unit_price = mywindow.findChild("_unitPrice"); 
	var due_date = mywindow.findChild("_dueDate");
	var project = mywindow.findChild("_project");
	var notes = mywindow.findChild("_notes"); 
	var dos = mywindow.findChild("_dos"); 

	var qry = "select pohead_number,  poitem_linenumber,itemsite_item_id, poitem_qty_ordered,"  +
		" poitem_unitprice,  poitem_duedate,poitem_prj_id,poitem_comments,poitem_ext_dos " +		
		" from public.poitem " +
		" join ww_billing.poitem_ext on poitem_ext_id = poitem_id " +
		" join public.pohead on pohead_id = poitem_pohead_id " +
		" join public.itemsite on itemsite_id = poitem_itemsite_id " +
		" where poitem_ext_active and poitem_id = " + po_item_id;

	var q = toolbox.executeQuery(qry);

	if (q.size() > 0)
	{
		q.first();
		po_number.text = q.value(0);
		line_number.text = q.value(1);
		item.setId(q.value(2));
		qty_ord.text = q.value(3);
		unit_price.baseValue = q.value(4);
		due_date.date = q.value(5);
		project.setId(q.value(6));
		notes.text = q.value(7);
		dos.date = q.value(8);
	}
}

function filldata()
{
	
	var po_item_id = mywindow.id();
	var line_number = mywindow.findChild("_lineNumber");
	var po_number = mywindow.findChild("_poNumber");
	var item = mywindow.findChild("_item");
	var qty_ord = mywindow.findChild("_ordered");
	var unit_price = mywindow.findChild("_unitPrice"); 
	var due_date = mywindow.findChild("_dueDate");
	var project = mywindow.findChild("_project");
	var notes = mywindow.findChild("_notes"); 
	var dos = mywindow.findChild("_dos"); 

	var qry = "select pohead_number,  poitem_linenumber,"  +
		"  itemsite_item_id,poitem_qty_ordered,poitem_unitprice,  poitem_duedate,poitem_prj_id,poitem_comments,poitem_ext_dos " +		
		" from public.poitem " +
		" join public.pohead on pohead_id = poitem_pohead_id " +
		" join ww_billing.poitem_ext on poitem_ext_id = poitem_id " +
		" join public.itemsite on itemsite_id = poitem_itemsite_id " +
		" where poitem_id = " + po_item_id;

	var q = toolbox.executeQuery(qry);

	if (q.size() > 0)
	{
		q.first();
		po_number.text = q.value(0);
		line_number.text = q.value(1);
		item.setId(q.value(2));
		qty_ord.text = q.value(3);
		unit_price.baseValue = q.value(4);
		due_date.date = q.value(5);
		project.setId(q.value(6));
		notes.text = q.value(7);
		dos.date = q.value(8);
	}
}


function save_data()
{
	var good = true;

	var item = mywindow.findChild("_item");

	if ( item.id() == -1)
		good = false;

	var qty_ord = mywindow.findChild("_ordered");

	if ( qty_ord.text == "")
		good = false;
		
	var unit_price = mywindow.findChild("_unitPrice");

	if ( unit_price.baseValue == 0)
		good = false;

	var dos = mywindow.findChild("_dos"); 

	if ( dos.date == "Invalid Date")
		good = false;

	if ( good)
	{
		if ( po_item_id == -1)
		{
		insert_data();
		}
		else
		{
		update_data();
		}
	}
	else
	{
		QMessageBox.critical(mywidget, qsTr("Missing data"), qsTr(" Item, Qty,Unit Price and DOS required."));
	}
}

function getLineNumber()
{
	var qry = "select count(*) + 1 from public.poitem where poitem_pohead_id =  " + po_id;

	var q = toolbox.executeQuery(qry);

	if (q.size() > 0)
	{
		q.first();
		linenum = q.value(0);
	}
}

function getItemData()
{
	var po_item_id = mywindow.findChild("_item").id();

	var qry = "select itemsrc_id from public.itemsrc where itemsrc_item_id = " + po_item_id +
		" and itemsrc_vend_id = " + vend_id;

	var q = toolbox.executeQuery(qry);

	if (q.size() > 0)
	{
		q.first();
		itemsrc_id = q.value(0);
	}

	var qry1 = "select itemsite_id from public.itemsite where itemsite_item_id = " + po_item_id;

	var q1 = toolbox.executeQuery(qry1);

	if (q1.size() > 0)
	{
		q1.first();
		itemsite_id = q1.value(0);
	}
}

function insert_data()
{
	getLineNumber();
	getItemData();

	var po_item_id = -1;

	var poitem_linenum = linenum;
	var po_item = mywindow.findChild("_item").id();
	var poitem_itemsite_id = itemsite_id;
	var poitem_expcat_id = -1;
	var poitem_itemsrc_id = itemsrc_id;
	var poitem_vend_item_number = "";
	var poitem_vend_item_descrip = "";
	var poitem_vend_uom = "";
	var poitem_invvenduomratio= -1;
	var poitem_qty_ordered = mywindow.findChild("_ordered").text;
	var unitPrice = mywindow.findChild("_unitPrice").baseValue;
	var dueDate = mywindow.findChild("_dueDate");	
	var poitem_comments = "";
	var poitem_prj_id = mywindow.findChild("_project").id();
	var note = mywindow.findChild("_notes").text;

	var year = dueDate.date.getYear() + 1900;
	var day = dueDate.date.getDate() ;
	var month = dueDate.date.getMonth() + 1 ;


	var qry = " INSERT INTO public.poitem " +
		"(  poitem_pohead_id, poitem_status, poitem_linenumber,"  +
		"  poitem_itemsite_id, poitem_expcat_id," +
		"  poitem_itemsrc_id, " +
		"  poitem_qty_ordered," + 
		"  poitem_unitprice,  poitem_duedate,poitem_prj_id,poitem_comments) " +		
		" values ( " + po_id + ",'O'," + poitem_linenum + "," +
		itemsite_id + "," + poitem_expcat_id + "," + itemsrc_id + "," +
		poitem_qty_ordered + "," + unitPrice + ",'" +  year + "/" + month + "/" + day + "'::date," +		
		 poitem_prj_id + ",'" + note + "'" +
		" ) returning poitem_id " 

	var q = toolbox.executeQuery(qry);

	if (q.size() > 0)
	{
		q.first();

		po_item_id = q.value(0);

		// now the poitemExt table

		var dos = mywindow.findChild("_dos");		

		var qry1;

		if ( dos.date == "Invalid Date")
		{
			qry1 = "insert into ww_billing.poitem_ext(poitem_ext_id )"
				+ " values ( " + po_item_id + ") returning poitem_ext_id "
		}
		else
		{
			var dos_year = dos.date.getYear() + 1900;
			var dos_day = dos.date.getDate() ;
			var dos_month = dos.date.getMonth() + 1 ;

			qry1 = "insert into ww_billing.poitem_ext(poitem_ext_id,poitem_ext_dos)"
				+ " values( " + po_item_id + ",'" +  dos_year + "/" + dos_month + "/" + dos_day + "'::date )"
				+ " returning poitem_ext_id "; 
		}

		var q1 = toolbox.executeQuery(qry1);

		if (q1.size() > 0)
		{
			q1.first();

			var test_id = q1.value(0);

			if ( test_id == po_item_id)
			{
				QMessageBox.information(mywidget, qsTr("Insert Success"), qsTr("poitem_id # " + po_item_id + " inserted."));
			}
			else
			{
				QMessageBox.critical(mywidget, qsTr("PO Item Ext Data "), qsTr("PO # " + po_id + " could not save ext data, contact support"));
			}
		}
		else
		{
			QMessageBox.critical(mywidget, qsTr("PO Item Ext Data "), qsTr("PO # " + po_id + " could not save ext data, contact support"));
		}
	}
	else
	{
		QMessageBox.critical(mywidget, qsTr("PO Item Insert "), qsTr("PO # " + po_id + " could not add po item, contact support"));
	}

	var test = "";

}

function update_data()
{
	getLineNumber();
	getItemData();	
		
	var po_item = mywindow.findChild("_item").id();
	var poitem_itemsite_id ;	
	var poitem_itemsrc_id ;

	var qry = " select itemsrc_id,itemsite_id " +
		" from public.itemsrc " +	
		" join public.itemsite on itemsite.itemsite_item_id = itemsrc_item_id " +	
		" where itemsrc_vend_id = " + vend_id +
		" and itemsrc_item_id = " + po_item;

	var q = toolbox.executeQuery(qry);
	
	if (q.size() > 0)
	{
		q.first();
		
		poitem_itemsrc_id = q.value(0);
		poitem_itemsite_id = q.value(1);
			
		var poitem_qty_ordered = mywindow.findChild("_ordered").text;
		var unitPrice = mywindow.findChild("_unitPrice").baseValue;
		var dueDate = mywindow.findChild("_dueDate");	
		var poitem_comments = mywindow.findChild("_notes").text;
		var poitem_prj_id = mywindow.findChild("_project").id();

		if (poitem_comments == "undefined")		
			poitem_comments = "";

		var year = dueDate.date.getYear() + 1900;
		var day = dueDate.date.getDate() ;
		var month = dueDate.date.getMonth() + 1 ;


		var qry1 = " update public.poitem " +		
		"  set poitem_itemsite_id = " + poitem_itemsite_id +
		", poitem_duedate = " + "'" +  year + "/" + month + "/" + day + "'::date" +
		", poitem_unitprice = " + unitPrice +
		", poitem_qty_ordered = " + poitem_qty_ordered + 
		", poitem_comments = ' " + poitem_comments + "'" +
		", poitem_itemsrc_id = " + itemsrc_id + 
		", poitem_prj_id = " + poitem_prj_id + 
		" where poitem_id = " + po_item_id +
		" returning poitem_id";

		var q1 = toolbox.executeQuery(qry1);

		if (q1.size() > 0)
		{
			q1.first();

			var test_id = q1.value(0);

			if ( test_id == po_item_id)
			{
				// now update the dos
				var dos = mywindow.findChild("_dos");

				if ( dos.date != "Invalid Date")
				{				

					var dos_year = dos.date.getYear() + 1900;
					var dos_day = dos.date.getDate() ;
					var dos_month = dos.date.getMonth() + 1 ;
	
					var qry2 = "update ww_billing.poitem_ext " +
						" set poitem_ext_dos = '" + dos_year + "/" + dos_month + "/" + dos_day + "'::date" +
						" where poitem_ext_id = " + po_item_id +
						"  returning poitem_ext_id ";

					var q2 = toolbox.executeQuery(qry2);

					if (q2.size() > 0)
					{
						q2.first();

						var ext_id = q2.value(0);

						if ( ext_id == po_item_id)
						{
							QMessageBox.information(mywidget, qsTr("Insert Success"), qsTr("poitem_id # " + po_item_id + " updated."));
						}										
					}
					else
					{
						QMessageBox.critical(mywidget, qsTr("Update PO Item Ext Failed"), qsTr("poitem_ext_id # " + po_item_id + " failed, contact support"));
					}
				}
				else
				{
					var qry2 = "update ww_billing.poitem_ext " +
						" set poitem_ext_dos = null"  +
						" where poitem_ext_id = " + po_item_id +
						"  returning poitem_ext_id ";

					var q2 = toolbox.executeQuery(qry2);

					if (q2.size() > 0)
					{
						q2.first();

						var ext_id = q2.value(0);

						if ( ext_id == po_item_id)
						{
							QMessageBox.information(mywidget, qsTr("Update Success"), qsTr("poitem_id # " + po_item_id + " updated."));
						}										
					}
					else
					{
						QMessageBox.critical(mywidget, qsTr("Update PO Item Ext Failed"), qsTr("poitem_ext_id # " + po_item_id + " failed, contact support"));
					}
				}				
			}
			else
			{
				QMessageBox.critical(mywidget, qsTr("Update PO Item Failed"), qsTr("poitem_id # " + po_item_id + " failed, contact support"));
			}
		}
		else
		{
			QMessageBox.critical(mywidget, qsTr("Update PO Item Failed"), qsTr("poitem_id # " + po_item_id + " failed, contact support"));
		}

		var test = "";
	}
	else
	{
		QMessageBox.critical(mywidget, qsTr("Itemsrc Failed"), qsTr("poitem_id # " + po_item_id + " failed, contact support"));
	}
	
		
	

}



function close_window()
{
	mydialog.done(1); 	
}

Gil, for the screen just import the 5.1 purchaseOrderItem.ui from source

So I tried loading this script and writing the supporting code for the Purchase Order window with a 5.1.0 client and database. I don’t see the problems with the item cluster that you described — missing data, missing headers — and there’s nothing in the script that explains such behavior.

As long as the class codes query returns at least one row, this should work. If that query returns no rows, there should be a syntax error reported in the database log from the item cluster near the in

...item_classcode_id in ())...
                        ^

Thank you Gil,

I have even removed all extra clauses in order to see the entire list of items. Still nothing.

But this may be a clue, that you could help.

My trade_poitem screen is set to NON MODAL for now, so that I can run debugging.
And the parent screen , the tradePO is NON MODAL, as it should be.

But, the trade_poitem screen still acts as a MODAL Dialog and debugging screen freezes.

Could this behavior possibly affect the behavior of the item cluster list screen, which is modal.

There’s more going on here than we can work out via a forum discussion. It’s highly unlikely to be a modal vs. non-modal issue, although the script debugger has been acting strangely in recent Qt releases.

To rule out the obvious, turn off script debugging. If the problem still occurs, I suggest contacting support directly.

Gil