RE:Formatted Search - Programmatically

[Former Member]

Hi All,

How to add Formatted search to a particular EditText through the program.

My requirement is :

I have 3 userdefined field U_CardCode,U_CardName and U_ContactPerson.I want to add a FS to U_CardCode.If i select a BP from the list i should load the corresponding contactperson and BPName in the U_CardName and U_ContactPerson field.

How should i achieve this.

Thanx in advance

Mohana

[Former Member · Accepted solution]

Mohana,

I use this function:

Public Function AssignFormattedSearch(ByRef oCompany As SAPbobsCOM.Company, ByRef oApplication As SAPbouiCOM.Application, _
            ByRef QueryName As String, ByRef TheQuery As String, ByRef FormID As String, ByRef ItemID As String, ByRef QueryID as Integer, Optional ByRef ColID As String = "-1") As Boolean
        AssignFormattedSearch = False

        Dim oRS As SAPbobsCOM.Recordset = oCompany.GetBusinessObject(BoObjectTypes.BoRecordset)
        Dim oFS As SAPbobsCOM.FormattedSearches = oCompany.GetBusinessObject(BoObjectTypes.oFormattedSearches)

        Try
            oRS.DoQuery("SELECT TOP 1 T0.IndexID FROM [dbo].[CSHS] T0 WHERE T0.FormID='" & FormID & "' AND T0.ItemId='" & _
            ItemID & "' AND T0.ColID='" & ColID & "' AND T0.QueryID=" & QueryID)
            If oRS.RecordCount = 0 Then
                QueryID = CreateQuery(oCompany, oApplication, QueryName, TheQuery)
                oFS.Action = BoFormattedSearchActionEnum.bofsaQuery
                oFS.FormID = FormID
                oFS.ItemID = ItemID
                oFS.ColumnID = ColID
                oFS.QueryID = QueryID
                oFS.ByField = BoYesNoEnum.tNO
                Dim lRetCode As Long = oFS.Add
                If lRetCode = -2035 Then
                    oRS.DoQuery("SELECT TOP 1 T0.IndexID FROM [dbo].[CSHS] T0 WHERE T0.FormID='" & FormID & _
                            "' AND T0.ItemId='" & ItemID & "' AND T0.ColID='" & ColID & "'")
                    If oRS.RecordCount > 0 Then
                        oFS.GetByKey(oRS.Fields.Item(0).Value)
                        oFS.Action = BoFormattedSearchActionEnum.bofsaQuery
                        oFS.FormID = FormID
                        oFS.ItemID = ItemID
                        oFS.ColumnID = ColID
                        oFS.QueryID = QueryID
                        oFS.ByField = BoYesNoEnum.tNO
                        lRetCode = oFS.Update
                    End If
                End If
                If lRetCode <> 0 Then
                    oApplication.MessageBox("Error while assigning formatted search")
                    Exit Function
                End If
            End If

            AssignFormattedSearch = True
        Catch ex As Exception
            oApplication.MessageBox("AssignFormattedSearch(): " & ex.Message)
        Finally
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS)
            oRS = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oFS)
            oFS = Nothing
            GC.Collect()
        End Try
    End Function

To get the QueryID I use the following code

Public Function GetQueryID(ByRef oCompany As SAPbobsCOM.Company, ByRef oApplication As SAPbouiCOM.Application, _
            ByRef CategoryID As Integer, ByRef QueryName As String) As Integer
        GetQueryID = 0
        Dim oRS As SAPbobsCOM.Recordset = oCompany.GetBusinessObject(BoObjectTypes.BoRecordset)

        Try
            oRS.DoQuery("SELECT TOP 1 INTRNALKEY FROM OUQR WHERE QCATEGORY=" & CategoryID & " AND QNAME='" & TranslateStr(oApplication, QueryName) & "'")
            If oRS.RecordCount > 0 Then
                GetQueryID = oRS.Fields.Item(0).Value
            End If
        Catch ex As Exception
            oApplication.MessageBox("GetQueryID(Cat: " & CategoryID & ", Query: " & QueryName & vbCrLf & _
            TranslateStr(oApplication, Error_) & oCompany.GetLastErrorCode.ToString & ", " & ex.Message)
        Finally
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS)
            oRS = Nothing
            GC.Collect()
        End Try
    End Function

Public Function GetQrtCatID(ByRef oCompany As SAPbobsCOM.Company, ByRef oApplication As SAPbouiCOM.Application, _
            ByRef CategoryName As String) As Integer
        GetQrtCatID = -3
        Dim oRS As SAPbobsCOM.Recordset = oCompany.GetBusinessObject(BoObjectTypes.BoRecordset)

        Try
            oRS.DoQuery("SELECT TOP 1 CATEGORYID FROM OQCN WHERE CATNAME='" & TranslateStr(oApplication, CategoryName) & "'")
            If oRS.RecordCount > 0 Then GetQrtCatID = oRS.Fields.Item(0).Value
        Catch ex As Exception
            oApplication.MessageBox("GetQrtCatID(): " & CategoryName & vbCrLf & _
            TranslateStr(oApplication, Error_) & oCompany.GetLastErrorCode.ToString & ", " & ex.Message)
        Finally
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS)
            oRS = Nothing
            GC.Collect()
        End Try
    End Function

Regards,

Vítor Vieira

[Former Member · Accepted solution]

Hi,

When i copy and paste ur code, i got the error in createQuery and in

Translate_Str.What to do for this.

Thanx in advance

Mohana

[Former Member · Accepted solution]

Hi Mohana,

The TranslateStr(oApplication, STRING_ID) is a function I created to return a translated string.

I use it to retrieve error messages, query strings, category name, group names, etc.

In the case of CategoryName, in the add-on I'm working on, it will return "Laranja.One" if the UI is in Portuguese or "Orange.One" if the UI is in English.

Regards,

Vitor Vieira

[Former Member · Accepted solution]

Hi Vitor,

u have called the CreateQuery() function.In that function what coding did u wrote.Provide me the CreateQuery() function Coding for the further clarification.Thanx in advance.....:-)

Regards

Mohana

[Former Member · Accepted solution]

Hi Mohana,

As requested:

    Public Function CreateQuery(ByRef oCompany As SAPbobsCOM.Company, ByRef oApplication As SAPbouiCOM.Application, _
            ByRef QueryName As String, ByRef TheQuery As String) As Integer
        CreateQuery = -1
        Dim oRS As SAPbobsCOM.Recordset = oCompany.GetBusinessObject(BoObjectTypes.BoRecordset)
        Dim oQuery As SAPbobsCOM.UserQueries = oCompany.GetBusinessObject(BoObjectTypes.oUserQueries)

        Try
            oRS.DoQuery("SELECT TOP 1 INTRNALKEY FROM OUQR WHERE QCATEGORY=" & GetSysCatID(oCompany, oApplication) & _
                " AND QNAME='" & TranslateStr(oApplication, QueryName) & "'")
            If oRS.RecordCount > 0 Then
                CreateQuery = oRS.Fields.Item(0).Value
            Else
                oQuery.QueryCategory = GetSysCatID(oCompany, oApplication)
                oQuery.QueryDescription = TranslateStr(oApplication, QueryName)
                oQuery.Query = TranslateStr(oApplication, TheQuery)
                If oQuery.Add <> 0 Then
                    oApplication.MessageBox(TranslateStr(oApplication, CreateQueryError) & TranslateStr(oApplication, QueryName) & _
                    vbCrLf & TranslateStr(oApplication, Error_) & oCompany.GetLastErrorCode.ToString & ", " & oCompany.GetLastErrorDescription)
                    Exit Function
                End If
                CreateQuery = CInt(Mid$(oCompany.GetNewObjectKey, 1, InStr(1, oCompany.GetNewObjectKey, vbTab) - 1))
            End If
        Catch ex As Exception
            oApplication.MessageBox("CreateQuery(): " & QueryName & vbCrLf & _
            TranslateStr(oApplication, Error_) & oCompany.GetLastErrorCode.ToString & ", " & ex.Message)
        Finally
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS)
            oRS = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oQuery)
            oQuery = Nothing
            GC.Collect()
        End Try
    End Function

I also provide another function that returns the ID of a Query category with the same description as the Add-on's name.

    Public Function GetSysCatID(ByRef oCompany As SAPbobsCOM.Company, ByRef oApplication As SAPbouiCOM.Application) As Integer
        GetSysCatID = -3
        Dim oRS As SAPbobsCOM.Recordset = oCompany.GetBusinessObject(BoObjectTypes.BoRecordset)

        Try
            oRS.DoQuery("SELECT TOP 1 CATEGORYID FROM OQCN WHERE CATNAME='" & TranslateStr(oApplication, AddOnName) & "'")
            If oRS.RecordCount > 0 Then GetSysCatID = oRS.Fields.Item(0).Value
        Catch ex As Exception
            oApplication.MessageBox("GetQrtCatID(): " & AddOnName & vbCrLf & _
            TranslateStr(oApplication, Error_) & oCompany.GetLastErrorCode.ToString & ", " & ex.Message)
        Finally
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS)
            oRS = Nothing
            GC.Collect()
        End Try
    End Function

Regards,

Vítor Vieira

[Former Member]

ya it is solved.thanx vitor

[Former Member]

look at