Adding columns to existing screens using scripting

changopiano · February 18, 2017, 5:17am

I would like to add a column of data to an existing screen using scripting. I can get it to add a column and post data that was on the initial query for the screen but I can’t add new data from a new query.

Here is the script I have been using:

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

function searchClicked()
{
var params = new Object;
params.item_id = mywindow.findChild("_item").id();

var myq = toolbox.executeQuery(“SELECT location_name”
+“FROM item”,
+“LEFT OUTER JOIN itemsite ON item.item_id = itemsite.itemsite_item_id”,
+“LEFT OUTER JOIN location ON itemsite.itemsite_location_id = location.location_id”);

mywindow.sFillList();
}

_item.addColumn(qsTr(“item_loc”), -1, Qt.AlignRight,true, “location_name”);

If I tell the column to contain “item_id” which was on the initial query it outputs just fine. How can I get it to display the new query information?

bcwilson · February 18, 2017, 5:45am

Pete and others, the attachments on this page seem to work fine now. Just right click on the attachment and save it to your local machine (it doesn’t work if you left-click…). I’ve tried it with the text file and the zip on this page and it works.

BC

marena · March 13, 2017, 9:12pm

Which screen are you working on?

changopiano · March 13, 2017, 9:12pm

I am currently working on the searchForItem screen, although I would like to be able to use it on other screens as well.

ma23693 · March 13, 2017, 9:13pm

It is fairly standard practice in sFillList routines to do a hard coded query to provide the data. (See for instance itemSites.cpp)
Your approach is fine for picking and choosing which columns to display but it won’t display any columns not in the hard coded query.

changopiano · March 13, 2017, 9:13pm

Does that mean that to be able to modify the data that is shown in the XTree widget you have to modify the core files? It seems like this would be an excellent area for scripting which would allow users to view data that is important to them, but not necessarily to the xTuple community.

ma23693 · March 13, 2017, 9:13pm

I posed a question a few weeks ago: http://www.xtuple.org/node/2338
and pointed to a issue that I thought was related: http://www.xtuple.org/issuetracker/view.php?id=7623

I do believe that scripting is undergoing a lot of changes and the new capabilities might address some of these questions.

marena · March 13, 2017, 9:13pm

I agree. It will be awesome if we can add data to an xtreewidget without touching the core source.

petebisson · March 13, 2017, 9:15pm

It is possible in many cases to add extra data to a core XTreeWidget.

The problem with trying to do it the way changopiano tried in the original post is that when you call mywindow.sFillList() your new query is discarded and the original core query is executed.

The technique to use is as follows:

Create a new query in your script that contains all the original columns and your new requirements.
Use toolbox.coreDisconnect() to detach every existing signal from sFillList()
Reattach all the various signals to your new function.

This can get quite tricky depending on the screen you are working with.

Pay particular attention to global signals on ‘mainwindow’ as well as local ones on ‘mywindow’.

To illustrate this better, I have attached a working version of what changopiano was trying to do.

I have assumed you are using 3.3.x so this script is written for that version - if you want to do this on 3.2.2 you won’t be able to use the qsTr() translation functions, and will just have to use non-translatable strings instead.

Enjoy

Pete

changopiano · March 13, 2017, 9:15pm

Thanks Pete. I was headed down that road (coreDisconnect) but hadn’t gotten it to work yet.

When I try to grab the attachment it gives me a page not found error. Could you upload it again or something so that it works.

petebisson · March 13, 2017, 9:15pm

I’m not sure why the file attachment didn’t work last time, so I’ll try it again

EDIT:

And failed again.

Let’s see if we can do it inline:

/* 
 * Script: searchForItem
 * Notes: Add extra column(s)
 * Order: 0
 * Version: 3.3.x
 *
 * Copyright(c) 2009 petebisson - xtuple[at]bisson.co.uk
 *
 * This file is a script adding extra functionality to
 * xTuple ERP: PostBooks Edition, a free and open source 
 * Enterprise Resource Planning software suite,
 * Copyright (c) 1999-2009 by OpenMFG LLC, d/b/a xTuple.
 *
 * It is licensed to you under the Common Public Attribution License
 * version 1.0, the full text of which (including xTuple-specific Exhibits)
 * is available at www.xtuple.com/CPAL.  By using this software, you agree
 * to be bound by its terms.
 */

function newFillList()
{
	try {
		var qry = toolbox.executeQuery(mql, setParams());
		toolbox.populateXTreeWidget(_item, qry, false);
	}  
	catch (e)
	{
		toolbox.messageBox("critical", mywindow, mywindow.windowTitle, e.lineNumber + ": " + e);
	}
}

function setParams()
{
	var params = new Object;
	params.purchased = qsTr("Purchased");
	params.manufactured = qsTr("Manufactured");
	params.phantom = qsTr("Phantom");
	params.breeder = qsTr("Breeder");
	params.coProduct = qsTr("Co-Product");
	params.byProduct = qsTr("By-Product");
	params.reference = qsTr("Reference");
	params.costing = qsTr("Costing");
	params.tooling = qsTr("Tooling");
	params.outside = qsTr("Outside Process");
	params.assortment = qsTr("Assortment");
	params.job = qsTr("Job");
	params.planning = qsTr("Planning");
	params.kit = qsTr("Kit");
	params.error = qsTr("Error");
	params.useNumber = _searchNumber.checked;
	params.useDescrip1 = _searchDescrip1.checked;
	params.useDescrip2 = _searchDescrip2.checked;
	params.searchString = _search.text.toUpperCase();

	if(_showInactive.checked)
	{
		params.showInactive = true;
	}
	return params;	
}

var _item = mywindow.findChild("_item");
var _search = mywindow.findChild("_search");
var _searchDescrip1 = mywindow.findChild("_searchDescrip1");
var _searchDescrip2 = mywindow.findChild("_searchDescrip2");
var _searchNumber = mywindow.findChild("_searchNumber");
var _showInactive = mywindow.findChild("_showInactive");

var mql = " SELECT item_id,"
		+ " 	item_number, (item_descrip1 || ' ' || item_descrip2) AS description,"
		+ "     CASE WHEN (item_type='P') THEN <? value(\"purchased\") ?>"
		+ "     	WHEN (item_type='M') THEN <? value(\"manufactured\") ?>" 
		+ "         WHEN (item_type='F') THEN <? value(\"phantom\") ?>"
		+ "         WHEN (item_type='B') THEN <? value(\"breeder\") ?>"
		+ "         WHEN (item_type='C') THEN <? value(\"coProduct\") ?>"
		+ "         WHEN (item_type='Y') THEN <? value(\"byProduct\") ?>"
		+ "         WHEN (item_type='R') THEN <? value(\"reference\") ?>"
		+ "         WHEN (item_type='S') THEN <? value(\"costing\") ?>"
		+ "         WHEN (item_type='T') THEN <? value(\"tooling\") ?>"
		+ "         WHEN (item_type='A') THEN <? value(\"assortment\") ?>"
		+ "         WHEN (item_type='O') THEN <? value(\"outside\") ?>"
		+ "         WHEN (item_type='J') THEN <? value(\"job\") ?>"
		+ "         WHEN (item_type='L') THEN <? value(\"planning\") ?>"
		+ "         WHEN (item_type='K') THEN <? value(\"kit\") ?>"
		+ "         ELSE <? value(\"error\") ?>"
		+ "		END AS type, "
		+ "     location_name "	
		+ " FROM item "
		+ " 	LEFT OUTER JOIN itemsite ON item.item_id = itemsite.itemsite_item_id"
		+ "     LEFT OUTER JOIN location ON itemsite.itemsite_location_id = location.location_id"
		+ " WHERE ( ( ((<? value(\"useNumber\") ?>) AND (item_number ~* <? value(\"searchString\") ?>))"
		+ " 	OR ((<? value(\"useDescrip1\") ?>) AND (item_descrip1 ~* <? value(\"searchString\") ?>))"
		+ "     OR ((<? value(\"useDescrip2\") ?>) AND (item_descrip2 ~* <? value(\"searchString\") ?>)) )"
		+ " <? if not exists(\"showInactive\") ?> "
		+ "     AND (item_active) "
		+ " <?endif ?>"
		+ " ) "
		+ " ORDER BY item_number;";

// Add the new column(s) to the XTreeWidget
// All the original columns are set by xTuple core code so we can ignore them.
toolbox.addColumnXTreeWidget(_item, qsTr("Location"), -1, Qt.AlignLeft, true, "location_name");

// Disconnect handlers from the original code or the old 
// query runs and the new column(s) do not get re-populated.
toolbox.coreDisconnect(_showInactive, "clicked()", mywindow, "sFillList()");
toolbox.coreDisconnect(_searchNumber, "toggled(bool)", mywindow, "sFillList()");
toolbox.coreDisconnect(_searchDescrip1, "toggled(bool)", mywindow, "sFillList()");
toolbox.coreDisconnect(_searchDescrip2, "toggled(bool)", mywindow, "sFillList()");
toolbox.coreDisconnect(_search, "lostFocus()", mywindow, "sFillList()");
toolbox.coreDisconnect(mainwindow, "itemsUpdated(int, bool)", mywindow, "sFillList()");

// ... and connect the new handlers.
_showInactive.clicked.connect(newFillList);
_searchNumber.toggled.connect(newFillList);
_searchDescrip1.toggled.connect(newFillList);
_searchDescrip2.toggled.connect(newFillList);
_search.lostFocus.connect(newFillList);
mainwindow.itemsUpdated.connect(newFillList);

// Run newFillList() once on load.
newFillList();

petebisson · March 13, 2017, 9:15pm

No joy with the attachments - obviously the forum file upload is broken

You can either do a global search and remove on all the extra
tags on the inline version or you can drop me an email to xtuple[at]bisson.co.uk and I’ll send you the file by return.

Hopefully this will get fixed soon…

Cheers,

Pete

petebisson · March 13, 2017, 9:15pm

… And I’ll try it by email just in case the site is broken

changopiano · March 13, 2017, 9:15pm

I removed all the tags and it works great. Thanks! Now I need to find what is different between ours so that I can duplicate it in the future with other screens. Thanks again.

nlewis · March 13, 2017, 9:42pm

I have solved this problem too, but did it without overriding the core code’s sFillList() and query.

My technique is pretty simple:

Add the columns
Bind to the tree’s populated signal

In response to the populated signal:
3. Run your own query that contains the key of the items as well as your new column(s).
4. Iterate over the result of the query and use findXTreeWidgetItemWithId() to locate the row.
5. Use xtreeWidgetItem.setData() to set the data for the column(s)

The attached script adds characteristic names, values, and class codes to the BOM screen.

Note that characteristic values come back from the core query, so all that we need is the characteristic names and class code names.

It is clearly less efficient than running a new query that contains everything, but it avoids duplicating what the core code does making it more compatible with future core changes.

Also, binding to the tree’s populated signal means you don’t have to track down all the places the core’s sFillList() is bound to.

(ignore the moveColumn() stuff, that’s a feature I’m trying to get into the core right now)

PhilMcIntosh · March 13, 2017, 10:17pm

So is the .moveitem supposed to let us place new columns added to core display screens in the column order we would like as opposed to just adding to the end?

Was it ever put in the core? If so, what version?

nlewis · March 13, 2017, 10:17pm

Yes, moveColumn() was integrated into the core, quite a while ago - sometime in 3.x