The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
custom Account-Contacts datagrid view
Posted: 29 Jun 09 4:18 AM
|
I have added two tabs to the basic datagrid view, containing the accounts contacts, that appears under the Account details screen to show the creator of the account contact and the department they belong to. What i would like to do, and what im having trouble with, is filtering the datagrid depending on the department to which the person viewing the account belongs. For example, if a member of the IS department logs into SalesLogix and begins vewing accounts i only want them to see the contacts that have been created by IS department users.
I have written some code that populates a text box i added to the account view with the users department but im having trouble filtering the datagrid.
Any help would be great, thanks. |
|
|
|
Re: custom Account-Contacts datagrid view
Posted: 30 Jun 09 2:30 AM
|
Each Datagrid (unless it is bound to a Recordset) will have an SQL statement associated with it.
You could get the statement at run time (put it on a Message Box), modify it to handle your conditions and then set it back into the Grid's SQL Property:
e.g.
' Extract the original SQL statement for guidance, then comment out
' SQL = myGrid.SQL.Text
' msgbox SQL Use to get the SQL Statement from the grid during a test run, afterwards just put in your own Statement based on what you gather from here
If Department = "Dept 1" then
SQL = "SELECT ...."
Else If Department = "Dept 2" then
SQL = "SELECT ...."
End If
myGrid.SQL.Text = SQL
' myGrid.Refresh (You may not need to do this, try it, if the data doesn't change, add this code) |
|
|
|
Re: custom Account-Contacts datagrid view
Posted: 30 Jun 09 2:43 AM
|
Thank you very much for your reply Raul.
However, i can not edit the SQL statement in the properties of the datagrid everything has to be done through the Query Builder feature. Is this an access issue i can change to be able to edit the SQL? |
|
|
|
Re: custom Account-Contacts datagrid view
Posted: 30 Jun 09 2:59 AM
|
Ok, so you are not looking for a Coding solution, but rather on how to do it at Design Time on the Group Builder?
Easy (sometimes).
So, if what you want to do is show Contacts that were created by members of the same Department:
- On the Conditions tab, add the Department tab as a Condition to the list.
- On the Operator, select equal to (=) -- No need to use IN since we are going to be returning a single value. If in doubt, use IN instead.
- Check the Case Sensitive Compare (otherwise SLX will add "UPPER" statements to the query - This depends on the version of SLX. 7.2 and higher may not if the User value as literal is selected)
- Check the Use Value as Literal Checkbox
- On the Value is text box, type the following statement:
(SELECT DEPARTMENT FROM USERINFO WHERE USERID = :userid)
The :userid should be replaced by SLX at runtime with the ID of the current User.
The Subquery will then return the Current User's Departmet, and that may achieve the result you are looking for. |
|
|
|
Re: custom Account-Contacts datagrid view
Posted: 30 Jun 09 3:20 AM
|
Hi Raul,
Good news is there were no errors, bad news is the view in the datagrid didnt change. But youve got me thinking 
Actually sorry there was an error i was publishing the wrong plugin!!! The error says 'Object Required: Sender.RecordSet'
I have tried variations on the where clause but the error only stops if i cmpletely remove the filter. |
|
|
|
Re: custom Account-Contacts datagrid view
Posted: 30 Jun 09 3:44 AM
|
Well, first of all, I am not quite sure what you are trying to do and/or your schema, so you should adjust the conditions to match your schema.
Second, the error message is coming from an Event Handler, but even if the SQL statement was wrong, you shouldn't be receiving this error, did you make any code changes?
Third, and last, run SLX Profiler so you could see the actual SQL statement being executed, but also take a gander at the scripts behind the form. The error message probably has the line number for the error, so you should be able to see where it is failing. |
|
|
|
Re: custom Account-Contacts datagrid view
Posted: 30 Jun 09 3:51 AM
|
Hi Raul,
The error message seems to point to here:
If Sender.Recordset.RecordCount > 0 Then
frmContacts.AddToContextList "CONTACT", grdContacts.GetCurrentField("CONTACTID"), grdContacts.GetCurrentField("NAMELF") 'DNL
End If
I REALLY appreciate your help, thank you. |
|
|
| |
|
Re: custom Account-Contacts datagrid view
Posted: 30 Jun 09 4:15 AM
|
This obviously means that the recordset is NULL.
I would suggest that you run the SLX Profiler and check what SQL statement it is running (and validate against the SQL statement from the Grid).
I would've expect you had received a "Failed to Parse" message, but as stated, it is still possible that there are other things missing ... |
|
|
| |
| |
|
Re: custom Account-Contacts datagrid view
Posted: 30 Jun 09 4:49 AM
|
Originally posted by Andrew Grandin
Thank you very much for your reply Raul.
However, i can not edit the SQL statement in the properties of the datagrid everything has to be done through the Query Builder feature. Is this an access issue i can change to be able to edit the SQL? |
|
Also, what I meant on this is that you could place code on the FormChange event to capture and/or replace the SQL statement on the grid.
Doing it this way would provide a simpler query for the grid, and on a big database maybe a more optimal execution. |
|
|
|