Queries

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]

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...

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.

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.

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.

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'.

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

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

When referring to a User Defined Table (UDT) in Query use the following syntax:

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'