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]
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'
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] = ''
Need the Current Date to Use Within Your Query?
Get the current system date using the GetDate() value within your query...
Examples:
SELECT GetDate()
Returns the current date
Use the date for comparisons:
SELECT * FROM OINV WHERE DocDate <= GETDATE()
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] = ''
Simplified Order Guides for Sales Orders
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.
Commonly Used Tables for Queries
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
Looking for the Marketing Document you Just Entered?
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.
Tags:
The Anatomy of a SQL Statement - SQL Statement Breakdown (Part 2)
Previous Discussion:
The Anatomy of a SQL Statement - The Basics
The Anatomy of a SQL Statement: Rename your columns and tables (Part 3)
Previous Topic: The Anatomy of a SQL Statement - SQL Statement Breakdown (Part 2)
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