Product Information
User Query Deserves a Count Widget
User queries are one of the first customization tools – being highly valuable, it became quickly very popular. Some customers have small number of user queries while others use plenty. Point is, user queries are an integral part of the daily course of work same as any out-of-the-box report or function. When it comes to your frequently used queries, SAP Business One enables you to increase efficiency, and create count widgets and add them to your Fiori-Style cockpit.
From the “Tools” menu choose: “Cockpit” > “Count Widget Setup”:
Here you can set a name and description to your widget and browse through the various queries to assign the one you need. Choosing the “Add” button saves your count widget to the gallery.
Now you just need to edit your cockpit, choose the count widget you created and position it where it fits best:
For additional information about count widgets for user queries, click here.
Available in SAP Business One 10.0, version for SAP HANA. This tip and all the other tips are available on the Tip of the week community page. You can also visit the Implementation Arena for useful implementation tips
Searching for amounts and dates is not functioning properly. I have attempted various methods for searching, but unfortunately, it is not working for monetary amounts. However, it is functioning correctly for other fields such as names and invoice numbers.
Dear Ahmed Bahram Maghdid
Thank you for your comment. If you want to filter by amounts or dates, the following method applies:
In the filter field use the relevant condition sign - e.g. "=" , ">" , "<" , then enter a value that exists in the list but without the separators. for example:
filtering the list to show only orders with total amount smaller than 10,573.00 :
and here is the filtered list:
Please have a try.
best regards,
Maya
Dear Maya Shiff,
Thank you for your prompt response. I appreciate your explanation on filtering by amounts, and I'm happy to report that searching for amount is working perfectly now. However, I'm still having some difficulty with searching for dates. When I attempt to filter by dates, it doesn't seem to work as expected. Could you please provide some guidance on how to effectively search for dates in the list?
Your assistance would be greatly appreciated.
Dear Ahmed Bahram Maghdid ,
After consulting with our developers, turned out that filtering by date columns is unfortunately not supported.
I know this is not ideal, but may I suggest as a workaround - you can execute the related query and filter the results using the filter icon in the toolbar where date format is supported. Alternatively, you can also export the results to Excel
+++ Another option can be, edit the query connected to the widget using "cast". For example - if you want to be able to filter the results of "My Sales Orders Not Delivered " system query by Posting Date column, add the line in green to the query expression:
SELECT
TT0."DocEntry" AS "Document Internal Key",
TT0."DocNum" AS "Document Number",
TT2."CardCode" AS "Customer Code",
TT2."CardName" AS "Customer Name",
TT0."DocDate" AS "Posting Date",
TT0."DocDueDate" AS "Delivery Date",
TT0."OpenAmount" AS "Open Amount",
TT0."InsTotal" AS "Original Amount"
FROM (SELECT
T0."DocEntry",
T0."DocNum" ,
T0."CardCode",
--T0."DocDate",
CAST(T0."DocDate" AS VARCHAR(10)) AS "DocDate",
T0."DocDueDate",
(T1."InsTotal" - T1."PaidToDate") AS "OpenAmount",
T1."InsTotal",
CASE WHEN T0."OwnerCode" IS NULL
THEN T0."UserSign"
ELSE T2."userId"
END AS "UserId"
FROM ORDR T0
INNER JOIN RDR6 T1 ON T0."DocEntry" = T1."DocEntry"
LEFT OUTER JOIN OHEM T2 ON T0."OwnerCode" = T2."empID"
WHERE T0."DocStatus" = 'O') TT0
INNER JOIN OUSR TT1 ON TT0."UserId" = TT1."USERID"
INNER JOIN OCRD TT2 ON TT0."CardCode" = TT2."CardCode"
ORDER BY TT0."DocDueDate",
TT0."DocNum"
Once you open the detailed results, you'll see the "Posting Date" format is changed:
If i use this filter expression:
The following result appears:
best regards,
Maya
Dear Maya Shiff,
Thank you so much for your assistance! Your guidance on navigating the challenges with date column filtering and providing effective workarounds is incredibly helpful. I truly appreciate your support in finding solutions. If there's anything else you can help with or clarify, please feel free to let me know. Thanks again!
Hi Maya,
Great article and we have customers using this.
One thing I would like to ask. How do you save the column width in the detailed results window that opens from your query?
All the columns seem to be a set width and I can't find where to adjust these so that it saves as default.
example
Any ideas?
Many thanks.