[Solved] Form List-box Filtering with Multiple Users

Creating and using forms

[Solved] Form List-box Filtering with Multiple Users

Postby topherdan » Thu Apr 12, 2012 4:36 am

Hey all,

I have a few forms that use the list box filtering technique described here: http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&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
Last edited by topherdan on Mon May 28, 2012 10:15 am, edited 1 time in total.
topher

OpenOffice 3.3 on Windows XP
topherdan
 
Posts: 22
Joined: Wed Jan 25, 2012 3:19 pm

Re: Form List-box Filtering with Multiple Users

Postby DACM » Thu Apr 12, 2012 8:52 am

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.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Form List-box Filtering with Multiple Users

Postby topherdan » Thu Apr 12, 2012 2:31 pm

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!
topher

OpenOffice 3.3 on Windows XP
topherdan
 
Posts: 22
Joined: Wed Jan 25, 2012 3:19 pm

Re: Form List-box Filtering with Multiple Users

Postby DACM » Thu Apr 12, 2012 7:25 pm

DACM wrote:...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.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Form List-box Filtering with Multiple Users

Postby Villeroy » Thu Apr 12, 2012 8:17 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26142
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Form List-box Filtering with Multiple Users

Postby topherdan » Mon Apr 16, 2012 3:51 am

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...
topher

OpenOffice 3.3 on Windows XP
topherdan
 
Posts: 22
Joined: Wed Jan 25, 2012 3:19 pm

Re: Form List-box Filtering with Multiple Users

Postby Villeroy » Tue Apr 17, 2012 1:45 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26142
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests