OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Report based on >1 user-selected values for fiel

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
mousethief
General User
General User


Joined: 09 Jan 2012
Posts: 5

PostPosted: Mon Jan 09, 2012 7:59 pm    Post subject: [Solved] Report based on >1 user-selected values for fiel Reply with quote

Here's the setup: I'm looking at three tables:

Volunteer -- information about volunteers for our organization
Skill -- an inventory of skills that a volunteer could have any number of
VolunteerSkill -- a linking table containing record IDs from the other two tables if that particular volunteer has that particular skill. (typical 3rd normal form many-to-many setup)

I want the user to be able to select any number of skills from the Skill table, and bring up a list of all of the volunteers who have that skill listed. I do not want the user to have to type in the skills because some of the descriptions are pretty clunky.

Any help would be greatly appreciated.
_________________
thanks,
MT


Last edited by mousethief on Wed Jan 11, 2012 9:24 pm; edited 2 times in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Jan 10, 2012 3:57 am    Post subject: Reply with quote

Add a new table, say "Filter" and one integer primary key.
Create a form with a grid control and a list box column within.
Enter the skill IDs by the skill description.

Run a report based on:
SELECT "this","that" FROM "various tables","Filter" WHERE "Skills"."ID"="Filter"."INT"
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Tue Jan 10, 2012 4:20 am    Post subject: Reply with quote

Quote:
I want the user to be able to select any number of skills from the Skill table, and bring up a list of all of the volunteers who have that skill listed. I do not want the user to have to type in the skills because some of the descriptions are pretty clunky.

Welcome to the forum.

A suitable answer will essentially depend on your familiarality with Base and in particular with its graphical user interface. I suggest one method you might consider would be to add an extra (boolean) field to the "Skill" table that can act as a flag whereby only those records are selected from the "VolunteerSkill" table where the 'SkillID' matches any (none, one or several) record(s) that have its flag set. A button will be required will be required to update the second data form, whose source is an SQL SELECT statement, whenever there is a change to any of the flags. An example may be more helpful than my inadequate description, so I have adapted a crude demo file where you will need to substitute 'Skill' for 'Trips' and 'Volunteer' for 'Clients'. You should be able to download demo file from here. The third form document, "SelectTrips", in the demo file illustrates the suggested method.

Other contributors to the forum more skilled than I, will hopefully offer you a better answer. Unless you have good experience with the OpenOffice API, I would strongly advise against resort the any method that required use of macros.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
mousethief
General User
General User


Joined: 09 Jan 2012
Posts: 5

PostPosted: Tue Jan 10, 2012 7:54 pm    Post subject: Reply with quote

Thanks to both of you! I will play around with what you've said / sent, and see what I come up with.
_________________
thanks,
MT
Back to top
View user's profile Send private message
mousethief
General User
General User


Joined: 09 Jan 2012
Posts: 5

PostPosted: Wed Jan 11, 2012 9:22 pm    Post subject: Reply with quote

I went with the idea of adding a boolean check-box field to Skill -- I called it SkillSelection -- and putting that in a table on a form. Under the table I created a push button and mapped a report to it using a macro, so the user can click what skills they want, and press the button, and the report creates. I have some questions about the content of the report but I'll open a new thread for that.

Thank you! A very elegant solution.
_________________
thanks,
MT
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group