Blog

User Defined Prompts in SAP Business One Queries

By Ryan Howe

Customizing the prompts in Business One Queries

SAP Business one provides a powerful way to prompt users for parameters when running a query you define.  One tip is the ability to change the name of prompting text from the default to something more meaningful.

This method, gives you the ability to define any table/field combination as a user definable prompt.  Thus, when a user is prompted for a value, it can be anything you want instead of the limitation of it being the field being compared in the WHERE clause.  Thus, if we wanted to prompt the user for a range of dates to narrow down the documents we present, the prompts might look like the following.


Using the alternate technique a similar user prompt might appear as:

The term “From Date” and “To Date” are more intuitive than the first example.  The query below shows how to use this technique

/*Parameter Area*/
/*SELECT FROM [dbo].[OSRT] P0*/
declare @StartDate as datetime
/* WHERE */
set @StartDate = /* P0.FromDate */ ‘[%0]‘
/*SELECT FROM [dbo].[OSRT] P1*/
declare @EndDate as datetime
/* WHERE */
set @EndDate = /* P1.ToDate */ ‘[%1]‘
/*Main Query Area*/
SELECT T0.DocNum ‘Inv#’,T0.DocDate ‘Post Date’,T0.CardCode ‘Customer#’,T0.CardName ‘Customer Name’
FROM OINV T0
WHERE (T0.DocDate >= @StartDate and T0.DocDate <= @EndDate) ORDER BY T0.DocDate, T0.CardCode FOR BROWSE

 

We hope you found this helpful.  If you need any help in general with writing or using queries, feel free to hit the chat button below or leave us a message.

Ryan Howe

Partner, Clients First Business Solutions New Jersey

Ryan Howe is a Certified Public Accountant turned SAP Business One consultant. Leading the SAP Business One practice at Clients First, Ryan has over 23 years’ experience in the financial management and ERP industry, specializing in business analysis, relationship building, and business process optimization. When he’s not helping customers leverage SAP Business One to meet their short and long-term needs, you can find him at any Michigan State sporting event with his family – go Spartans!