Okay. I have an idea and I'm not sure if this even possible.
The Situation:
- There is an "USERS"-Table, which contains:
- "USER_ID" <-- UserName
- "FILTER_ID"
Currently there are 10 "Data"-tables. Every table has defined filters based on the User-ID of each of the currently 138 users.
Some users have simple filters like
If (CurrentUserName() == "123"){AddFilter($filter,"FILTER_ID= '123");}
And others have multiple filters like
If (CurrentUserName() == "456"){AddFilter($filter,"FILTER_ID= '123' OR FILTER_ID= '456' OR AMT = 'FILTER_ID' OR AMT = '789'");}
So if there is any change needed, it's very complicated and there is a high chance of making something wrong.
To simplify this and also to move the job from my side to the client side, I'd like to create an new login with a seperate page, only for maintaining the filters.
The Idea in detail:
- I'd extend the "USERS"-Table with 10 additional columns. Each column for one "Data"-Table ("Datafilter 01", "Datafilter02", "Datafilter03", etc. pp.)
- The new Edit-Page should give then the possibility to choose one item from a dropdown (Lookup the Filter_ID) and adding the selected value to the desired Datafilter-Column by clicking on an "Add" button"
- Maybe it'll be neccesary to have seperated Edit-Pages for each column?
- After choosing and adding one ore more items and clicking on "Save", the value(s) will be written in the database. If more than one item has been chosen, each item should be separated by a delimiter (comma?)
- In the code-section (Table-specific --> Common --> Recordset Selecting) of the each table I'd like to check the current username and automatically apply the in the "Datafilter0*" defined value(s).
- especially when there are more than one values, I'd need to get and separate them to fit in the code.
With this solution, the users won't be able to enter wrong values and thanks to the dynamic filter settings, it won't matter how many entries there are.
Also updating the filters will make the re-generate of the form obsolete because the filter values are stored in the database.
I hope the idea is clear enough and also somehow possible...
If not, I've an simplified backup-idea which is not the best, but it'll work.