Formatted Search Through SDK

[Former Member]

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

[Former Member]

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

[Former Member]

        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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

Did you find a solution?

[CRVMANISH]

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)