SBOhub Search

Constructing SAP Business One Form System Variables

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: $[$38.1.0]

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.

View Menu Showing System Information
View Menu Showing System Information

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:

System Information
System Information

Using the system variable syntax you know that the variable can be constructed as:
$[$38.1.0]

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: $[ORDR.CardCode]

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.

What $[$-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 $[$22.0.number]

Using System Variables

Query with System Variable
Query with System Variable

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.

SELECT $[$4.0.0]

Open up the query generator and paste the code into a query. Save the query.

Setting up the Example
Setting up the Example

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.

Author Info

Peter Canale
Peter Canale Follow Connect

Co-founder of SBOhub, starter, experienced executive, and probably having the best day ever.

Discussion

  • Pingback: Pivot Data » Summer Projects()

  • NMAC

    HI Peter, Great information. I am wondering about how one can reference the value of a UDF on a Marketing Document in SAP BusinessOne. For example, if I am writing an Approval, based on a query to trigger every time and agent enters an order without filling out a required UDF, how could I reference the UDF in a query in such a way to trigger the Query and thus the Approval? Any insight would be greatly appreciated.

  • kasandra

    I was wondering if you could help me. On all my forms (purchase orders, sales orders, quotes, ect.) in the QTY its has 1.0 I am trying to get rid of the .0 and just a single digit QTY.

  • opluda

    Hello Peter,
    My business case is to capture the value of system variables on MULTIPLE lines of a document, example: how can I get the value of the GL Account code in a Purchase order for lines 1,2,3 etc.
    Currently the “$[$Item.Column.DataType]” format retrieves only the first line of a document.
    You advise here would be VERY VERY appreciated.
    Thanks,
    Oded