https://netsuiteprofessionals.com logo
Join Slack
Powered by
Hi All. I’m trying to create a query that I think...
# suiteql
j
Hi All. I’m trying to create a query that I think should be fairly easy, but stumbling badly… On our sales orders, we use the “main or header (mainline=T)” location to designate what office a sales person is from. I want to sum up specific inventory items and discounts that are the line items of a sales orders over a specific time. The problem is that I can’t figure out how to group by the “header/main” location. I’ve tried all kinds of joins with the transaction and transaction line tables. I think it mainly comes down to being able to access the “header/main” location from the transaction line level. Additionally, I might need to look at the location from the “created from” quote as well. Below is a sample of what I am looking for. Any ideas or pointers would be greatly appreciated!
c
Just brainstorming, but maybe join a CTE that's just an internalid + mainline location map?
j
@Clay Roper Thanks for the response. I'm not sure i follow how to do that.
c
As I understand it, CTE is a common table expression, defined in a WITH clause at the top of a query. This snippet shows a simplified approach to be adapted to your specific needs. There may be more optimized ways of solving this by someone with savvier SQL-fu 😄
Untitled.sql
j
@Clay Roper very interesting! Thanks, i'll give this a close look in the morning... Much appreciated. You just might have snatched the pebble from my hand, lol
c
🦗
j
@Clay Roper Hi Clay. I finally got some time to look at your suggestion. It is definitely picking up the right rows when i modify the where... The only issue i am having is that i get an error when i try to group by. I have tried to group by fields in each of the different tables as well as in the with clause. My research leads me to believe that i am doing it correctly. i wonder if this is a NS restriction?
c
@JR Post your query here as a snippet?
j
I added a bunch of stuff here, but you can see the commented out GROUP BY's
Copy code
WITH so_locations AS (
	SELECT
		transaction,
		location
	FROM transactionline
	WHERE mainline = 'T' 
	/* GROUP BY location */
)		
SELECT
	so_locations.transaction,
	line.mainline,
	so_locations.location,
	BUILTIN.DF(tran.ID) AS TranId,	
	tran.ID,
	tran.type,
	line.item,
	line.itemType ,
	BUILTIN.DF( line.Item ) AS ItemID,
	line.foreignamount
FROM transactionline AS line

	JOIN transaction AS tran
		ON   tran.id = line.transaction

	JOIN so_locations
		ON   so_locations.transaction = line.transaction

WHERE
	tran.type = 'SalesOrd' 
	AND line.mainline = 'F'
	AND tran.TranDate >= '5/1/2024'
	AND tran.TranDate < '6/1/2024'
	AND line.itemType in ('InvtPart', 'Discount', 'Service')
	AND tran.custbody2 NOT LIKE ('%x%')

ORDER BY
	so_locations.location

/*
GROUP BY
	tran.ID
*/
c
@JR You need to GROUP BY every column in your SELECT clause that isn't an aggregate or analytic function (like SUM)
j
hmmm... ok, i'll trim it down and give that a try, thanks!
🙌 1
c
Any column that you're not performing a function on, like counting, summing, getting the min/max from, and that you don't want to group by, shouldn't be in the SELECT clause
j
agreed. i put them there to confirm that i was getting all the rows i wanted...
👍 1
@Clay Roper that gets me really close! the only issue i have now is to convert the location to something readable. It chokes on the BUILTIN_DF function. I might have to join the location table to get then name (i'll try that later). Below is the latest query and results... Thanks so much for your help!
Copy code
WITH so_locations AS (
	SELECT
		transaction,
		location
	FROM transactionline
	WHERE mainline = 'T' 
)		
SELECT
	so_locations.location, 
	SUM(line.foreignamount) AS AMT
	/* BUILTIN.DF(so_locations.location), */

FROM transactionline AS line

	JOIN transaction AS tran
		ON   tran.id = line.transaction

	JOIN so_locations
		ON   so_locations.transaction = line.transaction
WHERE
	tran.type = 'SalesOrd' 
	AND line.mainline = 'F'
	AND tran.TranDate >= '5/1/2024'
	AND tran.TranDate < '6/1/2024'
	AND line.itemType in ('InvtPart', 'Discount', 'Service')
	AND tran.custbody2 NOT LIKE ('%x%')
GROUP BY
	so_locations.location
ORDER BY
	so_locations.location


/*
	so_locations.transaction
	line.mainline,
	BUILTIN.DF(tran.ID) AS TranId,	
	tran.ID,
	tran.type,
	line.item,
	line.itemType ,
	BUILTIN.DF( line.Item ) AS ItemID,
	AVG(line.foreignamount) AS AMT

ORDER BY
	so_locations.transaction
GROUP BY
	so_locations.location
HAVING
	tran.ID > 0

	Transaction.TranDate >= '4/1/2024'
	AND Transaction.TranDate < '5/1/2024'
	AND Transaction.Type = 'SalesOrd'
	AND  TransactionLine.Mainline = 'F'
	AND TransactionLine.itemType in ('InvtPart', 'Discount', 'Service')
	AND Transaction.custbody2 NOT LIKE ('%x%')
*/
c
@JR You could update the CTE
Copy code
WITH so_locations AS (
	SELECT
		transaction,
		location AS location_id,
        BUILTIN.DF(location) AS location_name
	FROM transactionline
	WHERE mainline = 'T' 
)
then you'd have 
location_id
for your joining and ordering, and 
location_name
for the results
j
oooo, i'll try that after dinner!!!
c
@JR any luck?
j
@Clay Roper Hi Clay. Yes, some... as you can see below, i am now able to group the data correctly by location name and carry out some simple case statements to figure out sales targets and progress towards those targets. What i can't seem to figure out is how to segregate the data into two other buckets (move sales and contract sales). retail and contract sales should be determined by a custom field called "Division" in the "header" of the sales order. i want to query that field in a case statement. Additionally, the "move sales" column should only pertain to items (thus line items on the sales order) with the word "Assembly" in it. so, in this case, i would be interrogating the item text of each item that the line level. I've done a bit of a deep dive along with trying numerous solutions, including creating unions for each other these date points to no avail. It seems that since the values i want to interrogate are not available, since they are not in the select list. You can see the commented out code in the case statements for a gist of what i'm trying to do. I'm not sure if what i want is even possible anymore...
Copy code
WITH so_locations AS (
	SELECT
		transaction,
		BUILTIN.DF(location) AS location_name
	FROM transactionline
	WHERE mainline = 'T' 
)		
SELECT
	location_name,
	MAX(line.item) AS mylineitem,
	(SUM(line.foreignamount) * -1) AS 'Retail Sales',

   	CASE
       	WHEN location_name = 'Los Angeles' THEN '[ $667,000 ]'
       	WHEN location_name = 'New York' THEN '[ $667,000 ]'
       	WHEN location_name = 'San Francisco' THEN '[ $200,000 ]'
       	WHEN location_name = 'Seattle' THEN '[ $667,000 ]'
	END AS target,

   	CASE
       	WHEN location_name = 'Los Angeles' THEN 667000 - SUM(line.foreignamount)
       	WHEN location_name = 'New York' THEN 667000 - SUM(line.foreignamount)
       	WHEN location_name = 'San Francisco' THEN 667000 - SUM(line.foreignamount)
       	WHEN location_name = 'Seattle' THEN 667000 - SUM(line.foreignamount)

	END AS progress,

   	CASE
        	/* WHEN ((location_name = 'Los Angeles') AND (line.item LIKE '%assembly%')) THEN '-999' */
        	WHEN location_name = 'New York' THEN '[ $667,000 ]'
        	WHEN location_name = 'San Francisco' THEN '[ $200,000 ]'
        	WHEN location_name = 'Seattle' THEN '[ $667,000 ]'
	END AS 'Move Sales',

   	CASE
        	/* WHEN ((location_name = 'Los Angeles') AND (tran.division = 'Contract')) THEN '-999' */
        	WHEN location_name = 'New York' THEN '[ $667,000 ]'
        	WHEN location_name = 'San Francisco' THEN '[ $200,000 ]'
        	WHEN location_name = 'Seattle' THEN '[ $667,000 ]'
	END AS 'Contract Sales'

FROM transactionline AS line
	JOIN transaction AS tran
		ON   tran.id = line.transaction

	JOIN so_locations
		ON   so_locations.transaction = line.transaction
WHERE
	tran.type = 'SalesOrd' 
	AND line.mainline = 'F'
	AND tran.TranDate >= '4/1/2024'
	AND tran.TranDate < '5/1/2024'
	AND line.itemType in ('InvtPart', 'Discount', 'Service')
	AND tran.custbody2 NOT LIKE ('%x%')
GROUP BY
	so_locations.location_name
ORDER BY
	so_locations.location_name
c
line.item
will return the integer internal ID - if you want to compare the text of the item name, you should use 
BUILTIN.DF(line.item)
. If the text "Assembly" would be elsewhere, such as the item description, you'll need to join the item table in, or, if you trust that staff won't be editing the description, you could use 
line.memo
I suspect the same is true of 
division
, that it's a List/Record type field and returns the internal ID if you don't join or use 
BUILTIN.DF
j
@Clay Roper understood on the data types char/id and yes, division is a list/record. i'm getting the following error on just the simplest case here below, If i leave the Group by and Order by in - "Search error occurred: Invalid or unsupported search" . if i remove them, i get one row for each line and the case statement works correctly, identifying the "assembly" text. Maybe it's an issue on how and when it evaluates the group by, compared to the case statement???
Copy code
WITH so_locations AS (
	SELECT
		transaction,
		BUILTIN.DF(location) AS location_name
	FROM transactionline
	WHERE mainline = 'T' 
)		
SELECT
	location_name,

   	CASE
        	WHEN ((location_name = 'Los Angeles') AND (BUILTIN.DF(line.item) LIKE '%assembly%')) THEN 'LA Move' 
        	WHEN ((location_name = 'New York') AND (BUILTIN.DF(line.item) LIKE '%assembly%')) THEN 'NY Move' 
        	WHEN ((location_name = 'San Francisco') AND (BUILTIN.DF(line.item) LIKE '%assembly%')) THEN 'SF Move' 
        	WHEN ((location_name = 'Seattle') AND (BUILTIN.DF(line.item) LIKE '%assembly%')) THEN 'SEA Move' 
	END AS 'Move Sales'
Copy code
FROM transactionline AS line
	JOIN transaction AS tran
		ON   tran.id = line.transaction

	JOIN so_locations
		ON   so_locations.transaction = line.transaction
WHERE
	tran.type = 'SalesOrd' 
	AND line.mainline = 'F'
	AND tran.TranDate >= '4/1/2024'
	AND tran.TranDate < '5/1/2024'
	AND line.itemType in ('InvtPart', 'Discount', 'Service')
	AND tran.custbody2 NOT LIKE ('%x%')
GROUP BY
	so_locations.location_name
ORDER BY
	so_locations.location_name
c
You need to group by the CASE statements as well
121 Views
Open in Slack
PreviousNext
Powered by