https://netsuiteprofessionals.com logo
Join Slack
Powered by
I can't seem to ge the N/query groupBy to work, as...
# suitescript
s
I can't seem to ge the N/query groupBy to work, as I can't find a good example in the API. Any suggestions would be helpful. I'm ultimately using addQuery() to renderAsPDF.
Copy code
var paymentsQuery = query.create({
              type: 'customrecord_x',
              columns: [
                	{fieldId: 'custrecord_date'},
                	{fieldId: 'custrecord_type'},
                    {fieldId: 'custrecord_company'},
                	{fieldId: 'name'},
                	{fieldId: 'custrecord_amount'},
              ],
              groupBy: 'custrecord_type'
            });


			var resultSet = paymentsQuery.run();
			log.debug('paymentsQuery Result:', resultSet);
r
look like you are trying to return non agrigated columns which I beleive is a nono
Copy code
SELECT
	Employee.LastName,
	Employee.FirstName,
	SUM( Transaction.ForeignTotal ) AS TotalSales,
	SUM( Transaction.TotalCostEstimate ) AS TotalCostEstimate,	
	SUM( Transaction.EstGrossProfit ) AS TotalGPEstimate
FROM
	Transaction
	INNER JOIN Employee ON
		( Employee.ID = Transaction.Employee )	
WHERE
	( Transaction.Type = 'SalesOrd' )
	AND ( Transaction.ShipDate BETWEEN TO_DATE( '2020-11-01', 'YYYY-MM-DD' ) AND TO_DATE( '2020-11-30', 'YYYY-MM-DD' ) )
	AND ( Transaction.Voided = 'F' )
GROUP BY
	Employee.LastName,
	Employee.FirstName
ORDER BY
	Employee.LastName,
	Employee.FirstName
look at this query
all the returning fields are either aggrigate functions or in the group by
maybe you are wanting order by
but in your query it groups the records and expects to return only one row for each rec_type so it doesnt know which record_date to retrun for that row. should it be first, min, max, avg?
s
Ohhhhhh....Okay!
I'm into running total (by group) territory
r
what are you ultimately trying to return?
total would be sum() likely
are you trying to return all records as well as a sum of some fields?
s
invoice line items by subsidiary
Yes
r
are you trying to get the sum amount of the line items to show at the bottom of all item details?
s
yes
r
if so, you might be best to sum them in freemarker in a variable as you loop through the LIST
or theoretically you could run an items query and a link them in free marker
or run a SuiteQL query instead of N/Query query and have an inner select to add sum to all record lines
s
A normal SQL query would be ideal.
r
then do it in freemarker
look at example at bottom but just retrun sum instead of avg
s
Okay, that works. I appreciate the help.
r
here
use this
s
Any idea what the N/query version of getText is? My list values are being returned as ID's.
r
try creating a formula field
Copy code
var myFormulaColumn = myTransactionQuery.createColumn({
    type: query.ReturnType.CURRENCY,
    formula: '{amount} * 125'
});
sorta like this
TEXT return type
and formula
Copy code
BUILTIN.DF( IDField )
not sure if it will work
or you could join to the table holding the status values
or try '.' lookup refernece like this
'vendor.companyname'
s
No luck.
r
is this for custrecord_type
s
Yes, Payment Method 1. Cash 2. Check 3. Amex 4. Discover 5. etc... it's returning 2 instead fo Check
r
what table or list holds values of cash or check
s
It's just a custom record that has a custom filed of type list (that points to the native NetSuite Payment Method list)
r
try adding field
paymentMethod.name
s
Copy code
var paymentsQuery = query.create({
              type: 'customrecord_x',
              columns: [
                	{fieldId: 'custrecord_date'},
                	{fieldId: 'custrecord_type'},
                    {fieldId: 'custrecord_company'},
                	{fieldId: 'name'},
                	{fieldId: 'custrecord_amount'},
                    {fieldId: 'custrecord_pay_method'},

              ]
            });
Yikes! Can't get it to work.
Almost done 🙂 I appreicate your help BTW.
r
really not sure, the fast hack is use switch in freemarker
Copy code
<#switch animal.size>
  <#case "small">
     This will be processed if it is small
     <#break>
  <#case "medium">
     This will be processed if it is medium
     <#break>
  <#case "large">
     This will be processed if it is large
     <#break>
  <#default>
     This will be processed if it is neither
</#switch>
on custrecord_pay_method
just print cash for example
s
Yeah, I was afraid I may have to resort to that.
b
BUILTIN.DF is the normal option to turn internal ids into text
r
i have not used in Query columns. do you use as formula column? or as fieil name/id
b
its not popular to use query.Query objects
BUILTIN.DF is to be used with suiteql
if you want to use the query and have actual columns, then you use options.context.name
s
Copy code
var paymentsQuery = query.create({
              type: 'customrecord_x',
              columns: [
                   {fieldId: 'custrecord_pay_type',
                     context: {
                        name: query.FieldContext.TEXT
                     }
                    },

              ]
            });
What am i doing wrong?
b
the vast majority of my statements make no sense if you dont follow the links in the documentation
the documentation for 
options.context.name
also has a link to valid 
query.FieldContext
keys
TEXT is not one of them
s
🙂 I apprecaite your help, but when you're 10 hours into fire drill session, things aren't always clear.
Copy code
name: query.FieldContext.DISPLAY
Did the trick. Thank you both for your time. I really, REALLY appreicate it!
Open in Slack
PreviousNext
Powered by