Using Views on SQL Database to Filter Displayed Data

[andrewdebeer]

Hi All

I need to develop a web application using A5 that accesses filtered data from a SQL database. Easy enough, right?

But our company consists of a central group head office, 9 regional head offices each with between 11 and 27 branch offices throughout the country.

The data is captured at head office level and HO therefore needs access to all the data. The regional offices and subsequent branch offices must have entry points to ONLY access to their own data. Each record must have columns to identify the correct region and branch.

That's simple (I thought) we'll just use views at server level to filter the data. Until I started thinking it through...... How do I pass a value to the view in SQL to achieve the filter?

Surely the user log in process can identify the user and the correct entry point from a data table, but how do I get the entry point value to the view?

Am I over-complicating this issue? Surely there must be many such examples in place.

All advice will be appreciated.

Sincere Regards
Andrew

[Steve Workings]

Re: Using Views on SQL Database to Filter Displayed Data

Andrew - I have a couple apps with this kind of scheme. One grid, for instance, but different selections of records depending on the HO, office or branch.

I think a key here is an understanding that you're not really going to use Alpha to pass the filter to the SQL data, but rather use Alpha to select which data to display (at least in what I'm about to describe).

When you set up security and related session variables, design so that each user is assigned some role -- such as Office, Branch -- that sort of thing. This is easy and can come right out of the security framework.

When you create your grid (using this as an example), add arguments to the query definition:

arg_Branch_ID
arg_Office_ID

and populate them with the appropriate session variable (session.Branch_ID for instance).

Create your grid as usual.

Then, go to the grid's OnGridExecute event, where you can modify the grid's select statement depending on your need.

Straying from your example into one of mine, here's a snippet of code for an activity log where I want facility administrators to see the activity log for their facility, but the company admin to see it for their whole company which can include many facilities:

	if "Facility_Admin" $ session.usergroups
		e.tmpl.CS.SQL = e.tmpl.CS.SQL + " where facility_id = :arg_facility_ID"
	end if
	if "company_admin" $ session.usergroups
		e.tmpl.CS.SQL = e.tmpl.CS.SQL + " where comp_id = :arg_company_id"
	end if

If you don't know what the tmpl.CS.SQL is, look in the Xbasic tab in the very very bottom left part of your design screen and you'll find this string.

The explanation here is long, but the development and execution of this goes very quickly and is easy to implement.

[andrewdebeer]

Re: Using Views on SQL Database to Filter Displayed Data

Hi Steve.

Thank you for your advice. I am just wondering what the difference in response time is between running a SQL script on the client vs a SQL view on the server. I'm a desktop app developer and am only moving into the web arena now, am I being over sensitive regarding this issue?

Best Regards
Andrew

[agillbb]

Re: Using Views on SQL Database to Filter Displayed Data

For most queries, there won't be much difference at all - the slowdown will mostly happen in network lag and transferring the data.
For the heavy ones, I prefer to keep the queries optimised within the database environment (in views).
Personally, I find myself using views for almost everything...for grids and dialogs. This way there is less chance of accidentally messing up the syntax of a heavily-joined query.
By this is only my opinion. It is personal preference, really.

[lvasic]

Re: Using Views on SQL Database to Filter Displayed Data

I use views with a UDF (User Defined Function) that gets what tenant ID logged in. From there I use all of the built in Alpha Security.

[andrewdebeer]

Re: Using Views on SQL Database to Filter Displayed Data

Thanks Andy and Lee.

Your input is really appreciated. It seems that a combination of SQL views and Alpha security will bring together the best of both worlds.

Sincere Regards
Andrew