[Solved] Form List-box Filtering with Multiple Users

[topherdan]

Hey all,

I have a few forms that use the list box filtering technique described here: http://user.services.openoffice.org/en/ ... 00&t=42845
They are all working correctly, however, I'd like to extend the use of this database to more users, who will all need the filtering capabilities. As my database is designed now, filtered values are stored in a single-row table called "ClassFilter", which is linked to a table in a form (through a query). I'm worried that if one user filters the form (for example, selecting "Intermediate" in the class filter) and then another user concurrently tries to filter the same form with a different value (for example, selecting "Advanced") then any refresh of the form by the original user will result in a different record set being displayed.

My original plan was to add more rows to the filter table, as well as a User Name field, and another filter clause in the query that is something like WHERE "ClassFilter"."User Name" = CURRENT_USER. The problem I'm running into is that Base doesn't know to enter filter data to the record with the current user name stored. I'm thinking this may require a macro to move the record pointer whenever a form is opened? Alternatively, I noticed something in the HSQLDB documentation about temporary tables, but the explanation is a bit lacking - can I make a temporary filter table for each user every time they connect to the database?

Thanks for any help you can provide!!
Chris

[DACM]

Chris,
Very interesting. You're on the right track, and I agree it will take a multi-line permanent FILTER table with reference to CURRENT_USER. I presume this can be accomplished without Macros, but I can't be certain at this point. I mean, if the ParentForm is based on an 'SQL command' based on the FILTER table, with a CURRENT_USER clause and corresponding field, then I don't see how the record pointer would even be a player since the CURRENT_USER record would be the only record available to the data-Form (in theory). Otherwise, I'm not sure how to bind Forms to temporary (memory only) tables. BTW, you're describing a multi-user environmnent with concurrent read/write access. Is that your current setup? Have you setup USERs/ROLEs in HSQLDB? Just curious.

[topherdan]

I think that's the answer, actually, my forms with the filters in them aren't filtered by username - they're just associated with the filter table itself. Restricting that by associating the form with a query instead of a table should do it. I won't have a chance to work on it for a few days but I'll let you know how it goes...

Thanks for the insight!

[DACM]

...Have you setup USERs/ROLEs in HSQLDB? Just curious.

Sorry to quote myself, but I'm 'just curious' because this multi-line FILTER-table approach would require an additional administrative step to ensure newly-created users get a corresponding record established in the FILTER table. This additional step could be avoided using a Form macro that checks for the CURRENT_USER in the FILTER table, and adds a dedicated user-record if necessary as the Form is loading. But if you're willing to get into macros, then List Box filtering could likewise be coded, thereby eliminating the need for a FILTER table and the associated admin.

[Villeroy]

I administrate such a database since 1 year. There are 8 users and 4 sets of filter criteria so the filter table amounts to 32 rows with a primary key on both fields user_name (varchar) and FilterID (int).
Actually my 8 users represent client machines. The users constantly move from one working place to another and many persons use the same form which is loaded 12 hours on the same machine. So the logged-in current_user represents the machine and the human users pick their initials from a list box. I keep a separate list of human users with short initials and a boolean to disable retired persons. For a small business this is very well maintainable without any macros.
The database has one macro for default values which inserts the last used person-IDs into new records until it is overwritten by the next person on the same machine.

[topherdan]

Villeroy, when you're adding new users/machines, how do you ensure that there is a record in the filter table with that username (to check against CURRENT_USER)? I currently have a form that can add and delete users with buttons on it that writes/erases records in the filter tables with the appropriate user name values, but maybe using a macro that checks every time the form is used is safer. What would that macro look like? I'm not a super-experienced macro programmer but I've managed to make a few of them work...

[Villeroy]

We don't hire&fire new machines nor employees on a daily basis. So I don't feel urged to automatize this administration effort. When a new machine arrives I'd type one row per filter into my filter table.
Having 4 filter forms bound to criteria rows 0 to 3 and a new machine/user logging in as "Back Office" I would enter the following 4 rows
FID CUSER Criterion1 Criterion2 ... ...
0 Back Office
1 Back Office
2 Back Office
3 Back Office
That's it.