A powerful skill to learn when digging into SQL query and writing formatted search (FMS) is learning how to construct SAP Business One system variables. You may have seen them in examples on the Internet but not quite understood how they are constructed. We're going to show you how to find the building blocks of a variable and construct one. Once you get to understand them it is easy to apply it in everyday situations.
The $ sign system variables will work in conjunction with the SQL query generator within the SAP client. When writing formatted searches to customize the behavior of SAP Business One they are extremely powerful, especially on the marketing document level.
Constructing System Variables
A SAP Business One system variable looks like this:
It is important to learn the syntax, $[$Item.Column.DataType]
The item designates which form data matrix you are working with in the SAP client. Column stands for the field in the table and type is a special designator related to which data type we are working with.
It is very easy to find these values to construct the variable in most cases. You simply turn on System Information on the SAP Business One view menu. A keyboard shortcut will also turn these fields on by hitting CTRL+Shift+U.
Once you have enabled this, hover over any field or label in the Business One client. You will see something like this across the bottom status bar of the client:
Using the system variable syntax you know that the variable can be constructed as:
You may also utilize database table names and field names when constructing form variables. In these variables you would leave off the second dollar sign ($) and data type. They are constructed like this:
One last thing to know about constructing $ system variables is that sometimes the "Item" in the syntax will need a negative sign in front of it.
$[$-4.0.0] is telling your code is that the system variable relates to a user defined field on the header of your document. The main form will be positive and the UDF form will be negative.
System Variable Data Types
The last number in the variable stands for data type. The data type part has a set of available options:
0 = string
1 = number
2 = currency
3 = date
When the system information does not give a column value this means the column is probably a string. Instead of using the numbers for data type it's easier to use their name such as
Using System Variables
A practical example is seeing how these form variables can be used is in a formatted search. When customizing SAP Business One to make it work for your company you will be employing many of these examples. This code snip is a SQL formatted search query that will return the value being held in the business partner card code attached to a sales order.
Open up the query generator and paste the code into a query. Save the query.
Open up the sales order marketing document and add a customer. Place your cursor in the "Customer Ref. No." field and press CRTL - ALT - F2 to bring up the formatted search window. Choose the third radio button and select your saved query. Click the magnifying glass in the field or SHIFT - F2 and watch the customer's card code fill the field.
System variable are not magic but certainly can make you look like a magician. With a little practice constructing and using these customization techniques you can do some ingenious things with SAP Business One right out of the box with no expensive consulting help.