Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Create SAP B1 Query with Optional User Input Fields

Hi All... any help is greatly appreciated, I am new to this forum and hope to contribute in the near future once I become more of an expert with B1.

I have a query I need to build that will search about 10 UDF's. The problem is, I don't know how to make the user input fields optional within the query. Currently, I have the following queries as testing searching 2 UDF"s:

SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand] FROM OITM T0 WHERE T0.[U_Quality] = [%0] OR T0.[U_LengthFT] =[%1]

This query works when leaving one of the user input fields blank. However, the values it provides are wrong because it is an OR statement which will show item codes with a certain quality OR a certain size.

The following query is the same as above but switched the OR to AND:

SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand] FROM OITM T0 WHERE T0.[U_Quality] = [%0] AND T0.[U_LengthFT] =[%1]

This query provides me with the correct values but will not work if one of the user defined fields is not filled out.

How do I go about getting the results I want and not having the user fill out all the UDF's for the query to execute properly?

Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question