Archive for Customization
SAP Business One How to Copy One Screen Layout to Another User
NOTE: This example is not recommended for novice users. You must have access to the SBO SQL Database and be comfortable with updating your production database. Updating your production database directly could void your SBO warranty and/or service agreement. Please consult with a DBA before proceeding. Updating queries cannot be run within the SBO query [...]
SAP Business One How to Access the Query Manager
Access the Query Manager in two ways:
From the drop-down menus at the top of the screen, go to: Tools -> Queries -> Query Manager
Click on the Query Manager icon located on the icon bar at the top of the screen.
The Query Manager window will appear…
Thanks to Ed Monk of SBONotes.com
How to Make a Print Layout (PLD) Report from a SBO Query
Follow these steps:
Create a Query in SAP Business One
Open the Query Manager
Find your Report
Highlight your report (single click)
Click on the Create Report button at the bottom of the window.
A window appears with the name of your query at the top of the window.
Window is divided into two parts: Top is existing reports, bottom is a [...]
SAP Business One 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’.
/* Shows the open balances by Customer Group */ SELECT gp.GroupCode, gp.GroupName, Sum(cs.Balance) ‘Open Balance’
FROM OCRD cs INNER JOIN [...]
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 Query: Customers Added Within a Certain Number of Days
Query will prompt the user for a number of days and show the customers that were added within that number of days. Copy and paste into the Query Generator and run. /*This query will return all customers that have been added to the system */ /*within the number of [...]
SAP Business One Enhanced User Prompts in Queries
The use of user prompted parameters in Business One queries provides powerful flexibility to the Business One reporting environment. A limitation was discovered with the use of this technique. It appears that some erroneous error messages can occur when the user prompted parameters are used in long queries or when they are used in certain [...]
SAP Business One Birthday List! (without the year)
A quick and easy query to give you a list of the birthdays from the Employee Master Data application in SBO.
Copy and paste the lines below into the Query Generator tool.
/*———————–*/ /* Displays a list of all the employees in SBO with their birthdays (without the year) */
SELECT lastname as ‘Last [...]