RE: Multiple formula fields in Advanced Template PDF/Html
Hello!
I am trying to add a search with some joined fields to an Advanced PDF/Html template.
I define the search in suitescript and I add it to the template with
renderer.addSearchResults("template_rows",searchresult);
If I normally define the fields by using join
columns.push(new nlobjSearchColumn("externalid", "item"));
I don’t know how to retrieve the data inside the Template (${template_row.externalid} simply doesn’t work).
As a workaround I tried to define the fields as formulatext.
It works, the value is passed to the template and I can retrieve it from the template both with ${template_row.formulatext} and with ${template_row[1]} where 1 is the index of the column.
Unfortunately I have more than one field. In that case it seems that the last win. I define more than one field, everything seems to work but in the template I get only the last defined. For example in the following case I don’t find the color field but only the size field. And I find it at position 1 as it would have overwritten the color column.
var filters= []; filters.push(new nlobjSearchFilter("internalid",null,"is", 40556971)); filters.push(new nlobjSearchFilter("mainline",null,"is", "F")); filters.push(new nlobjSearchFilter("shipping",null,"is", "F")); filters.push(new nlobjSearchFilter("taxline",null,"is", "F")); filters.push(new nlobjSearchFilter("accounttype",null,"anyof","COGS")); var columns=[]; columns.push(new nlobjSearchColumn("externalid", "item")); column=new nlobjSearchColumn("formulatext"); column.setFormula("{item.custitem_flb_santoni_colore}"); column.setLabel("color"); columns.push(column); column=new nlobjSearchColumn("formulatext"); column.setFormula("{item.custitem_flb_santoni_taglia}"); columns.push(column); columns.push(new nlobjSearchColumn("accounttype")); columns.push(new nlobjSearchColumn("quantity")); var searchresult = nlapiSearchRecord("transaction", null, filters, columns );
Could anybody help me?
Thanks in advance
I tried to use formulatext_1 but it doesn’t work.
Meanwhile I received an answer by Scott on slack channel.
In order to distinguish different formulas in template we can add a unique id after formulatext.
For example one can add “formulatext_color” and then retrieve it from template with ${formulatext_color}.
I think this is what Netsuite do on saved searches created on UI.
With this hint my code becomes:
var filters= []; filters.push(new nlobjSearchFilter("internalid",null,"is", 40556971)); filters.push(new nlobjSearchFilter("mainline",null,"is", "F")); filters.push(new nlobjSearchFilter("shipping",null,"is", "F")); filters.push(new nlobjSearchFilter("taxline",null,"is", "F")); filters.push(new nlobjSearchFilter("accounttype",null,"anyof","COGS")); var columns=[]; column=new nlobjSearchColumn("formulatext_externalid"); column.setFormula("{item.externalid}"); columns.push(column); column=new nlobjSearchColumn("formulatext_color"); column.setFormula("{item.custitem_flb_santoni_colore}"); columns.push(column); column=new nlobjSearchColumn("formulatext_size"); column.setFormula("{item.custitem_flb_santoni_taglia}"); columns.push(column); columns.push(new nlobjSearchColumn("accounttype")); columns.push(new nlobjSearchColumn("quantity")); var searchresult = nlapiSearchRecord("transaction", null, filters, columns );
I don’t understand if it is an “official” solution or if it is a workaround: anyway by now it works very well.