Formatted Search

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

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] = ''

Get the current system date using the GetDate() value within your query...

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] = ''

For those who would like to try their hand at doing some queries for formatted searches, alerts, or reports, but do not know what the common table names are, here is a short list. Check back and I will update the list from time to time.

Master Files:

OITM = Item Master
OCRD = Customer, Vendor, and Lead Master
CRD1 = Customer Master Address
OCRG = Customer Master Group Code

Use this query to display a list of items that a customer likes to buy. It was made to be placed on the Item Number field on the Sales Order as a Formatted Search. The User will press Shift + F2 to display the window as opposed to pressing tab to display the entire catalog of items.

A reoccurring issue that users have with the marketing document entry modules within SBO is finding documents that were just entered by that user. Many consultants tell users to use the "back" arrow in the marketing documents, but this does not work very well when you have a room full of people entering documents. To help ease the search for their documents, I have implemented the following, simple query that is used within the marketing documents.

Using the example from the previous lesson, lets look at how we can make a SQL query more user friendly.

SELECT ItemCode, ItemName FROM OITM

Previous Discussion: The Anatomy of a SQL Statement ? The Basics