Formatted Search screen - populate multiple fields

[Former Member]

I have an Employee Id field in my screen.

On clicking Choose button,I use a Formatted Search screen to populate value to that field.

I also have other fields like employee Name,department which should be populated, once an employee Id is chosen from Formatted Search screen.

The functionality is like that of "Purchase Order screen".

Once a vendor Id is chosen Name,Contact Person etc get populated in "Purchase Order screen".

How do I do that?

Should I trap the Choose button event of Formatted Search screen?

Please help.

[Former Member]

You would be better just using the VALIDATE event on the original item, that way the same code will also work when a user types in a value rather than uses the formatted search.

You will probably need to manually shift the focus to a different field after returning from the formatted search to ensure the validate event is fired.

Regards,

John.

[Former Member]

Hi,

Thanks i will try as you mentioned.

I need to know one more thing. I use a formatted search/

user defined query in my employee id field

I call ActivateMenu on the click event of choose button.

SBO_Application.ActivateMenuItem("7426")

When I click the choose button first time,a pop window comes up. I choose a User defined query(Employee) to be associated with the employee id field

Instead of expliciting associating the query like this, Can we do the same in the code?

That is I want to create the user defined query and assign it to the lookup directly through code, without asking user to assign a user defined query while executing.

[Former Member]

hi,

Validate event is not getting fired when I return from the formatted search

to THE specified field.

I am not able to put values in other fields because of this

Please help.

[Former Member]

As I mentioned, you will need to shift the focus manually on return from the formatted search to force the validate event to take place.

You can shift the focus using the Click method on another field.

I use the Form_Activate event to detect when control returns from the formatted search, and issue a click onto the next field in the matrix.

John.

[Former Member]

John could you help me out regarding this.

I need to know one more thing. I use a formatted search/

user defined query in my employee id field

I call ActivateMenu on the click event of choose button.

SBO_Application.ActivateMenuItem("7426")

When I click the choose button first time,a pop window comes up. I choose a User defined query(Employee) to be associated with the employee id field

Instead of expliciting associating the query like this, Can we do the same in the code?

That is I want to create the user defined query and assign it to the lookup directly through code, without asking user to assign a user defined query while executing.

[Former Member]

I haven't seen any objects in the DI that would let you do this, and doing direct inserts to the database tables would be against the rules.

You could look at using BC sets for distributing these, or alternatively set them up manually during implementation. For small customer specific add-ons, I normally just include any recommended formatted searches in a README document for the consultant to copy/paste during implementation.

John.

[Former Member]

Hi John

Regarding VALIDATE event,

The above scenario I specified is not with matrix, but for an Edit control with choose button.

I have written the below coding

Case SAPbouiCOM.BoEventTypes.et_VALIDATE

If pVal.ItemUID = "TxtEmpId" Then

If oForm.Items.Item("TxtEmpId").Specific.value <> "" Then

Dim oEmployeesInfo As SAPbobsCOM.EmployeesInfo

oEmployeesInfo = vCmp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oEmployeesInfo)

If oEmployeesInfo.GetByKey(oForm.Items.Item("TxtEmpId").Specific.Value) = False Then

SBO_Application.SendKeys("+")

BubbleEvent = False

End If

End If

End If

Why does the VALIDATE event keep on firing continuously?

As a result i keep on getting a FORMATTED search window after few seconds

I also get Before_ACtion property as always FALSE.

Please help me out

[Former Member]

Regarding my above question,

I used VALIDATE event of the control to pop up formatted search screen

If pVal.ItemUID = "TxtEmpId" Then

If oForm.Items.Item("TxtEmpId").Specific.value <> "" Then

Dim oEmployeesInfo As SAPbobsCOM.EmployeesInfo

oEmployeesInfo = vCmp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oEmployeesInfo)

If oEmployeesInfo.GetByKey(oForm.Items.Item("TxtEmpId").Specific.Value) = False Then

SBO_Application.SendKeys("+")

BubbleEvent = False

End If

End If

End If

As u suggested,I used the Form_Activate event to detect when control

returns from the formatted search, and issue a click onto the next field.

This again causes the VALIDATE event of that control to be fired.

Then formatted search screen pops up, when above condition fails

And again Form_Activate event fires.

So this makes the formatted search screen appear again and again in a loop

What shall I do?

[Former Member]

My suggestion was to use the Validate event to handle the populating of the other related fields on the screen, not to launch the formatted search from it. In my screens I launch the formatted search from the TAB keypress.

I will extract some code from one of my projects and post a sample here later.

John.

[Former Member]

Hi John,

I have also put the code in TAB.It works out fine.

But what I really want is , in screens such as "Purchase order" ,when I enter a wrong Vendor Id, the Formatted screen pops up again and again, when the user clicks anywhere in the screen until a correct vendor Id is provided.

I was trying to accomplish this.

If we put the code in TAB alone, then the above funtionality will not work during other conditions(when I click other controls)

Please post your code.It will be helpful to me.

Regards

Mina

[Former Member]

I think all you are missing is a check to make sure you don't try and re-run the lookup when you are validating the return value from a previous lookup.

I made a minor change to my code and achieved the behaviour you described. I have extracted the relevant code from the application . Unfortunately it looses its indentation when I post it, so it might be quite hard to read. It uses recordsets for the validation as in practice most of the real validation is more complex than this cut-down sample.

This particular example is from a screen with a Customer Code prompt, and displays the customers name below it.

Regards,

John.

<b>Constant holding menu uid of Formatted Search</b>

Public Const AZU_MNU_SEARCH As Long = 7425

<b>Declare a variable at module level</b>

Dim mblnCardLookup as Boolean

<b>Create validation routine (cut down sample included)</b>

Sub ValidateCustomer(sboApp As SAPbouiCOM.Application, sboCompany As SAbobsCOM.Company, BubbleEvent As Boolean)

On Error GoTo ErrorHandler

Const AZU_PROCEDURENAME As String = "ValidateCustomer"

Dim sboRecordset As SAPbobsCOM.Recordset

Dim sboForm As SAPbouiCOM.Form

Dim sboDSCard As SAPbouiCOM.UserDataSource

Dim sboDSName As SAPbouiCOM.UserDataSource

Set sboForm = sboApp.Forms(strUID)

Set sboDSCard = sboForm.DataSources.UserDataSources("UCARD")

Set sboDSName = sboForm.DataSources.UserDataSources("UNAME")

If sboDSCard.value <> "" Then

Set sboRecordset = sboCompany.GetBusinessObject(BoRecordset)

sboRecordset.DoQuery "Select CardCode, CardName, Currency From OCRD where CardCode Like '" & sboDSCard.value & "%'"

If sboRecordset.RecordCount < 1 Then

If sboDSName.value <> "" Then

sboDSName.value = ""

sboForm.Items("edtName").Update

End If

If Not mblnCardLookup Then

LookupCustomer sboApp

End If

BubbleEvent = False

Exit Sub

End If

sboRecordset.MoveFirst

sboDSCard.value = sboRecordset.Fields(0).value

sboDSName.value = sboRecordset.Fields(1).value

mstrCurrency = sboRecordset.Fields(2).value

sboForm.Items("edtName").Update

Else

sboDSName.value = ""

sboForm.Items("edtName").Update

BubbleEvent = False

End If

Set sboDSName = Nothing

Set sboDSCard = Nothing

Set sboForm = Nothing

Set sboRecordset = Nothing

Exit Sub

ErrorHandler:

Call AZU_STD_ERROR_MSGBOX(AZU_MODULENAME, AZU_PROCEDURENAME)

End Sub

<b>Create lookup routine (cut down sample included)</b>

Private Sub LookupCustomer(sboApp As SAPbouiCOM.Application)

On Error GoTo ErrorHandler

Const AZU_PROCEDURENAME As String = "LookupCustomer"

Dim sboForm As SAPbouiCOM.Form

Set sboForm = sboApp.Forms(strUID)

mblnCardLookup = True

sboApp.ActivateMenuItem AZU_MNU_SEARCH

Set sboForm = Nothing

Exit Sub

ErrorHandler:

Call AZU_STD_ERROR_MSGBOX(AZU_MODULENAME, AZU_PROCEDURENAME)

End Sub

<b>Add the following code to the ITEMEVENT handler</b>

'Validate Customer Code

If pVal.EventType = et_VALIDATE And pVal.ItemUID = "edtCard" And pVal.Before_Action = False Then

Call ValidateCustomer(sboApp, sboCompany, BubbleEvent)

End If

'Customer Code Lookup on TAB Key

If pVal.EventType = et_KEY_DOWN And pVal.ItemUID = "edtCard" And pVal.Before_Action = True And pVal.CharPressed = 9 Then

Set sboDSCard = sboApp.Forms(strUID).DataSources.UserDataSources("UCARD")

If sboDSCard.value = "" Then

LookupCustomer sboApp

BubbleEvent = False

End If

End If

'Force Validation on Return from Lookup

If pVal.EventType = et_FORM_ACTIVATE And mblnCardLookup = True Then

sboApp.Forms(strUID).Items("edtRef").Click

mblnCardLookup = False

End If

[Former Member]

Hi John,

Thx for your Code. I have tried and it works good.

Regarding CONDITION object, All examples use DBDatasource which points to a single table.

How can we form queries using JOINs and more complex conditions?

Happy Weekend!

Mina

[Former Member]

DBDataSources are currently pretty limited, you can only access the one table.

For more complex selection, the DI Recordset object is useful. You can build a SQL Query using Joins etc, and get the result set back. You can write the contents of this to UserDataSources if you want to display them on a form.

There is a very simple example of using this technique in the code I posted. You could easily try out using joins by changing the SQL string passed to the DoQuery method.

John.

[Former Member]

Thanks John.

[Former Member]

Hi John,

When I load my form using XML, all controls get loaded quickly.

But when it comes to matrix, there seems to be a problem.

First a blank black space comes in the space of the matrix and then all the rows appear as white lines.

The jerk is easily visible.

This happens when I load from XML ALONE.

I tried FORM.freeze also.

Could you please help me out?

[Former Member]

When I load my form using XML, all controls get loaded quickly.

But when it comes to matrix, there seems to be a problem.

First a blank black space comes in the space of the matrix and then all the rows appear as white lines.

The jerk is easily visible.

This happens when I load from XML ALONE.

I tried FORM.freeze also.

Could you please help me out?

[Former Member]

Hi Mina,

i think John mentioned this hint before. When loading a form via xml, the form does not have to be visible.

If you set the xml attribute visible of the form tag to zero, the form will not show. Simply set visible to true when you are done with loading.

That should get rid of any visual problems during batchLoad.

<form type="0" visible="0" .....>

HTH Lutz Morrien

[Former Member]

Thanks Lutz. Its working fine