Archive for Customization
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. [...]
XL Reporter
I too a look at the sample report pack from SAP and I noticed that when they created a graph (even over expanding groups), they were selecting the cell below the value as well.
When I first added the graph to the report, I selected the expanding row value (e.g., $B$6) only as the data values [...]
Creating Custom Queries in SAP Business One
There are two tools for creating custom queries in SAP Business One, both of which will walk you through making your own query:
Query Generator
Query Wizard
The end result of both tools listed above is a results window with the actual query statement in the top portion of the window and any data that was retrieved in [...]
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 [...]
The Anatomy of a SQL Statement – SQL Statement Breakdown (Part 2)
In the previous discussion I introduced a simple SQL query that selected all of the customer numbers and names from the SAP Customer/Vendor/Lead Master file. The query actually selects all three types (customers, vendors, and leads) because SBO uses one table/file to store this information. How you will know which type you are looking at [...]
