Archive for Formatted Search
SAP Business One Retrieving the Ship-To State for a Sales Order
It was important to one client to be able to do sales reporting by state (which was done using the query tool). In SBO, the address on the marketing documents is one large field and you cannot get the state unless you get the information from the BP (Business Partner) master for scan through the [...]
SAP Business One Creating a User Defined Field for Marketing Documents – Basic
Backup your database. Get everyone out of the system.
Create a UDF (User Defined Field) (Tools->User-Defined Fields) at the marketing document header by clicking on the yellow (or gold) arrow to the left of the Marketing Documents row.
Look for the row below that called Title and click on it. In the Lower right hand corner [...]
SAP Business One Assigning a Formatted Search Query to a Field
Instuctions on how to assign a form query to a field:
Open a form.
Click on the field.
Select for the drop down menus at the top of the screen: Tools->Search Function->Define
A window will appear. Click on Search by Saved Query. Double Click on the long [...]
SAP Business One: 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.
/* Shows all sales orders where the qty ordered is greater */ /* than or equal to the amount within the [...]
SAP Business One: 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
Thanks to Ed Monk of SBONotes.com
SAP Business One 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 [...]
SAP Business One How To Enter a User Defined Table (UDT) in Query
When referring to a User Defined Table (UDT) in Query use the following syntax: [dbo].[@Table_Name]
Thanks to Ed Monk of SBONotes.com
SAP Business One: 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 [...]
SAP Business One Formatted Search: 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. [...]
SAP Business One The Anatomy of a SQL Statement: Rename your columns and tables (Part 3)
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 The fields ItemCode and ItemName will appear as the column names for the data when the query is executed. Many times the field names (the ones from the [...]
