SAP Business One How to Specify an On-Screen Field in a Formatted Search SQL Statement

When referencing an on-screen field in an SQL statement, specific syntax is required and unfortunately the only way that the syntax can be validated is by testing the formatted search. The error messages that result are not very helpful either.

Use the instructions in this document to build these references to on screen fields.

Turn on the system information display via the menu path View > System Information

Hover over the desired field and look at the displayed system information in the lower left-hand corner of the screen. The information should look similar to the screen shot below:

The important pieces of information are found immediately after the “Item=” and the “Column=”

In this example, the Item designation is 178 and the column designation is 12.

When referring to this field in the query statements use the syntax:

$[$Field‘s Item Number. Field‘s Column Number.0]

So in the example we would have $[$178.12.0]

NOTE: When the field in in the header portion of the screen then the Column information will not be displayed. In this case, use a value of o (zero) where the column number would appear.

NOTE: there appear to be some cases where this syntax is modified slightly by adding a minus sign immediately in front of the Item information. So in our example the way that the tax code field would be referred to would be:

$[$-178.12.0]
- written by Joe Stevenson

Thanks to Ed Monk of SBONotes.com

This entry was posted on Wednesday, February 11th, 2009 at 11:10 am and is filed under Customization. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.