SQL queries written for use by formatted searches can reference any field on the current SAP Business One form using a special syntax made up of position and field name as displayed in the system information bar of SAP Business One.
First, turn system information on, under the menu: View > System Information.
Please note that the syntax is different for header and grid records:
Header Record Syntax: $[$ITEM.0.0]
Grid Record Syntax: $[$ITEM.COLUMN.NUMBER]
The third value in the syntax must match the field type that it is referencing: Number, Currency, Date or 0 (text).
Note: The values are CASE SENSITIVE and must match exactly as written in the system information.
Examples
If you want to reference the field Line Type on the Quote/Budget tab of a job, the system information will show:
Form = Enprise_JCMaster Item = GridQuote Pane = 2 Column = QUOTELINETYPE Row = 1
To reference this field, it would look like this: $[$GridQuote.QUOTELINETYPE.0]
If you wanted to reference a user-defined field (UDF) that you have added to the header area of a job, the system information will show:
Form = Enprise_JCMaster Item = EdH2 Pane = 0 Variable = 499 #000002048,DSU01
To reference this field, it would look like this: $[$EdH2.0.0]
Select Examples
The script below will return the SUBJOBID from a direct material row:
SELECT $[$GridMain.Col11.0]
This script will insert the Job ID into any other field:
SELECT $[$EdJobID.0.0]
In Timesheet Entry, this script will lookup itemcode and itemcode details from the Quote based on the Subjob ID:
SELECT Stockcode, Description, Qty as Quoted
FROM ENPRISE_JOBCOST_JOBLINES
WHERE SUBJOBID = $[$GridStaff.SUBJOBID.0] AND LINETYPE = 'Q'
Comments
0 comments
Please sign in to leave a comment.