on 05-21-2012 8:07 AM
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?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
Hi Albert,
I think you need to use UserDataSource, Recordset and Collection classes to solve this problem.
Ben Penafiel
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.