Prompting for User Input in SAP Business One Queries

SAP Business One actually has some very slick functionality when it comes to queries. One of those pieces of extra SQL is prompting a user for input. We utilize these extensively in our business. It provides an excellent way to create SQL queries that anyone can use without knowledge of writing SQL query. We have even employed this technique  to completely rewrite the stock SAP Business One item and customer searches!

Prompting for User Input

Usually, when you are writing reports based on SQL in SAP Business One, you have to have a basic working knowledge of SQL to manipulate the data. Running a query without changing the SQL will always give the same result. I don't know about you, but I'd rather not have most users of SAP Business One learning SQL.

The answer to this problem is designing your queries in SAP Business One prompting for user input. This will give your users a way to affect the results of the query without learning how to code in SQL.

To prompt a user for input simply use [%0] in your query in place of hard coded choices. For example, if you had a query that found a business partner name from their card code like:

SELECT T0.[CardName] FROM OCRD T0 WHERE T0.[CardCode] = 'C20000'

The above SQL query will always return that one answer and is not very useful for anyone to use.

Change the query to use user input and we have ourselves a useful query that anyone with no skills in SQL can use, like this:

SELECT T0.[CardName] FROM OCRD T0 WHERE T0.[CardCode] = [%0]

User Prompt

When using prompts your user will see a box that is asking for what ever field coded as a prompt. It even includes the search "gray ball" to bring up a list of choices! This functionality is really great!

Go even further by applying wildcard searches to the query like this:

SELECT T0.[CardName] FROM OCRD T0 WHERE T0.[CardCode] LIKE '%[%0]%'

Don't forget the single quote 'tick' marks around the wildcard and prompt. This is perfect for filling the query with open ended or partial user responses. You can start to see how we employed this technique in recreating SAP item and customer searches. Apply the query to a FMS search and instruct users to click the magnifying glass to bring up the custom query.

A major thing to note that this is a specialized SAP Business One only tool. If you are using SQL Studio Management Studio to write your query then you will have to make sure to replace the user input prompts for testing. Feel free to copy and paste these queries into your own system to try them out! Experiment with different ways to impress your users with user input to utilize data in SAP Business One.