List of items form - how to do substring search ?

[former_member1015145]

Let's say I have one item in the OITM with the ItemName field equal to

"The quick brown fox jumped over the lazy dog".

and the ItemCode equal to "ABC"

The default behavior is that if I search for "AB" in the ItemCode, the "List of Items" form will show that item.

If I search for "BC" in the ItemCode, the "List of Items" form will be empty.

Furthermore, if I search for "The quick" in the ItemName, the item shows up in the "List of Items" form.

However, if I search for "brown fox", nothing shows up.

I know this sounds trivial, but how do I override search parameters that get passed onto the "List of Items" form?

What I'd like to do is to be able to enter a value of "brown fox lazy dog" in the ItemName in the Item Master form, hit tab, and have the "List of Items" form pull up the item by doing a substring search on ItemName, such as this:

WHERE ItemName LIKE '%brown%' AND ItemName LIKE '%fox%' AND ItemName LIKE '%lazy%' AND ItemName LIKE '%dog%'.

I have tried to do trigger an ItemEvent via a FORM_LOAD with just the word "brown":

            If pVal.EventType = SAPbouiCOM.BoEventTypes.et_FORM_LOAD AND pVal.FormType = 10003 Then
                ds = f.DataSources.DBDataSources.Item("OITM")
                conditions = SBO_Application.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_Conditions)
                cond = conditions.Add()
                cond.Alias = "ItemName"
                cond.Operation = SAPbouiCOM.BoConditionOperation.co_CONTAIN
                cond.CondVal = "brown"
                ds.Query(conditions)
            End If

That however, returns an error.

[Former Member · Accepted solution]

What is the error you get?

A common workaround I've seen in this situation is to write an FMS, that works like you have suggested. Then assign it to the field. When the user triggers the CFL, catch the event, set bubbleevent = false, and then use Application.SendKeys("^F2") to trigger the FMS.

You could even use the DI to change the query depending on the number of terms the enter.

It's a complicated workaround, so lets first look at what errors you receive before you start looking into using it.

[former_member1015145 · Accepted solution]

Thanks for you help.

The idea is to catch the List of Items event FORM_LOAD before it is loaded to query with custom conditions mentioned above.

When the code above is executed, the error is:

Add-on 9000151 failed with exception; Event Type: 16

Forgot to mention, f in the example code above is the form retrieved based on pVal.FormType and pVal.FormTypeCount, which in this case, returns the List of Items Form.

Edited by: Dung Nguyen on Jul 6, 2011 6:22 PM

[Former Member · Accepted solution]

OK, I think that it is because you are trying to query the datasource directly, which generally doesnt work on system forms. I would not invest any more time in this approach.

Instead, try to catch the "ChooseFromList" event on beforeaction = true, and change it's conditions using the conditions collection. (I am afraid you will have to test this to see if it is allowed on a system form - I think it is, but I'm not sure)

If neither of these approaches work, I think you will need to implement a workaround similar to what I mentioned.

[former_member1015145 · Accepted solution]

Okay, thanks for your help again. I will try what you've recommended.