Changing SQL query of the Browse Form

[Janusz]

Hi,
I would like to change the SQL browse FORM based on variable from JS (for example limit data displayed based on user name or department name)

for ex.: initially in the SQL Browse Form Properties I have:

Code: Select all

SELECT storage_id, sto_ref_nr, sto_name, sto_memo, sto_resp FROM storage

and from JavaScript I would like to update this with:

Code: Select all

nuSetProperty('browse_sql', "SELECT storage_id,sto_ref_nr,sto_name,sto_memo,sto_resp from storage WHERE sto_resp='John'");

In the CONSOLE I can see that this property is changed - but it does not affect at all the browse data displayed.

Do you think should this work? or maybe any suggestion how can I do it?

[admin]

Janusz,

If you run this first

Code: Select all

nuSetProperty('responsible', 'Bob');

You can create a temp table in Before Browse (#TABLE_ID#) and use that.

Code: Select all

$s = "
CREATE TABLE #TABLE_ID#
SELECT storage_id, sto_ref_nr, sto_name, sto_memo, sto_resp 
FROM storage
WHERE sto_resp = '#responsible#'
";

nuRunQuery($s);

And swap the Form's SQL to use the new table.

table_id.JPG

Steven

[Janusz]

Steven, thanks a lot.
It's working .
There is one small issue - when you initlally opening the browse form it's empty.
To have data in the from you need to use refresh or just to press column title like for sorting - and after this initial let say conditioning it works OK.
So probably I will implement a kind of refresh just after form opening.

[admin]

Janusz,

If it's blank to start with I guess it is because you haven't had a chance to set the Hash Cookie.

Maybe if you include that possibility in your SQL.

Code: Select all

$s = "
CREATE TABLE #TABLE_ID#
SELECT storage_id, sto_ref_nr, sto_name, sto_memo, sto_resp 
FROM storage
WHERE sto_resp = '#responsible#'
OR '#responsible"."#' = '#responsible#'
";

OR '#responsible"."#' = '#responsible#'

Notice the string is joined together so that '#responsible#' doesn't get replaced twice.

Steven

[Janusz]

Steven,
Thank you very much for your great support and suggestions - which was really very helpful for me.
So after the first quick trial when I had the issue with initiating the table - and after some more tests later - finally I moved the whole code to BB php and I am setting the data based on user name and everything works pefectly

With the following code I can separate data per every user (so everyone has an access only to his data) and for admin I have an access to the full table

Code: Select all

$object = nuUser();
$usr=$object->sus_name;
if ($usr=="") $usr="admin";

$s = "
CREATE TABLE #TABLE_ID#
SELECT * FROM rejestr
WHERE rej_created_by = '$usr'
ORDER BY rej_created_on DESC
";

$s1 = "
CREATE TABLE #TABLE_ID#
SELECT * FROM rejestr
ORDER BY rej_created_on DESC
";

if ($usr=="admin") {nuRunQuery($s1);} else {nuRunQuery($s);}

[Janusz]

and I checked the following on my original case from the initial post

Code: Select all

......
OR '#responsible"."#' = '#responsible#'

and with that the situation is following that initially I get the full browse form with all data displayed and after any operation on the form - data is limited as requested by query.
But anyway for the moment the filtering based on the user name - as I described in my previous post - completely solved my problem

[kev1n]

Steven,

Code: Select all

$object = nuUser();
$usr=$object->sus_name;
if ($usr=="") $usr="admin";

$s = "
CREATE TABLE #TABLE_ID#
SELECT * FROM rejestr
WHERE rej_created_by = '$usr'
ORDER BY rej_created_on DESC
";

$s1 = "
CREATE TABLE #TABLE_ID#
SELECT * FROM rejestr
ORDER BY rej_created_on DESC
";

if ($usr=="admin") {nuRunQuery($s1);} else {nuRunQuery($s);}

A bit more compact:

Code: Select all

$usr = nuUser()->sus_name;

// No WHERE clause if admin
$where = ($usr == "") ? "" : " WHERE rej_created_by = '".$usr."' ";

$qry = "
	CREATE TABLE #TABLE_ID#
	SELECT * FROM rejestr"
	.$where."
	ORDER BY rej_created_on DESC
";

nuRunQuery($qry);

[Janusz]

Hi Kevin,
Thanks for sharing - this short code is working
Appreciate very much your post - it's always very interesting to see some improvements which can be implemented to the code.

[nc07]

Hi,

I have tried the code suggested by Kevin and Januz. I have multiple sites like LTK or NAN which is recorded through java and works fine. All site have site managers, so I need managers to view all staffs record for his site (individuals viewing their own record is working fine form me) and the General Manager (GM) to view all records from all sites. The user access level reads something like staff-ltk or staff-nan and for managers like mng-ltk or mng-nan. I need help here, please.

[Janusz]

Hi,
Maybe this example can be helpfull - just tested and it's working well. You can use it from web browser - exactly like in attached example or create similar php code for the nuBuilder (but did not test from nuBuilder)
https://www.ionos.com/community/hosting ... -database/
when using this example directly please be carefull with security issue because such file can be easilly downloaded by anyone having the link with wget and there is password inside.