The Item Syntax

Previous  Next |  Direct link to this topic

This is the most common syntax and allow you to inject a value of an SAP Window item into SQL and arguments. The syntax is as follows:

$[$<ItemUID>.<ColumnUID>.<DataType>]

Where <ItemUID>, <ColumnUID> and <DataType> is the 3 things you need to provide.

Let’s look at a sample:

clip0140

Here we have the Sales order were we have pointed out 6 fields that we will look at. They all represent the different datatypes we will encounter in SAP Business One

1.String values (Customer Name)

2.Integer values (Document Number)

3.Dates (Posting Date)

4.Data on line level (Price After Discount)

5.Decimal values (Discount)

6.Decimal values mixed with currency codes (Document Total)

We will now look at each and determine how we translate into the dynamic syntax.

 

String values (Customer Name)

Its item UID is 54 and the type is a string.

It is not on line level so there are no columnUid involved (we indicate this by setting it to 0)

The string data type in the syntax is either shown by to enter STRING or the more popular 0 (because it is faster to write).

With this information our dynamic value will be:

clip0141

= $[$54.0.STRING]

= $[$54.0.0]

If we used this in an SQL-sentence:

SELECT $[$54.0.0] = SELECT 'Parameter Technology'

 


Integer values (Document Number)

Its item UID is 8 and the type is a integer.

It is not on line level so there are no columnUid involved (we indicate this by setting it to 0)

The integer data type in the syntax is shown by to entering NUMBER.

With this information our dynamic value will be:

clip0142

= $[$8.0.NUMBER]

 

If we used this in an SQL-sentence:

SELECT $[$8.0.NUMBER] = SELECT 13

 


 

Dates (Posting Date)

Its item UID is 10 and the type is a date.

It is not on line level so there are no columnUid involved (we indicate this by setting it to 0)

The date data type in the syntax is shown by to entering DATE.

With this information our dynamic value will be:

clip0143

= $[$8.0.DATE]

 

If we used this in an SQL-sentence:

SELECT $[$8.0.DATE] = SELECT '2006-03-30'

(2006-03-30) is an invariant standard of dates, so you wish to compare dates please use this format.


 

Data on line level (Price After Discount)

Its item UID is 38 and the type is a mix of decimals and currency.

This is on line level so we also need to set the ColumnUid which is 17

The decimal/currency data type in the syntax is either shown by to entering NUMBER or CURRENCY.

With this information our dynamic value will be:

clip0144

= $[$38.17.NUMBER]
= $[$38.17.CURRENCY]

If we used this in an SQL-sentence:

SELECT $[$38.17.NUMBER] = SELECT 4200.00

 


Decimal values (Discount)

Its item UID is 24 and the type is a decimal.

It is not on line level so there are no columnUid involved (we indicate this by setting it to 0)

The decimal data type in the syntax is shown by to entering NUMBER or CURRENCY.

With this information our dynamic value will be:

clip0145

= $[$24.0.NUMBER]
= $[$24.0.CURRENCY]

If we used this in an SQL-sentence:

SELECT $[$24.0.NUMBER] = SELECT 10.500

 


 

Decimal values mixed with currency codes (Document Total)

Its item UID is 29 and the type is a mix of decimals and currency.

It is not on line level so there are no columnUid involved (we indicate this by setting it to 0)

The decimal/currency data type in the syntax is either shown by to entering NUMBER or CURRENCY.

With this information our dynamic value will be:

clip0146

= $[$29.0.NUMBER]
= $[$29.0.CURRENCY]

If we used this in an SQL-sentence:

SELECT $[$29.0.NUMBER] = SELECT 2173.18

 


 

Extensions to dynamic item syntax

Other than the normal $[$item.col.type] B1UP add a few additional features to the syntax

Feature

Description

.row extension

This syntax extend .row (aka $[$item.col.type.row]) so you can target a specific row. You can write a specific row number or the keywords .FIRST, .LAST, .LAST-1, .SELECTED and .FOCUSED

For more on this syntax please see the following video: https://www.youtube.com/watch?v=sQgN-qb-hvg

ROWSHEADER

If you need to target a row header of a matrix it is done like this:

clip0147

$[$<MatrixItemUid>.ROWSHEADER.0]