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]
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:
FS: Default the State Tax Code
If you would like to default the Customer Ship-To Tax Code to a specific value, create a query and paste in the SQL at the bottom of the post. Change the the insert your default value where noted and create a formatted search, triggered off of the address name, on the State Tax Code field. The query will retain any values that already exist in the field.
SELECT
Case When $[$-178.12.0] = ''
XL Designer: I added some fields, but the report produces nothing.
While listening to one of the expert sessions, I caught that XL Reporter will not show any information unless a measure (i.e., quantity, Dollar Sales, Etc.) is selected. I had created a simple customer list just to test it out and it kept returning no information. Once I added a value (measure) it produced the list.