addTableFilterParam

Classic Servoy
1

on 12/16/2004 Patrick:
What is nice about addFoundSetFilterParam is that Servoy takes care of that filter in all occasions (searches and so on). I think addFoundSetFilterParam should just stay as it is, but a addTableFilterParam could be added to the application node. With that we could completely “block” certain records for a session wherever they are shown.

I see that R2 2.2rc5 now includes addTableFilterParam. Does it do what Patrick described? The sample code reads:

//Filter all new created foundsets based on a table
//best way to call this in a global solution startup method
var success = databaseManager.addTableFilterParam('admin', 'messages', 'messagesid', '>', 10)

This looks very interesting. Has anyone got this to work yet? Any other samples, explanations, or comments would be appreciated.

2

It does what patrick describes, what info are you missing?

3

And thank you very much for that ! :D

4

Jan Blok:
It does what patrick describes, what info are you missing?

If you have a Contacts table and a related Letters table and you want to have four groups of people use the two tables based on their password, but need to have them not be able to see the records of users that are not in their group, how would you use the addTableFilterParam to achieve this (in this example there would be a total of four passwords, each group having their own password)?

If someone could provide sample code that would handle this type of situation it would be greatly appreciated.

5

Could you describe your issue a little better? How are contacts linked to a “password”? What is the relation between your data and the user?

6

For us this function is HEAVEN.
We develop a multi-company solution and at startup we use it to seperate the data.

Thanks a lot Servoyians!

7

patrick:
Could you describe your issue a little better? How are contacts linked to a “password”? What is the relation between your data and the user?

Good question. I guess each record in each table would need to have a password field that is assigned the password of the user when the record is created. Then the addTableFilterParam would be used at startup to filter out everyone else’s records based on the password field (filtering out the ones that do not have a matching password). Is that how it is supposed to work?

But I am still not clear on the syntax and when and how the filtering is done. It sounds like the filtering is done from a global method at startup. So in this scenario would the addTableFilterParam have to be applied several times within that method? I think I am close to understanding it, but a more detailed sample would be a big help.

8

You said it yourself, you work with four groups.
So you have to add to every table a column: group

and filter your data on that group where the user belongs to!

9

HJK:
You said it yourself, you work with four groups.
So you have to add to every table a column: group
and filter your data on that group where the user belongs to!

Yes, but the devil is in the details. Has anyone figured out how to do this using Servoy’s own built-in user name and password dialog, or is it better to use your own user name and password form?

10

I had a question along these lines as well. I have not yet played with the Servoy security, and assume the users are assigned to groups and you can assign table, field and layout access to users or groups.

But know that there is not and option to assign a calc to a group to filter records (like filemaker).

Anyway my solution is more department based, customer controllers for each department, that have access to their own custom forms. So in a sensse some of the security is controlled though navigation.

Forms that are shared accross the departments will have certian panels hidden / available or fields uneditable etc.. through methods trigged when navigating to the form depending on the department that accesses the form.

My question is this, I have one several forms that Sales Reps access through their custom navigation controller. All the reps will be using the same access level with the exception being that they can only access records that are related to them (each record has A Rep ID to reference). I also have a seperate table for all employees that define the reps with their ID, and other information.

I was hoping to set up a group within the servoy security for the reps that would restrict their access to the forms that are related to them.

I was also hoping use the one of the filter param options on the form, that would be an If statement that would verify their who was logged in to filter all searchs and records to the specifc rep.

It seems that servoy would need to know the Rep ID of the person logging in and store that in a global or something for the Filter Param to reference.

It seems like I am going to have to employ custom security no matter what. I wanted to try to keep this clean as possible. I am not sure the best way impliment security in this scenario.

Thanks for any push in the right direction.

11

I am normally the last person to make a suggestion regarding Servoy and anything but I have been thinking about the same issues.

I also have a short sighted/practical streak so this solution would really only work with a reasonable number of clients that won’t change that often.

One way of skinning this cat would be to have a filter table with a record for each group you wanted to provided various filtered record access to. For example, client table with a unique clientid.

Then create groups in servoy’s built in sercurity piece for each entry in your client table. Each security group in servoy is given an id number and you can manually enter that group id in your Filter table (ie clientid in my example).

Upon a users login you can figure out a users servoy security group and match it against your filter table unique id. Then set a global with that number and use it to filter your various tables with the idea Westy and HJK were referring to.

A nice perk is that you link the filtering functions servoy provides to servoy security, albeit with some manual work arounds. Also, once a user is assigned a group in servoy security they pick up the groups filtering settings.

Now I bet a lot of folks have complaints like…

You probably have to make sure that a user in servoys security scheme is only in one group.
Your Right!

You have to manually match the servoy group id to the filter table for each new group!
Your Right!

I didn’t say it was a good solution, just one option. This is what I have in place untill someone smarter then me figures out a better solution.

-Jim

*PS. Is anyone here wistfully looking forward to the day when we can use the filter functions on related fields? I for one don’t like the idea of having to add the filter field in every table that I want to filter when I am starting at the same field from a 1-1 related table.

You said it yourself, you work with four groups.
So you have to add to every table a column: group

and filter your data on that group where the user belongs to!

12

gte451f:
One way of skinning this cat would be to have a filter table with a record for each group you wanted to provided various filtered record access to.

Yes, and it would be nice to be able to automatically populate the table with the Servoy security group names. I think Morley mentioned something about this in a prior thread. You can get an array of all the existing group names and then use it to loop through and populate the table. The group’s id could also be placed in the table and used for the filter, but then you would have to make sure that a user is never in more than one group and never changed to a different group.

13

Regarding the User only allowed in one Group: What’s the idea behind that?

Can’t you add multiple Tablefilter Params over the same table?

Just have to check for conflicting filter yourself, but I think that should be a doable task.

And regarding Related records filter: Wouldn’t you just get the Related records for the records in the main table that you are allowed to see and if you have additional restrictions for the related records, you would have to put a tabel filter on that related table?

Can’t see why you would put restrictions on related records, looking from the main table. Each table should have it’s own filters and if you set that up properly, you only see the allowed main AND related records.

Or am I missing something here?

Paul

14

Can’t you add multiple Tablefilter Params over the same table?

Yeah, that isn’t what I originally had in mind but I don’t see why you couldn’t, barring a servoy function limitation.

Regarding the User only allowed in one Group: What’s the idea behind that?

In the idea outlined previously a servoy security group was matched up (Manually) to a filter table via some unique id. Keeping it to 1 group means manually linking 1 security group to 1 filter table record.

I have this in my opening script…

//Set the ClientID to limit access
//Get the current users groups
//We are assuming 1 group per user
var o = security.getUserGroups()
application.output (o.getValue(1,2))

//Their groupid has to match up to their client id!
//ClientValue is the field used in subsequent filterParams
globals.ClientValue = o.getValue(1,1)

Having a user in multiple groups would mean that you would require more logic to determine which group id takes presidence or adding more globals to store all the security groupid’s. This means more hard coding.

I guess it’s possible but the only benefit I see is the abiliity to filter somthing n number of times. In that case just store the actual ids used to filter in side of the filter table rather then in the form of multiple group ids. That way the groupid only need to get you to the correct record in the filter table. Then the filter table holds as many filter fields as you like. When you want to change filter fields do it there instead of duct taping multiple groups to multiple filter table records.

Just a thought…

15

Regarding filterparam on related fields…

A sample from a previous post…

camp

campid

family

familyid
fk_campid

person

personid
fk_familyid

I want to create a form based on the person table but filtering for a given campid.
In order to do that I either…
A. Manually add the campid to the person table and always fill it in on new records.
B. Use my handy dandy 1-1 relationship between person and family to get the fk_campid.

I know option B currently would not work because servoy’s filterparm’s do not support it. Still it seems like it would be better to refer to a related field than the have to store essentially the same field in multiple tables.

From a database perspective duplicate fields can be a waste but from servoy’s perspective filtering based on a related field might be to “expensive”?

16

Ok, I get the related field filter issue.

In varous threads on the forum, there’s been talk about Servoy relations becomming more diverse, like the ability to specify inner and outer joins. I think that would need to be supported to get your related field filter in place.

Regarding the multiple TableFilters on one table: I guess to see if it works, someone has to give it a try. do think that it’ll work though, since the foundsetfilter function does it as well.

And regarding the user in one security group: Yes, your logic should be different, you should store all the different groups in an Array, or something and store that in a global. And when applying the Tablefitlers, you have to loop through the Array with UserGroups and find the matching filters.

The part of duplicate entries, because of the differnet groups: You’d have to undouble them. Maybe this thread will help there: Searching for records with related (portal) records - #15 by maarten - Classic Servoy - Servoy Community

The part of conflicting filters: well, that depends on how you set it all up. Will the filters always be manually added? Does it happen often, on only when you install the solution etc, etc.

Paul

17

gte451f:
…from servoy’s perspective filtering based on a related field might be to “expensive”?

Filtering is a simple mechanism to help getting correct foundsets, If you need todo more complex filtering, use a find/search or loadRecords(SQL)

18

Filtering is a simple mechanism to help getting correct foundsets, If you need todo more complex filtering, use a find/search or loadRecords(SQL)

I’m not to lazy to go look this up myself, but for everyone following this thread… :)

Does loadRecord(SQL) or “find/search” have the filtering ability to make foundset filters semi-permanent from the user’s perspective without have to constantly re-build a found set?

19

No - it’s not permanent. You need to script it so that it will behave the way you want.

That is the concept behind addFoundSetFilterParam and addTableFilterParam.

BUT those two functions have a limited scope - so if you want to do anything outside that scope (like filter by related data, outer joins, inner joins, corrrelated subqueries, etc) - you have to make methods that will enforce that find yourself.