Filter Data Based on User

Applications Control
#1

How do I go about fileting data based on user. If i have multiple companies and each user is assigned to a different company. I want the user to only see there company data when logged into the form.

It from1 has data for all companies in it. There is a field for company. (1,2,3)
The user is assigned to company 1 I want them to only see company 1 data when logged into the form.

I am pretty new to ScriptCase and I am in the process of building out my app.

1 Like
#2

Re: Filter Data Based on User

This depends on how you store the ID.

Loading a grid, in the SQL:

SELECT <fields> FROM TABLE WHERE id_user = '[global_user_id]'; // or I use $_SESSION['id_user']

if you are calling app from a link:

SELECT <fields> FROM TABLE WHERE id_user = {id_user}; // where user_id was send from creating link in IDE or sc_link(..)

Regards,
Scott.

#3

Re: Filter Data Based on User

Thanks scot I will give it a shot and see how it works…

#4

Re: Filter Data Based on User

Hi Scoot,

BUT fgdesign don?t spoke about simple “user”, fgdesign wrote “many company, many users”, in other words, several users can have asigned the same company.

I want too can make one “SaaS” project, (One project AND many database files (one for each company with identical structure)) and after login can switch the connection depending on the proper company.

The question is

The SC_CHANGE_CONNECTION macro change the connection to the running application.
this change affect globaly to all instances running of the same application or only affect to my SESSION application ?

My Idea is named the default connection as “conn_00” and depending on the company number at login, construct a global variable [glo_myconn] with the string “conn_” & [glo_company] and change the connection with the macro in Onload event of each application.

Now, How to define all the connections in production environment ? or "How to execute the production environment, exist a command or shorcut ? (the production server dont have installed Scriptcase)

Aquiles

#5

Re: Filter Data Based on User

This did not work for me. I tried both way.

Here is the code I used.

SELECT 
  replid,
  cono,
  enterdt,
  poln,
  siteid,
  pcat,
  descrip,
  setupdate,
  avgldtm,
  binmin,
  binmax,
  monthsusg3,
  monthsusg12,
  orderqty,
  price,
  extcost,
  qohcdc,
  qohsite,
  qohcdwr,
  maxord,
  pouord,
  poord,
  requestor,
  status,
  dateapproved
FROM 
  dbo.tbl_replenishment
INNER JOIN dbo.sec_users ON dbo.tbl_replenishment.cono = dbo.sec_users.cono_hss
WHERE 
(dbo.tbl_replenishment.status = '1') AND (dbo.sec_users.login = $_SESSION['id_user'])

I put this code right into the SQL Select Statement on my application in the SQL section. This is a grid, which loads as the default on the Menu from the Security module.

#6

Re: Filter Data Based on User

If you there to enable debug for your SQL in settings, what is displayed on the screen? Is the generated SQL valid?

Regards,
Scott

#7

Re: Filter Data Based on User

This is the error I get.

“Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in C:Program FilesNetMakev5wwwrootscriptcaseappApprovalReplenishment_Approval_NewReplenishment_Approval_New.php on line 1169”

If I remove: “(dbo.sec_users.login = $_SESSION[‘id_user’])”

From my WHERE cluse it works fine. I can even put “(dbo.sec_users.login = ‘admin’)” and it work by grabing the info from the admin user. I only have admin setup right now as a user for testing. Is “$_SESSION[‘id_user’]” the right code to use? I am using the security module which I add the company to that user table. My user login field is not labels “id_user” it is just called “login” which I have also tried $_SESSION[‘login’].

Any thoughts?

#8

Re: Filter Data Based on User

From your last paragraph, I suspect that you misunderstood (or I did not fully explain) my first reply when I said I use ($_SESSION[‘id_user’]).
I suspect since you did not create that session var, it is empty and your SQL is invalid.

I use session vars created myself. I do not use what is built into SC.

See:
http://www.scriptcase.net/forum_en_us/index.php?topic=4106.msg9666#msg9666
http://www.scriptcase.net/forum_en_us/index.php?topic=4933.msg12561#msg12561
http://www.scriptcase.net/forum_en_us/index.php?topic=1723.msg5493#msg5493
http://www.scriptcase.net/forum_en_us/index.php?topic=1504.msg4690#msg4690

I myself have never used the built-in security module in any of my apps and have always built my own.
I might not be the best person to answer your question.

Regards,
Scott.

#9

Re: Filter Data Based on User

Scott i was able to get this working based on your code. I just needed to pass my user login as a global variable. Thanks for all your help. I am very new to Script Case and coding for it. So i am a very very very newbie! Thanks again for your help!

#10

Re: Filter Data Based on User

I think that must be

(dbo.sec_users.login = ‘$_SESSION[id_user])’

regards

#11

Re: Filter Data Based on User

(dbo.sec_users.login = ‘$_SESSION[id_user]’) <-----

mistake
regards

#12

Re: Filter Data Based on User

I also need lots of help what happens is I need a system where multiple users may have which might give them some privileges can only add additional update delete and others have read so you need to pass them as session variable and not as they do not where you have to do it please if you have something as an example where you can download and see how it would be helpful to please is very important