pre filter

[keith21]

Hi Everyone
I need some help with pre -filtering

I have a sudc table
A field called prid which is the same as user groups setup.
I want to filter my table with the prid field.

Users in the list can only edit if within that group.
Is this possible?

 

[keith21]

I written the query but need a little help on it

SELECT
bxe9p_usergroups.title
FROM bxe9p_usergroups
INNER JOIN sudc
ON bxe9p_usergroups.title = sudc.PRID
WHERE sudc.PRID = (what the user selects not sure about this bit)

What I need is that if the logged on user is in that group he/she can only view records of that group.

can someone please help with this.

Thanks

 

[myfatebiz]

You can use {$my->groups}

 

[keith21]

I have tried this but still does not work.

SELECT
bxe9p_usergroups.title
FROM bxe9p_usergroups
INNER JOIN sudc
ON bxe9p_usergroups.title = sudc.PRID
WHERE sudc.PRID = {$my->groups}

 

[myfatebiz]

Put '{$my->groups}'

 

[keith21]

Sorry that does not work as well. It just shows up with no records, although records are saved in the DB.

 

[keith21]

I have attached a pic for you to have a look at.

Thanks alot for your time.

 

[myfatebiz]

You might need to try another style Type pre-filter to do it. I'm sure '{$my->groups}' is to get group value for current user login. Sorry can't help much.

 

[keith21]

I dont know if I am doing this right, I want it to filter by logged in user PRID (which is a group in joomla). The looged in user is assigned to a property (user group). In my SUDC table I want to view all records for that property (usergroup). SUDC table has Field called prid that links to the user group table Title. In my query I have done an inner join.

I have no records displayed. not sure where im going wrong.

 

[keith21]

if someone can help with this it be great, I am little stuck on it

 

[keith21]

I done a bit more work but still does not work, this is the error message I am getting :

If difficulties persist, please contact the System Administrator of this site and report the error below.

1052 Column 'id' in field list is ambiguous SQL=SELECT `id` FROM `bxe9p_usergroups` INNER JOIN `sudc` ON `bxe9p_usergroups.title` = `sudc.PRID` WHERE `sudc.PRID` = '89'​

 

[cheesegrits]

Just noticed that this one. Although you are a subscribed Standard user, it was in Community, so I hadn't looked at it.

I've moved the thread to the Standard group, and will take a look at it on Monday.

-- hugh

 

[keith21]

I have got my query to work although if my user belongs to more than one group it does not show any records, I cant wrap my head around this?
If I put my user into just one group it seems to work, but only show records for just that group.

 

[cheesegrits]

Can you show me the full query as you have it now?

-- hugh

 

[cheesegrits]

I suspect that if you still have it set up the way it is in your original post, it's because you are using "EQUALS" rather than "IN".

EQUALS will on work for a single value. If your subquery can return multiple results, you need to see if the result is "IN" the result set.

-- hugh

 

[keith21]

Hi Hugh
Thanks for the speedy reply.
Yes you are right I did use the equals rather than IN.

I did not use the query in the end and used another way to over come this issue. I used a drowdown element with the same values as my groups. This is the only way I got it work.

 

[keith21]

Hi Hugh
When using the pre filter its works great for registered users although the special user cannot view all records, is there a way to overcome this?
Need special users to view records regardless if not in the group.


Thanks

Keith

 

[cheesegrits]

You'll have to create a view level in J! which has only the groups in it you want the filter to apply to.

-- hugh