cancel
Showing results for 
Search instead for 
Did you mean: 

DBDatasource in a user form not filtering

0 Kudos

Hi,

I need help in figuring out how to filter the display of the user form to certain records depending on their assigned access. I have created a user form through the use of the screen painter. The user form is divided into two sections. One header and one detail, much like the way marketing documents are displayed. Each of the header edit fields has been binded to a UDO field. The detail is a matrix and likewise binded to  UDT columns.

Once the form is triggered from the menu event, the script below kicks in:

oDS0 = oForm.DataSources.DBDataSources.Item("@OB_PRQ")

If oMyUserInfo.SecurityLevel = 1 Or oMyUserInfo.SecurityLevel = 2 Then

      If oMyUserInfo.SecurityLevel = 1 Then

         'oConditions = SBO_Application.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_Conditions)

         oConditions = New SAPbouiCOM.Conditions

         oCondition = oConditions.Add

         oCondition.BracketOpenNum = 1

         oCondition.Alias = "U_REQBY"

         oCondition.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL

         oCondition.CondVal = oMyUserInfo.username

         oCondition.BracketCloseNum = 1

         oDS0.Query(oConditions)

     Else

         'oConditions = SBO_Application.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_Conditions)

         oConditions = New SAPbouiCOM.Conditions

         oCondition = oConditions.Add

         oCondition.Alias = "U_DEPTID"

         oCondition.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL

         oCondition.CondVal = oMyUserInfo.deptid

         oDS0.Query(oConditions)

      End If

End If

  

oDS1 = oForm.DataSources.DBDataSources.Item("@OB_PRQ1")

If oMyUserInfo.SecurityLevel = 1 Or oMyUserInfo.SecurityLevel = 2 Then

     If oMyUserInfo.SecurityLevel = 1 Then

         'oConditions2 = SBO_Application.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_Conditions)

         oConditions2 = New SAPbouiCOM.Conditions

         oCondition2 = oConditions2.Add

         oCondition2.Alias = "U_UserSign"

         oCondition2.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL

         oCondition2.CondVal = oMyUserInfo.userid

         oDS1.Query(oConditions2)

     Else

         'oConditions2 = SBO_Application.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_Conditions)

         oConditions2 = New SAPbouiCOM.Conditions

         oCondition2 = oConditions2.Add

         oCondition2.Alias = "U_DEPTID"

         oCondition2.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL

         oCondition2.CondVal = oMyUserInfo.deptid

         oDS1.Query(oConditions2)

     End If

End If

Then the form opens in "find mode". When I clicked the next record button, the user form still shows all the records of UDO/UDT despite the condition was successfully executed above with no error. Any ideas what is wrong with my code?

Accepted Solutions (1)

Accepted Solutions (1)

pedro_magueija
Active Contributor
0 Kudos

Hi Albert,

The following is not a perfect solution, and it breaks the navigation ability (although you can capture the correct menu events and make it work again).

These must be either static or in a map (static will mean that you can only have one instance of the form open).

    Private Shared offset As Integer = 0

    Private Shared ds As DBDataSource = Nothing

This on the FORM_LOAD event:

      offset = 0

      ds = form.DataSources.DBDataSources.Item("<ds_id>")

This goes on the FORM_DATA_LOAD event (BeforeAction = false):

      If (ds.GetValue("field", 0) <> "field_value") Then ' this 0 is correct, because ds was populated by B1 with only one record, at this stage

        Dim cs As Conditions = B1Connections.theAppl.CreateObject(BoCreatableObjectType.cot_Conditions)

        Dim c As Condition = cs.Add()

        c.Alias = "field"

        c.Operation = BoConditionOperation.co_EQUAL

        c.CondVal = "field_value"

        ds.Query(cs)

        ds.Offset = offset

        If (offset < ds.Size - 1) Then

          offset += 1

        Else

          offset = 0

        End If

      End If

Again this is by no means the best solution to the problem, and surely will have issues with performance if you have a lot of data.

But it is a starting point.

Hope it helps.

Best regards,

Pedro Magueija

0 Kudos

Hi Pedro,

Thank you for the assistance. I tried to adopt  your example but only the header part is filtering. The detail, which has the matrix object, is still retrieving all the records. I don't know if this is the limitation of the matrix object once you have it bounded to a table, all the records will be displayed.

Regards,

Albert

pedro_magueija
Active Contributor
0 Kudos

Hi Albert,

With line filtering:

Dim ds As DBDataSource = form.DataSources.DBDataSources.Item("cab_id")

Dim lines As DBDataSource = form.DataSources.DBDataSources.Item("line_id")

      If (ds.GetValue("cab_field", 0) <> "val") Then ' this 0 is correct, because ds was populated by B1 with only one record, at this stage 

        Dim cabConds As Conditions = B1Connections.theAppl.CreateObject(BoCreatableObjectType.cot_Conditions)

        Dim lineConds As Conditions = B1Connections.theAppl.CreateObject(BoCreatableObjectType.cot_Conditions)

        Dim cabCond As Condition = cabConds.Add()

        Dim lineCond As Condition = lineConds.Add()

        cabCond.Alias = "cab_field"

        cabCond.Operation = BoConditionOperation.co_EQUAL

        cabCond.CondVal = "val"

        ds.Query(cabConds)

        Dim code As String = ds.GetValue("Code", offset).Trim

        lineCond.Alias = "line_field"

        lineCond.Operation = BoConditionOperation.co_EQUAL

        lineCond.CondVal = "line_val"

        lineCond.Relationship = BoConditionRelationship.cr_AND

        lineCond = lineConds.Add()

        lineCond.Alias = "Code"

        lineCond.Operation = BoConditionOperation.co_EQUAL

        lineCond.CondVal = code

        lines.Query(lineConds)

        CType(form.Items.Item("mtxFases").Specific, Matrix).LoadFromDataSource() ' very important as you'll load B1 loaded data if you do not call this

        ds.Offset = offset

        If (offset < ds.Size - 1) Then

          offset += 1

        Else

          offset = 0

        End If

      End If

Hope this helps.

Best regards,

Pedro Magueija

0 Kudos

Hi Pedro,

Thank you for the script. I manage to make it work with your script. The only drawback with the script is that the offset always moves forward even if you pressed the previous button.

Regards,

Albert

pedro_magueija
Active Contributor
0 Kudos
  1. Hi Albert,

Yes the script does that. But, you can capture the menu events and manipulate the offset variable to help with that.

Example:

capture the 1290 menu_click_event and place:

YourClass.offset = 0 ' since it is a shared var you will be able to access it

capture the 1288 menu_click_event and place

if(YourClass.offset > 0) then

     YourClass.offset -= 1 ' since it is a shared var you will be able to access it

else

     YourClass.offset = YourClass.lastRecord

end if

capture the 1289 menu_click_event and place

if(YourClass.offset < YourClass.lastRecord) then

     YourClass.offset += 1 ' since it is a shared var you will be able to access it

else

     YourClass.offset = 0

end if

capture the 1291 menu_click_event and place

YourClass.offset = YourClass.lastRecord

In your class place a new var to hold the lastRecord

Private Shared lastRecord As Integer = 0

and in the previous code remove the if and populate the lastRecord var

Dim ds As DBDataSource = form.DataSources.DBDataSources.Item("cab_id")   Dim lines As DBDataSource = form.DataSources.DBDataSources.Item("line_id") 

 

      If (ds.GetValue("cab_field", 0) <> "val") Then ' this 0 is correct, because ds was populated by B1 with only one record, at this stage   

 

 

        Dim cabConds As Conditions = B1Connections.theAppl.CreateObject(BoCreatableObjectType.cot_Conditions) 

        Dim lineConds As Conditions = B1Connections.theAppl.CreateObject(BoCreatableObjectType.cot_Conditions) 

        Dim cabCond As Condition = cabConds.Add() 

        Dim lineCond As Condition = lineConds.Add() 

 

 

        cabCond.Alias = "cab_field" 

        cabCond.Operation = BoConditionOperation.co_EQUAL 

        cabCond.CondVal = "val" 

        ds.Query(cabConds) 

        lastRecord = ds.Size - 1

        Dim code As String = ds.GetValue("Code", offset).Trim 

 

 

        lineCond.Alias = "line_field" 

        lineCond.Operation = BoConditionOperation.co_EQUAL 

        lineCond.CondVal = "line_val" 

        lineCond.Relationship = BoConditionRelationship.cr_AND 

        lineCond = lineConds.Add() 

        lineCond.Alias = "Code" 

        lineCond.Operation = BoConditionOperation.co_EQUAL 

        lineCond.CondVal = code 

 

 

        lines.Query(lineConds) 

        CType(form.Items.Item("mtxFases").Specific, Matrix).LoadFromDataSource() ' very important as you'll load B1 loaded data if you do not call this 

 

 

        ds.Offset = offset

Hope it helps.

Best regards,

Pedro Magueija

Answers (1)

Answers (1)

0 Kudos

Hi Albert,

I think you need to use UserDataSource, Recordset and Collection classes to solve this problem.

Ben Penafiel