CFL Not showing Data

[Former Member]

DEAR ALL,

  I am trying to display Emp Name from OHEM . I am using below code in my project , But CFL is not display data. Code execute successfully , but it does not display ,

    I am also replace Alias name to EMPName , but it does not show data.

  Private Function BeforeCFL()

            Dim rsetCFL As SAPbobsCOM.Recordset = Nothing

            Try

                        Dim strQuery As String = String.Empty

                strQuery = "SELECT DISTINCT T0.[firstName] + '  ' +  T0.[lastName] as 'EmpName' FROM OHEM T0 WHERE T0.[position]  Is not null and ACTIVE='Y' "

                        Dim ocfl As SAPbouiCOM.ChooseFromList

                        Dim oCFL_collection As SAPbouiCOM.ChooseFromListCollection

                        Dim ocondition As SAPbouiCOM.Condition = Nothing

                        Dim oconditions As SAPbouiCOM.Conditions = Nothing

                        Dim oEmptyConds As New SAPbouiCOM.Conditions

                        oCFL_collection = oForm.ChooseFromLists

                ocfl = SBO_Application.Forms.ActiveForm.ChooseFromLists.Item("CFLOwner")

                        rsetCFL = oApplication.Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

                        ocfl.SetConditions(oconditions)

                        oconditions = ocfl.GetConditions

                        rsetCFL.DoQuery(strQuery)

                        rsetCFL.MoveFirst()

                        For i As Integer = 1 To rsetCFL.RecordCount

                            If i = (rsetCFL.RecordCount) Then

                                ocondition = oconditions.Add()

                        ocondition.Alias = "lastName"

                                ocondition.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL

                                ocondition.CondVal = rsetCFL.Fields.Item(0).Value

                            Else

                                ocondition = oconditions.Add()

                        ocondition.Alias = "lastName"

                                ocondition.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL

                                ocondition.CondVal = rsetCFL.Fields.Item(0).Value

                                ocondition.Relationship = SAPbouiCOM.BoConditionRelationship.cr_OR

                            End If

                            rsetCFL.MoveNext()

                        Next

                        If rsetCFL.RecordCount = 0 Then

                            ocondition = oconditions.Add()

                    ocondition.Alias = "lastName"

                            ocondition.Relationship = SAPbouiCOM.BoConditionRelationship.cr_NONE

                            ocondition.CondVal = "-1"

                        End If

                        ocfl.SetConditions(oconditions)

            Catch ex As Exception

            Finally

                System.Runtime.InteropServices.Marshal.ReleaseComObject(rsetCFL)

            End Try

            Return True

        End Function

How to solve this issue?

[Former Member]

Hi Lakshmi,

The example below works for Profit Centers. I have placed it in a function, and i'm calling it on form load.

The philosophy is to create a where clause like "MSAccess" eg. WHERE 1=1 AND ((FIELD = 'VAL1') OR (FIELD = 'VAL2') OR (FIELD = 'VAL3'))

sSQL = "SELECT  U_Value" + Environment.NewLine +

                       " FROM [@SETTINGS_TABLE]" + Environment.NewLine +

                       " WHERE U_Setting = N'FILTER_VALUE'";

                    //My library that returns data from the database. (eg recordset)

                    string[] CCs = CommonLibrary.Functions.Database.ReturnDBValues(sSQL, "U_Value").ToString().Split(',');

                    SAPbouiCOM.DBDataSource oDBDataSource = this.oForm.DataSources.DBDataSources.Add("OOCR");  

                    SAPbouiCOM.Condition _oCondition_acct;

                    cflChildCC = this.oForm.ChooseFromLists.Item("cfCC");

                    cflChildCC.SetConditions(new SAPbouiCOM.Conditions());

                    SAPbouiCOM.Conditions _oConds_acct = cflChildCC.GetConditions();

                    _oCondition_acct = _oConds_acct.Add();

                    _oCondition_acct.BracketOpenNum = 1;

                    int i = 1;

                    foreach (string sCC in CCs)

                    {

                        _oCondition_acct.BracketOpenNum = 1;

                        _oCondition_acct.Alias = "OcrCode";

                        _oCondition_acct.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL;

                        _oCondition_acct.CondVal = sCC;

                        _oCondition_acct.BracketCloseNum = 1;

                        i++;

                        if (i <= CCs.Length)

                        {

                            _oCondition_acct.Relationship = BoConditionRelationship.cr_OR;

                            _oCondition_acct = _oConds_acct.Add();

                        }

                    }

                    _oCondition_acct.BracketCloseNum = 1;

                    cflChildCC.SetConditions(_oConds_acct);

                    oDBDataSource.Query(_oConds_acct);

Good Luck!

Evanggelos D. Plagianos