Hi,
I have declared a global variable [login] upon user successful login.
I have a grid application which contains multiple records but I only want the user to see only their own records based on the login.
In the grid application > under event “OnRecord”,
if({manager}==’[login]’){
sc_select(dataset, “select * from table1 where manager=’[login]’”);
}
The above code does not works. Can someone kindly advise? or is there actually a macro can be used to display records based on the field.
You must set the SQL of your grid with a filter for you user.
If I understand correctly, you mean under SQL of the grid, I just enter the where clause for the sql statement. “WHERE manager=’[login]’”.
However, I need to do a number of if else check. How should I go about on this? Thanks.
Eg.
1) admin user can access all records
2) manager only can access their own department record based on if their name appear under the column of the record.
Use la macro: [LEFT][SIZE=13px]sc_select_where [/SIZE][/LEFT]For that you don’t need use the filter for SQL. The SQL must be wihtout FILTER.
In event OnscriptIni:
if ([login] != ‘administrator’)
{
sc_select_where(add) = “where manager = [login]”;
}
thanks the macro works…but when I tried to do search (quick search or advance search), it will prompt errors. Please kindly advice.
I tried put the same codes in advanced search > event > onScriptInit, it does not works. Quick search does not has event.
When using advanced search:
[LEFT][SIZE=13px]Error while accessing the database[/SIZE][/LEFT]
[LEFT][SIZE=13px]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘where manager = ‘007’’ at line 1[/SIZE][/LEFT]
[LEFT][SIZE=13px]select count(*) from table1 where field like '%test%'where manager = ‘007’
[/SIZE][/LEFT][LEFT][SIZE=13px]When using quick search:[/SIZE][/LEFT]
it gives some meta data prompt.
[LEFT][SIZE=13px]i Can see the clause where need a space: '%test%'where manager = ‘007’
try add a space to macro at begining [/SIZE][/LEFT][LEFT][SIZE=13px]sc_select_where(add) = " where manager…[/SIZE][/LEFT]
Hi,
Anyone can kindly advise on this issue?
Not sure is there issue with the macro. Cant find any solution to this.
Thanks.
I don’t know how fix your issue, But try set in your setting for quick search: add fields, disable o enable the opci?n: Maintain search condition.
I can see you are setting where clause twice in the same query. You should do something like this
sc_select_where(add) = " and manager = [login]";
Instead of:
sc_select_where(add) = " where manager = [login]";
In order to be successful with your query when doing a quick or advanced search, you can set a where clause like this “WHERE 1=1” in your main SQL query
Hi,
Thanks both for the help.
It works by including the where 1=1 in the sql and the macro removed the where clause.