https://netsuiteprofessionals.com logo
Join Slack
Powered by
Hi Everyone. I am working on a project requiring ...
# suiteql
h
Hi Everyone. I am working on a project requiring a query to retrieve data in below format 0 : Inventory_Item_Internal_ID 1 :Inventory_Item_NameNumber 2 ;Inventory_Product_Category 3 :Inventory_Location_Abbreviation 4 :Inventory_Capsules 5 :Inventory_Themes 6 :Inventory_MH3_Class 7 :Inventory_MH1_Division 8 :Inventory_Lifestyle_Category 9 :Inventory_Size 10 :Inventory_Base_Price_ 11 :Inventory_Original_Price_ 12 :Transactions_Transaction_Number 13 :Date_Transaction_Date 14 :Transactions_Units_Sold_Net_Qty 15 :Transactions_Net_Sales_For_Product_ 16 :Item_ON_HAND Could you please assist in creating a query that combines these columns for analysis? Your expertise would be greatly appreciated in making this efficient and structured.
j
It would be easier to help you if you show where you are stuck. Can you post what you have so far?
h
I need to write a query to retrieve data for the specified columns. Could you please assist me in writing the query? I’m unsure which table I should query.
0 : Inventory_Item_Internal_ID 1 :Inventory_Item_NameNumber 2 ;Inventory_Product_Category 3 :Inventory_Location_Abbreviation 4 :Inventory_Capsules 5 :Inventory_Themes 6 :Inventory_MH3_Class 7 :Inventory_MH1_Division 8 :Inventory_Lifestyle_Category 9 :Inventory_Size 10 :Inventory_Base_Price_ 11 :Inventory_Original_Price_ 12 :Transactions_Transaction_Number 13 :Date_Transaction_Date 14 :Transactions_Units_Sold_Net_Qty 15 :Transactions_Net_Sales_For_Product_ 16 :Item_ON_HAND
j
I recommend starting with the Records Catalog to learn the tables / schema
h
I am on it
for this query as a starting point can you help me
"Select item.id as itemId,item.fullname as itemName,CUSTOMLISTCUSTOMLISTPSGS_PRDT_SIZ1.name AS itemSize, transaction.tranId,salesInvoiced.tranDate,salesInvoiced.amount As baseprice,salesInvoiced.amountnet AS netSales,salesInvoiced.itemCount, transactionLine.id,transaction.id as transaction,transactionLine.memo as description,item.totalQuantityOnHand,location.name as locationName,CUSTOMRECORD_TLS_LIFE_CAT.name AS productLifeStyleCategory,CUSTOMRECORD_TLS_PRODUCT_CATEGORY.name AS productCategory,CUSTOMRECORD_CSEG1.name AS Inventory_MH1_Division,CUSTOMRECORD_CSEG3.name AS Inventory_MH3_Class,AggregateItemLocation.quantityonhand AS locationLevelInventory,AggregateItemLocation.preferredstocklevel FROM salesInvoiced INNER JOIN transaction ON salesInvoiced.transaction = transaction.id INNER JOIN transactionLine  ON salesInvoiced.uniquekey = transactionLine.uniquekey INNER JOIN item ON item.id = salesInvoiced.item INNER JOIN location ON transactionLine.location = location.id INNER JOIN CUSTOMRECORD_TLS_LIFE_CAT ON item.custitem_tls_lifestyle_category = CUSTOMRECORD_TLS_LIFE_CAT.id INNER JOIN CUSTOMRECORD_TLS_PRODUCT_CATEGORY ON item.custitem_tls_product_category = CUSTOMRECORD_TLS_PRODUCT_CATEGORY.id LEFT JOIN CUSTOMRECORD_CSEG1 ON item.cseg1 = CUSTOMRECORD_CSEG1.id LEFT JOIN CUSTOMRECORD_CSEG3 ON item.cseg3 = CUSTOMRECORD_CSEG3.id INNER JOIN AggregateItemLocation ON ( AggregateItemLocation.Item = TransactionLine.Item ) AND ( AggregateItemLocation.Location = TransactionLine.Location ) LEFT JOIN CUSTOMLISTCUSTOMLISTPSGS_PRDT_SIZ1 ON transactionLine.custcol_tls_size_new = CUSTOMLISTCUSTOMLISTPSGS_PRDT_SIZ1.id  where  salesInvoiced.trandate = TO_DATE('2024-12-31', 'YYYY-MM-DD') AND salesInvoiced.itemCount != 0 AND Item.ItemType = 'InvtPart'"
Could you please assist me in optimizing this query? I need to retrieve records for products that are two years old or more. Additionally, I need to include the base inventory price for each product.
r
Have you tried pasting this exact question in #C06V7S5E77Z?
h
Nope
I will post there as well
10 Views
Open in Slack
PreviousNext
Powered by