Working with User-Defined Values (Formatted Searches) in SAP Business One for HANA

Modified on Tue, 17 Jun at 7:33 PM

This article explains the syntax differences and best practices when using User-Defined Values (UDVs)—also known as Formatted Searches (FMS)—in SAP Business One, version for SAP HANA. While most FMS syntax from the SQL Server version remains applicable, some important adjustments are required for HANA compatibility.

Basic Syntax Overview

Common syntax used in both SQL Server and SAP HANA environments:

$[TableName.FieldName]
$[$Field_Item_ID.0]
$[$Field_Item_ID.Column_ID.0]
These must be used with FROM DUMMY in HANA

Key Differences Between SQL Server and SAP HANA

Aspect	SQL Server	SAP HANA
Case Sensitivity	Not case-sensitive	Case-sensitive, use `""` for mixed case
`FROM` clause requirement	Optional	Required → must use `FROM DUMMY`
Date Function	`DATEADD(day, n, date)`	`ADD_DAYS(TO_DATE(date, format), n)`
String Concatenation	`+`	`

Scenario 1: Copy Posting Date to Delivery Date

Goal:

Copy the Posting Date (DocDate) to the Delivery Date field in a Sales Order.

SQL Server:

SELECT $[ORDR.DocDate]

SAP HANA:

SELECT $[ORDR."DocDate"] FROM DUMMY

Notes:

Field names like DocDate must be enclosed in double quotes.
Table names in uppercase (e.g., ORDR) do not require quotes.
Without FROM DUMMY, the query will fail in HANA.

Incorrect in SAP HANA:

SELECT $[“ORDR”."DocDate"] FROM DUMMY

This returns: "Internal error - 1110"

Scenario 2: Add 7 Days to Posting Date

Goal:

Set Delivery Date as Posting Date + 7 days.

SQL Server:
```
SELECT DATEADD(DD, 7, $[ORDR.DocDate])
```

SAP HANA:

SELECT ADD_DAYS(TO_DATE($[ORDR."DocDate"], 'mm/dd/yyyy'), 7) FROM DUMMY

Function Comparison:

HANA Function	SQL Server Equivalent	Example (HANA)	Example (SQL Server)
`ADD_DAYS(d, n)`	`DATEADD(day, n, date)`	`ADD_DAYS('2023-01-01', 7)`	`DATEADD(day, 7, '2023-01-01')`
`TO_DATE(expr, format)`	`CAST(expr AS date)` or `CONVERT()`	`TO_DATE('20230730', 'yyyymmdd')`	`CAST('20230730' AS date)`

Syntax Using Field Item ID

Format:

SELECT $[$Field_Item_ID.0] FROM DUMMY

Example: Copy Posting Date by Field ID

If the Posting Date field’s UID is 10:

SQL Server:
```
SELECT $[$10.0]
```
SAP HANA:
```
SELECT $[$10.0] FROM DUMMY
```

To add 7 days:

SQL Server:
```
SELECT DATEADD(DD, 7, $[$10.0])
```

SAP HANA:

SELECT ADD_DAYS(TO_DATE($[$10.0], 'mm/dd/yyyy'), 7) FROM DUMMY

Use View → System Information to identify Field Item IDs in SAP B1 UI.

Syntax Using Matrix Field Item and Column ID

Format:

SELECT $[$Item_ID.Column_ID.NUMBER|CURRENCY|DATE|0] FROM DUMMY

Data Type Options:

NUMBER: Extract amount only
CURRENCY: Extract currency code
DATE: Use date for calculations
0: Get value as string (default)

Scenario 3: Concatenate Item Code and Description into a UDF

Goal:

Populate a user-defined field U_ItemFullName in the Sales Order row with:

Example:

Item Code: A00001
Item Description: IBM Infoprint 1312
Result: A00001-IBM Infoprint 1312

Field Mapping:

Field	Matrix UID	Column UID
Item Code	38	1
Item Description	38	3

Queries:

SQL Server:
```
SELECT $[$38.1.0] + '-' + $[$38.3.0]
```

SAP HANA:

SELECT $[$38.1.0] || '-' || $[$38.3.0] FROM DUMMY

In HANA, use || for string concatenation.