Grid Sql issue

Applications Grid / Reports
#1

I am trying to create a grid application that will only return the records from tbl1 if tbl1.mscID matches tbl2.mscID. Basically, I’ve added a column to the users table (tbl2) that is mscID. and they data comes from tbl3. tbl3 only has 1 column which is varchar(11). So the logic is the user logs into the project and loads this application. The grid will check the tbl1.mscID and return records from tbl2 where tbl2.mscID is the same.

tbl1
Active (int)(1)
mscID (varchar)(11) <-- select from tbl3
name (varchar)(32)
submscID(varchar)(11)

tbl2
mscID(varchar)(11) <-- select from tbl3
submscID(varchar)(11)



SELECT
   tbl1.Active,
   tbl1.mscID,
   tbl1.name, 
   tbl1.submscID,
   tbl2.mscID,
   tbl2.submscID
FROM
   tbl1, 
   tbl2
WHERE 
   tbl1.Active = 1 AND
   tbl1.mscID = tbl2.mscID

When I run the application it returns all records regardless of mscID.

What am I missing here?

Thanks

#2

sorry

and if trying,
SELECT
tbl1.Active,
tbl1.mscID,
tbl1.name,
tbl1.submscID,
(SELECT tbl2.mscID FROM tbl2 WHERE tbl2.idmscID = tbl1.mscID) AS mscID2,
(SELECT tbl2.submscID FROM tbl2 WHERE tbl2.idmscID = tbl1.mscID) AS sumscID2
FROM
tbl1
WHERE
tbl1.Active = 1

#3

nsch2308 - It gave me an error for the sql. I have tried to make it work using different join type and it still fails. It is like it is not reading the data in tbl2.mscID…

Still working on it.

#4

Found a part of the issue. I was not setting the column in the tbl2 as a variable to check. got it working but now I need to be able to set tbl2.msc as a session varible so when a different person logs in it will reload their mscID from tbl2. instead of the last one loaded.

#5

So this is what I did and I still cannot get it to load correctly.

In my login application I added this to the onVaildate event


$usr_login = {login}; // original code
$usr_email = {email}; // original code
$usr_msc = {mscID}; //ADDED CODED

sc_set_global($usr_login); //original code
sc_set_global($usr_email); //original code
sc_set_global($usr_msc); // ADDED CODE

In the grid application I added a where clause in the sql statement


WHERE tbl1.mscID = '[usr_msc]'

This works perfectly for the first login. Once you log out and then login as someone else it still shows the same records in the grid.

I have tried to set the global variables as session, get, and post, with the option of In and Out. It does not seem to matter.

I tried adding [usr_msc] on the ScriptInit event in the Login application in the sc_reset_global line. Once I run the application with this setting, it gives me No Records found.


sc_reset_global([usr_login], [usr_email], [usr_msc]);

I know I’m over looking something simple but for the life of my I cannot figure this out.

SELECT
   tbl1.Active,
   tbl1.mscID,
   tbl1.name,
   tbl1.submscID,
   tbl2.mscID,
   tbl2.submscID
FROM
   tbl1
WHERE
tbl1.mscID = '[usr_msc]'
#6

the Keep Values is turned off.

I am afraid I am not following the rest of your explanation on the reply. This has nothing to do with a password. I am trying to filter the results of the grid based on the user’s login row.

I was able to get this working to declare the variables when the user logs in. The issue I am having now is the grid will not filter correctly I have tried using CASE Statements, expressions and various where clauses in my SQL. None of them seem to be working as intended.

In the same grid, I want to be able to adjust the filter level based on a particular field.

I have a [glo_var] which is [usr_level] which has a stored value of 1, 2, 3, or 4 only.

I need to filter the grid as follows

If usr_level is 1 I need to filter based on tbl_1.agencyID
If usr_level is 2 I need to filter based on tbl_1.CompID
If usr_level is 3 I need to filter based on tbl_1.UnitMSC
If usr_level is 4 I need to filter based on tbl_1.UnitComp

In the users table I added the following columns ( usr_agency, usr_comp, usr_MSC, and usr_UnitComp) each of these are set to a global variable.

usr_agency = [agencyID]
usr_comp = [compID]
usr_MSC =[mscID]
usr_UnitComp = [ucompID]

I used the following CASE statement in the Where Clause on the Grid SQL and it seemed to work until I tried to search the grid, or do a fined search:

WHERE '[usr_level]'
  CASE WHEN '[usr_level]' = 1 THEN 
    tbl_1.agencyID = '[agencyID]' and tbl_1.Active = 1
  END
  OR
  CASE WHEN '[usr_level]' = 2 THEN
    tbl_1.CompID = '[compID]' and tbl_1.Active = 1
  END
  OR
  CASE WHEN '[usr_level]' = 3 THEN
    tbl_1.UnitMSC = '[mscID]' and tbl_1.Active = 1
  END
  OR
  CASE WHEN '[usr_level]' = 4 THEN
    tbl_1.UComp = '[ucompID]' and tbl_1.Active = 1
  END

Why did this break the search options in the grid?

#7

have you tried this in the search events?
Search: advanced search, events, on validate.

#8

The code breaks the application.

I need to see if there is a way to write this as a PHP method with an IF Statement instead…

#9

either you do:
echo ‘code_html’;
or :
?>
code_html
<? php
where you need it
test this in a blank application

#10

I need this to execute in the sciptinit or the onload event… I don’t want the user to have to run the search to filter the results.

thanks

#11

for me best is onload event… then use ?> <?php, otherwise you will have a code window that will open unnecessarily

#12

was never able to get this to work.