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.
