Formatted Search: Retreive Current Username
If you need to get the user name of the person currently signed in for a document field use this query:
SELECT T0.U_NAME FROM OUSR T0 Where internal_k = $[user]
No Link Arrows in Your Query?
If you are missing a link to arrow next to a field you would normally expect to see an arrow, you must have forgotten to put...
Maximum Number of Fields for Print Layout Designer (PLD)
While creating a report in Print Layout Designer (PLD) from a query I noted that the maximum number of fields that the PLD would import was 100.
No Field Names in your Print Layout Designer (PLD)?
If you do not use FOR BROWSE in your query and you attempt to create a Print Layout Designer (PLD) report from that query, you may find that there will be no field descriptions for the fields.
High Quantity on a Sales Order
Use this query to check for high order quantities on a Sales Orders.
If you make an alert out of this query, then you can be notified when potential keying errors occur.
Query to Show Open Balances by Customer (or Vendor)
Use this query to show you a list of open balances by customer group. If you would like to see
open balances by vendor, then change the Where clause to cs.cardtype = 'S'.
Query to Show Customers Without Sales Employees Assigned
Use the query to show all of the customers that do not have sales employees assigned. You can use this as an alert as well.
SELECT CardCode as BP, CardName as 'Customer Name' FROM OCRD Where SLPCODE = -1 and CardType = 'C' FOR BROWSE
Query to Show When Item Min/Max is Not Set
You can use this query to view or alert you to items where the min/max on the Inventory tab is not set for an item.
SELECT ItemCode, ItemName, MinLevel, MaxLevel FROM OITM Where MinLevel = 0 or MaxLevel = 0 FOR BROWSE
How To Enter a User Defined Table (UDT) in Query
When referring to a User Defined Table (UDT) in Query use the following syntax:
Alert: Check for BP Tax Codes
If a state tax code was not entered on the Customer's Ship-To address then when the Sales Order is invoiced you will receive a message stating that the tax code was not provided for the order. This Alert will notify someone when the tax code was not entered. Create a Query with the following SQL and associate it with an alert:
SELECT
T1.CardCode AS 'BP Code'