| View previous topic :: View next topic |
| Author |
Message |
mousethief
General User
Joined: 09 Jan 2012
Posts: 5
|
 Posted: Mon Jan 09, 2012 7:59 pm Post subject: [Solved] Report based on >1 user-selected values for fiel |
 |
|
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 |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue Jan 10, 2012 3:57 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
Arineckaig
OOo Advocate
Joined: 01 Mar 2004
Posts: 331
|
| Posted: Tue Jan 10, 2012 4:20 am Post subject: |
|
|
| 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 |
|
|
mousethief
General User
Joined: 09 Jan 2012
Posts: 5
|
| Posted: Tue Jan 10, 2012 7:54 pm Post subject: |
|
|
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 |
|
|
mousethief
General User
Joined: 09 Jan 2012
Posts: 5
|
| Posted: Wed Jan 11, 2012 9:22 pm Post subject: |
|
|
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 |
|
|
|
