xTuple.com xTupleU Blog & News Customer Support

Create a script to add a column to an XtreeWidget

I seem to have run into a problem with the xtreewidget. I have a simple script.

That adds the column to the XTreeWidget. And I modified the Metasql as follows

-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. -- See www.xtuple.com/CPAL for the full text of the software license. SELECT vohead_id, COALESCE(pohead_id, -1), vohead_number, COALESCE(TEXT(pohead_number), TEXT(<? value("misc") ?>)) AS ponumber, (vend_number || '-' || vend_name) AS vendor, vendtype_code, vohead_invcnumber, vohead_distdate, COALESCE(vohead_gldistdate, vohead_distdate) AS postdate, vohead_amount, -- ADDED PORTON (ROUND((SELECT (COALESCE(dist,0) + COALESCE(freight,0) + COALESCE(tax,0)) FROM (SELECT SUM(COALESCE(voitem_freight,0)) AS freight FROM voitem WHERE (voitem_vohead_id=vohead_id)) AS data1, (SELECT SUM(COALESCE(vodist_amount, 0)) AS dist FROM vodist WHERE ( (vodist_vohead_id=vohead_id) AND (vodist_tax_id=-1) )) AS data2, (SELECT SUM(tax * -1.0) AS tax FROM ( SELECT ROUND(SUM(taxdetail_tax),2) AS tax FROM tax JOIN calculateTaxDetailSummary('VO', vohead_id, 'T') ON (taxdetail_tax_id=tax_id) GROUP BY tax_id) AS taxdata) AS data3),2)) AS balance, -- END ADDITION -- 'curr' AS vohead_amount_xtnumericrole, formatDate(vohead_distdate) AS f_distdate, formatDate(COALESCE(vohead_gldistdate, vohead_distdate)) AS f_postdate, formatMoney(vohead_amount) AS f_amount FROM vendinfo JOIN vendtype ON (vendtype_id=vend_vendtype_id) JOIN vohead ON (vohead_vend_id=vend_id) LEFT OUTER JOIN pohead ON (vohead_pohead_id=pohead_id) WHERE (NOT vohead_posted) <? if exists("vend_id") ?> AND (vend_id=<? value("vend_id") ?>) <? elseif exists("vendtype_id") ?> AND (vend_vendtype_id=<? value("vendtype_id") ?>) <? elseif exists("vendtype_pattern") ?> AND (vendtype_code ~ <? value("vendtype_pattern") ?>) <? endif ?> ORDER BY vohead_number;

Its design was to display the balance in the last column if the table. my query with manual parameter entry works.

But in Xtuple ui it does not display

So the first thing I look for is, have you named the column in the metaSQL the same as the added column. In your case it looks like you have.

Next thing I do when troubleshooting MetaSQL is to run the built query in a SQL Editor like PgAdmin to see if the query is returning data correctly. You could copy and paste the metaSql into the editor and replace all the variables, but for large or complex metaSQL, I tend to force an error in the metaSQL by adding xx characters somewhere.

ORDER BY vohead_number xx;

The metaSQL parser will build the query for you and then fail. You can find the built SQL in the database error log. Copy and paste that into your editor, remove the xx characters and run the query. You can then see if the balance column is being correctly populated.

The other benefit of forcing errors is you can see what parameters are being passed into the metaSQL.

Add to your script the following at the end of the script:


The window is loading before the script is run.

Thanks for the info about forcing errors, I had not though about using that to get the metasql parameters elsewhere and I know that times it will be helpfull.

Such a simple solution that I would have never found haha thanks for the help