I have created a SuiteQL query which show the sale...
# generalk
Kaif Momin
12/15/2024, 8:49 AMI have created a SuiteQL query which show the sales order of last 15 days, then I used in portlet (simple form) to show it on a dashboard. the problem is it show more than 100 SO on dashboard is there a way to customize the portlet so that it will show SO in pagination like 10 record per page?
b
Ben Barnett
12/15/2024, 8:41 PMCould you just LIMIT the query to 10 results and order based on date ASC?
k
Kaif Momin
12/16/2024, 11:42 AMThen it just shows the top 10 records. I want it to get all record but just show 10 record at first page.
a
Aaron McCausland
12/16/2024, 6:07 PMHm, I know there are APIs in SuiteScript to do pagination of query and search results, and you could program buttons into a portlet script to do that, but I don't know simpler ways yet.
k
Kaif Momin
12/17/2024, 4:23 PMMy job is at stake I need to do it at any cost. could you please help me?
a
Aaron McCausland
01/07/2025, 6:53 PMI'm so sorry I didn't see this message until today! I was gone for the holidays for about 2 weeks. While a pagination feature is an odd thing to have such high stakes, I do want to help you, but I also think someone else would be far better qualified for solving this particular problem.
That being said, I do want to help! So if you still need help with it, please show me the SuiteQL query you're currently using, and how you have configured the portlet.
Aaron McCausland
01/07/2025, 7:16 PMIf it doesn't need to be a SuiteQL query and you could get the same data in a Saved Search, then you could do it without a scripted Portlet, and just use a List dashboard tile instead, like in my screenshot below. To get this working I chose Transaction as the List type, and chose my saved search as the View within that List tile. The saved search has to have the Available as Dashboard View checkbox ticked, and I filtered it to Type = Sales Order, Main line = Yes, and Date Created on or after 15 days ago (choose Relative as the date filter type). The List tile lets you limit the page size on the dashboard to a certain number of rows at a time (10 by default), but comes with built-in pagination controls and you don't have to script anything. This should work as long as you can get the Saved Search to bring you all the same data that the SuiteQL query does.
Aaron McCausland
01/07/2025, 7:20 PMI tried using the same saved search in a Custom Search tile, and got the same results. I'm not sure why NetSuite has List tiles AND Custom Search tiles, but the Custom Search tiles have a few other things you can configure and might work a little differently. Custom Search tiles give you everything you need for this though, like the page size limiter and the pagination controls:
Aaron McCausland
01/07/2025, 7:23 PMSend me the SuiteQL query in a direct message (with the identifying information redacted or substituted out), and I'll determine whether it can be done in a saved search. If it can't then I think we'll have to create a scripted portlet (unless you know how to get SuiteQL query results to display in some kind of dashboard tile that's not a scripted portlet).
k
Kaif Momin
01/08/2025, 1:50 PMthanks for your concern, but luckily I've found a way to do that using inline HTML portlet. but here is the query let me know if you can achieve the same thing with saved search because i was not able to do it. my blocker is i want to get those SO who have foreign character in their shipping address (other than character allowed for English even other languages).
SELECT
Transaction.TranDate AS order_date,
Transaction.TranID AS sales_order_number,
TransactionShipment.DestinationAddress AS shipping_address,
REPLACE(BUILTIN.DF(Transaction.Status), 'Order : ', '') AS order_status
FROM
Transaction
INNER JOIN TransactionShipment ON TransactionShipment.Doc = Transaction.ID
LEFT JOIN TransactionLine ON TransactionLine.Transaction = Transaction.ID
where
Transaction.TranDate BETWEEN SYSDATE - 200 AND SYSDATE
AND Transaction.Type = 'SalesOrd'
AND Transaction.Status NOT IN ('SalesOrd:G', 'SalesOrd:H', 'SalesOrd:F')
AND TransactionShipment.DestinationAddress IS NOT NULL
AND REGEXP_LIKE( BUILTIN.DF(TransactionShipment.DestinationAddress), '[^ -~]' )
GROUP BY
Transaction.TranDate,
Transaction.TranID,
TransactionShipment.DestinationAddress,
BUILTIN.DF(Transaction.Status),
BUILTIN.DF(TransactionLine.Location)
ORDER BY
Transaction.TranID ASC
🙌 1
✅ 1
a
Aaron McCausland
01/08/2025, 5:21 PMI'm glad you were able to work it out!
I'm building what I think the equivalent in Saved Search would be, but I can't find anything in the SuiteQL tables reference on my account for "TransactionShipment".
I wonder if that is part of a feature I haven't turned on, or if it's a custom record type or something.
37 Views