on 06-21-2010 11:31 AM
Hello All,
I have created a user defined field in Sales Order Form named ItemDetail )
I want to allocate a formatted search in that field with query as follows :-
Select ItemName From OITM Where ItemCode Like %$[RDR1.ItemCode]
Now i know how to assign a Formatted search from the Front end screen .
But i want to assign the Formated Search through SDK.
How to code the Formatted Search in SAP using SDK in SAP 8.8
Thanks & Regards,
Amit
I wrote some code a long time ago to do what you are asking. I needed to use the SDK to create multiple formatted searches and each of them to several fields. So I wrote some generic subroutines that are probably alot more complicated than you need but I'm posting the code. Hopefully you will be able to dicipher it.
I'm going to put the code in a second post because it is not formatting correctly here.
Edited by: John Chadwick on Jun 21, 2010 1:37 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Private Structure SearchParams
Dim sql As String
Dim QueryName As String
Dim CatCode As String
Dim FormID As String
Dim ItemID As String
Dim QueryID As Long
Dim HDRTable As String
'RowParams
Dim RowTable As String
Dim ColumnName As String
'
End Structure
Dim CCS as SearchParams
With CCS
.CatCode = catcode
.FormID = "139"
.ItemID = "38"
.HDRTable = "ORDR"
.RowTable = "RDR1"
.sql = "Select name FROM OCPR where cardcode = $[&HDRTABLE.cardcode] and cntctcode = "
.QueryName = "XXD SO End User Contact Name"
.ColumnName = "U_XXDENDUS"
End With
CreateContactNameSearches(CCS)
Private Sub CreateContactNameSearches(ByVal zSP As SearchParams, Optional ByVal zItemIsMatrix As Boolean = True)
Dim fieldname As String
Dim sqlstmt As String
Try
With zSP
If zItemIsMatrix Then
fieldname = .ColumnName & "ID"
Else
fieldname = .ItemID & "ID"
End If
sqlstmt = Replace(.sql, "&HDRTABLE", .HDRTable)
sqlstmt &= "$[" & .RowTable & "." & fieldname & "]"
.QueryID = CreateUserQry(.QueryName, sqlstmt, .CatCode)
'then connect the FS to forms/fields needed
If .QueryID <> -1 Then
AddFormattedSearch(.FormID, .ItemID, .ColumnName, BoFormattedSearchActionEnum.bofsaQuery, .QueryID, BoYesNoEnum.tNO, BoYesNoEnum.tYES, BoYesNoEnum.tYES, fieldname)
End If
End With
Catch ex As Exception
B1App.StatusBar.SetText(ex.Message, , BoStatusBarMessageType.smt_Warning)
End Try
End Sub
Public Function CreateUserQry(ByVal zName As String, ByVal zSQL As String, ByVal zCategory As Long) As Long
Dim rs As Recordset = B1Comp.GetBusinessObject(BoObjectTypes.BoRecordset)
Dim qryID As Long
Dim qryIDA As String 'stupid B1 error is sending some bad characters at end of field
rs.DoQuery("select intrnalkey from ouqr where qname = '" & zName & "'")
If Not rs.EoF Then
qryID = rs.Fields.Item("intrnalkey").Value
Else
Dim uq As UserQueries = B1Comp.GetBusinessObject(BoObjectTypes.oUserQueries)
uq.QueryDescription = zName
uq.Query = zSQL
uq.QueryCategory = zCategory
If uq.Add = 0 Then
Try
qryIDA = B1Comp.GetNewObjectKey()
For x As Short = qryIDA.Length To 1 Step -1
If IsNumeric(qryIDA) Then
qryID = qryIDA
Exit For
Else
qryIDA = qryIDA.Substring(0, qryIDA.Length - 1)
End If
Next
Catch ex As System.Runtime.InteropServices.COMException
Catch ex2 As Exception
'B1App.MessageBox(ex2.ToString)
End Try
Else
qryID = -1
End If
End If
Return qryID
End Function
Public Function AddFormattedSearch(ByVal zFormID As String, _
ByVal zItemID As String, _
ByVal zTargetColumn As String, _
ByVal zAction As BoFormattedSearchActionEnum, _
ByVal zQueryID As Long, _
ByVal zByField As BoYesNoEnum, _
ByVal zRefresh As BoYesNoEnum, _
ByVal zForceRefresh As BoYesNoEnum, _
Optional ByVal zFieldName As String = "") As Long
Dim fs As FormattedSearches = B1Comp.GetBusinessObject(BoObjectTypes.oFormattedSearches)
fs.FormID = zFormID
fs.ItemID = zItemID
fs.ColumnID = zTargetColumn
fs.Action = zAction
fs.QueryID = zQueryID
fs.FieldID = zFieldName
fs.ForceRefresh = zForceRefresh
fs.Refresh = zRefresh
fs.ByField = zByField
If fs.Add = 0 Then
Return B1Comp.GetNewObjectKey
Else
'B1App.MessageBox(B1Comp.GetLastErrorDescription)
Return -1
End If
End Function
Hello John,
Thanks for your solution .
But i am not able to understand it. Though i have written a code for Formatted Search which is successfully creating the User queries but is unable to assign
Formatted Search to the Matrix Column Field named " U_OrderNo"
Code which i have written follows :-
Dim oRs As SAPbobsCOM.Recordset
oRs = ocompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
Dim oQuery As SAPbobsCOM.UserQueries =
ocompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserQueries)
oQuery.Query = "SELECT T0.ItemCode, T0.ItemName FROM OITM T0" 'Query
oQuery.QueryCategory = -1
oQuery.QueryDescription = "Order"
oQuery.Add()
Dim oFormatted As SAPbobsCOM.FormattedSearches =
ocompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oFormattedSearches)
oFormatted.FormID = "139"
oFormatted.ItemID = "38"
oFormatted.Action = SAPbobsCOM.BoFormattedSearchActionEnum.bofsaQuery
'oFormatted.FieldID = "38" 'ItemCode
oFormatted.ColumnID = "U_OrderNo"
oRs.DoQuery("select IntrnalKey from OUQR(nolock) Where QName = 'Order'")
'
oFormatted.QueryID = oRs.Fields.Item("IntrnalKey").Value ''"" ' oRs.GetField
oFormatted.Refresh = SAPbobsCOM.BoYesNoEnum.tNO
oFormatted.ForceRefresh = SAPbobsCOM.BoYesNoEnum.tNO
oFormatted.ByField = SAPbobsCOM.BoYesNoEnum.tNO
If oFormatted.Add() = 0 Then ' 0 Then
msgbox(ex.message)
End If
Please suggest which line i have to change for assigning the Formatted search to the column field
Thanks & Regards
Amit
What is the error message that you get when you try to add the formatted search?
Also, check to make sure that your field name (U_OrderNo) for the oclumnID matches the case exactly of the field as defined in the table. SOMETIMES B1 is very picky about case.
Edited by: John Chadwick on Jun 22, 2010 9:58 AM
Hello John,
The error which i am getting is when the following line executes :-
If oFormatted.Add() = 0 ThenThen
msgbox(ex.message)
End If
Error Message "This Entry Already Exists"
The User Query gets created but the Formatted Search is not getting assigned in the column of matrix .
U_OrderNo is the correct field no case mismatch is there..
Though the code is not assigning the Formatted Search in the column.
Thanks & Regards,
Amit
You said "The User Query gets created but the Formatted Search is not getting assigned in the column of matrix". Have you checked the CSHS table? That's where the links between the form/column gets made to the query you want to run. The message makes it sound like the record is there already. If it is I think you should compare that record to values on other records that have queries assigned to matrix fields to see what is different on the record you created. If you don't have a similar record, then try adding the formatted search to another column in the matrix using the client. Once you have it working that way you can compare the data between your auto-created record and the manually created one.
If the record is not in CSHS, then I can't imagine why you are getting the message that it already exists.
Also, am I reading your code right?
If oFormatted.Add() = 0 Then ' 0 Then
You are sending a message when the add is successful? And where does ex come from? Did you omit a try block in what you posted?
Edited by: John Chadwick on Jun 23, 2010 1:32 PM
Hi,
FYI
Formatted Search can be defined also via SAP B1 Gui.
Steps:
1. Create a Custom Query you would like
2. open your form, go to the required field and press SHIFT+F2
3. Link your query to the field. (no refresh nesessary)
4. Go to your addon, and when you press the TAB key on the required field (ET_KEY_DOWN event) activate menuitem SHIFT+F2 (menu id: 7425)
User | Count |
---|---|
99 | |
12 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.