Choose From List and Selection Criteria

[leon_laikan]

Hi

I want to put a CFL in a form. This CFL will pick up values from OPRJ (SAP B1 Table: Projects , Object Type = 63)

I adapted the code available from the SDK Sample 17.Choose From List

--------

Private Sub AddChooseFromList()

        Try

            Dim oCFLs As SAPbouiCOM.ChooseFromListCollection

            oCFLs = oForm.ChooseFromLists

            Dim oCFL As SAPbouiCOM.ChooseFromList

            Dim oCFLCreationParams As SAPbouiCOM.ChooseFromListCreationParams

            oCFLCreationParams = SBO_Application.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_ChooseFromListCreationParams)

            ' Adding 2 CFL, one for the button and one for the edit text.

            oCFLCreationParams.MultiSelection = False

            oCFLCreationParams.ObjectType = "63"

            oCFLCreationParams.UniqueID = "CFL1"

            oCFL = oCFLs.Add(oCFLCreationParams)

            oCFLCreationParams.UniqueID = "CFL2"

            oCFL = oCFLs.Add(oCFLCreationParams)

        Catch

            MsgBox(Err.Description)

        End Try

    End Sub

-------------

The code works fine, but unfortunately displays ALL records from OPRJ. I want a shorter list to be displayed.

The SQL for this reduced list is as follows:

SELECT DISTINCT T1. PrjCode, T1.PrjName from RDR1 T0

LEFT JOIN OPRJ T1 on T0.Project = T1.PrjCode

WHERE DateDiff(mm,T0.DocDate,GetDate())<9

ORDER BY T1.PrjCode DESC  

-------

Can you give me an idea how I can modify my code?

Or should I use a better method?

I also do not wish to use the method given in the sample because it displays the Projects window and allows unauthorized staff to add new projects.

I would be happier if the CFL could be an "ordinary" CFL, i.e a simple listing only.

Thanks

Leon Lai

[pedro_magueija · Accepted solution]

Hi Leon,

you can use the Conditions and Condition object to "introduce" constraints to the ChooseFromList.

Public Sub AddingConditions()

    Dim oConditions As SAPbouiCOM.Conditions
    Dim oCondition As SAPbouiCOM.Condition

    '// create a new conditions collection
    Set oConditions = New SAPbouiCOM.Conditions

    '// the requested conditions
    '// WHERE ((CardType = 'C') Or (CardType = 'S'))

    '// the conditions collection encapsulates the WHERE clause
    '// of a select statement

    Set oCondition = oConditions.Add
    '// ((CardType = 'C') Or
    oCondition.BracketOpenNum = 2
    oCondition.Alias = "CardType"
    oCondition.Operation = co_EQUAL
    oCondition.CondVal = "C"
    oCondition.BracketCloseNum = 1
    oCondition.Relationship = cr_OR

    Set oCondition = oConditions.Add
    '// (CardType = 'S'))
    oCondition.BracketOpenNum = 1
    oCondition.Alias = "CardType"
    oCondition.Operation = co_EQUAL
    oCondition.CondVal = "S"
    oCondition.BracketCloseNum = 2

End Sub

---

Best regards,

Pedro Magueija

[leon_laikan · Accepted solution]

Hi Pedro,

Thanks a lot for your reply.

I am trying your suggestion. For the moment, I would like to add a few comments.

(a) The code from SDK Sample gives the following screen. It has a New button, which I do not really want, because I don't want unauthorized staff to create new projects in Project Setup.

I would prefer a simple CFL list.

(b) My query involves joining 2 tables: OPRJ and RDR1. How can I deal with this?

(c) Is it better for me to use a CFL or a COMBO BOX?

Best Regards

Leon Lai

[pedro_magueija · Accepted solution]

Hi Leon,

Regarding a) you can setup Authorizations under System Initialization as read-only for Projects. This way they won't be able to create new projects.

Regarding b) the choose from list conditions will use the properties of the Projects, so you'd perhaps have to find which projects are valid (using your query) then create the conditions to display only those (ProjectCode = <the_allowed_values>)

Regarding c) this seems a good option, you can use your query to get the valid values and set them up. No need to worry about authorizations. However if there are many projects you may have a small performance price to pay (filling up the combobox and clearing it).

Good luck.

---

Best regards,

Pedro Magueija

[leon_laikan · Accepted solution]

Hi Pedro,

Thanks a lot.

Your points are very valid. I'll resume my project on Monday, and will let you know if I have any problems.

Best Regards

Leon Lai