![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
I am new to LibreOffice, and downloaded it initially for Base. The main purpose for my database search is to create a music library, with details such as vocal parts, church season, instruments, etc. Some of these would be best suited as a multiple select option in the form, but I seem to be having difficulty creating that. Am I missing something? This is my third attempt to find a database program that will give me this option...is it possible in Base?
Thank you for your help....
Marni
Hi 
I think it helps to use an external back-end rather than the default internal one.
It sounds as though you need a few tiny tables that have a set relationship to the main table. One of the sub-tables would have just 2 columns; one for Id/key/reference-number and one that lists the option you mentioned (vocal parts, church season, instruments). Then in the main table one column would have a relationship of 1-to-many (err, i think that way around? or would it be 1-to-1?) with the key of that sub-table. On the other hand it might be easier to have a flat database (like a spreadsheet) rather than a relational one.
There is some draft documentation appearing for Base
http://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Guide
which might help.
Just out of curiosity please could you let us know what other database programs you have tried? Did you manage to build-up a table of data in any of them? If so it might be good to get Base to use that table as it's back-end as that would solve 2 issues in one go.
Regards from
Tom
Hi Marni,
I think that what you are wanting should be doable, but you're not
clear enough for me to be more helpful.
Can you give a clear example of what you want to do and then perhaps
I/we can help you out better.
Regards
Mark Stanton
One small step for mankind...
Hi Marni,
I am new to LibreOffice, and downloaded it initially for Base. The main purpose for my database search is to create a music library, with details such as vocal parts, church season, instruments, etc. Some of these would be best suited as a multiple select option in the form, but I seem to be having difficulty creating that. Am I missing something? This is my third attempt to find a database program that will give me this option...is it possible in Base?
Try looking here :
http://www.oooforum.org/forum/viewtopic.phtml?t=21159
http://www.oooforum.org/forum/viewtopic.phtml?t=27761
http://www.oooforum.org/forum/viewtopic.phtml?t=126402
http://www.oooforum.org/forum/viewtopic.phtml?t=37079
http://www.factooorpro.net/viewtopic.php?f=9&t=4909
http://user.services.openoffice.org/fr/forum/viewtopic.php?f=3&t=29806
http://user.services.openoffice.org/fr/forum/viewtopic.php?f=9&t=27622&hilit=sélection+multiple
Basically, from what I understand of the above threads, if you want to bind the multiple values selected to a database field, then this is not possible with the current multiple selection control property.
A multiple selection is only available for a free standing form control list, i.e. one in which the data is presented or displayed in a Writer form or Calc sheet, but can not be bound to a database field, unless you store the values individually in a corresponding table (n-m relationship). Alternatively, it is also apparently possible to do this using a macro to write the data from the multiple selection into corresponding fields in a bound table.
As far as I know, Base does not currently support writing to ENUM or SET data types, and most likely the control properties do not provide for that within the current UNO API (although I haven't checked).
Alex
As another poster, I would like to know: What the form is suppose
to look like. What tables, queries of views will be used? What is meant
by multiple selections? Designing the parts of a DBMS requires knowing
the details.
--Dan
Hi
A very rough draft scribbled in Writer (or even Draw or Gimp or something)
might be enough to give us a better idea of how you plan to lay the form
out. Note that if you view the link in Nabble and reply to one of the posts
then use
More - "Upload a file" - Browse
(which is about the same technique as attaching a file to an email)
Regards from
Tom
Hi Alex,
The data controls will read/write to those filed types, when the
connected database engine supports them - what the built in controls
will not do is parse the results for the GUI.
IIRC this post on one of the forums that includes a script that does
parse a set enum, with example showing how to connect the script to a
list control in multi-select mode.
http://www.oooforum.org/forum/viewtopic.phtml?t=37079
Haven't looked at that in a long time - it sounds like what the person
needs.. I'll try the script and make sure it still works, but can't
really see why it wouldn't still.
//drew
http://nabble.documentfoundation.org/file/n3658280/Music_Library.odt
Music_Library.odt
I spend my days working in a Cache database that has a user front end, and
it has been years since I have built something like this in from the bottom
up, so you are reminding me of all the things I have forgotten & never knew!
Let's see if I can answer everyone's questions....
My friend took over as choir director and I have been helping her go through
the music library. There are files and files of music - from small anthems
to full mass settings, Requiems and books of anthems. They currently have
old card files like the old school library days - one set filed by title,
one by composer, and a third by church season. The church has been through
some changes and the files are a not all kept up. We wanted to move the
directory into a database so that we could easily search for pieces
appropriate for the season, perhaps include certain instruments, etc. If I
attached the document correctly, you should see a rough draft of the type of
information we want to track. I initially built it with several fields for
Season and Instruments, but I would rather have one field and have the data
entry person enter all that apply. That way I could also query the one
place for the instrument or season I want when searching for music.
In looking for a program to do this, I wanted to look for something
inexpensive (the church doesn't have a lot of money), and/or easily exported
out if the church moved to something else. (I am doing all this myself on
my mac for now.) And I wanted to create a form that would be easy for the
volunteers to enter data, as many of the ladies are not computer savvy. I
tried to "cheat" and modify a couple different CD/DVD library tools, but
they didn't allow the flexibility for sheet music. I tried one database I
don't recall that was a no-go in the first 5 minutes (too clumsy), and
iDatabase.
I think I covered all the questions, but feel free to call me on it if I
didn't.
Thank you for all the suggestions so far, I am still working through all the
information. You're really inspired me!
Marni
Hi Drew,
The data controls will read/write to those filed types, when the
connected database engine supports them - what the built in controls
will not do is parse the results for the GUI.
Oooh, thanks for that piece of info, very handy to know. I doubt that the LO form control GUI currently supports e.g. the MYSQL ENUM 65K byte limit though, but will confess to not having tried, maybe an exercise for a lazy Sunday !
IIRC this post on one of the forums that includes a script that does
parse a set enum, with example showing how to connect the script to a
list control in multi-select mode.http://www.oooforum.org/forum/viewtopic.phtml?t=37079
Haven't looked at that in a long time - it sounds like what the person
needs.. I'll try the script and make sure it still works, but can't
really see why it wouldn't still.
Apart from any newly introduced bugs ? 
All the best,
Alex
Hi Marni,
Thanks for that.
Still looks a bit vague, but I haven't seen the form yet, I'll have a
look later.
This is basic database stuff that any database can/should do. The
effort of making them do it for end users will be the difference
between them.
As I said, unless someone else beats me to it, I'll have a stab at it
later (I'm also a musician, so that might help )
Mark Stanton
One small step for mankind...
Marni,
Hi Marni,
Thanks for that.
Still looks a bit vague, but I haven't seen the form yet, I'll have a
look later.This is basic database stuff that any database can/should do. The
effort of making them do it for end users will be the difference
between them.As I said, unless someone else beats me to it, I'll have a stab at it
later (I'm also a musician, so that might helpMark Stanton
One small step for mankind...
If I understand your application is to cross reference for example a musical piece by instrumentation required, vocal parts required, liturgical season (Advent, Lent, Easter, etc.), possibly service setting (Holy Communion, Matins, Vespers, etc.).
I would see a main table with listing each work with a unique id number assigned to it (key). The issue is how many tables will actually be needed for a good database design. Each table will have its own key with any foreign keys listed for cross referencing. The basic design rule is to enter data once and the keys establish the relation between the tables.
For a first pass at the design I would look at the data and cross referencing used in your manual card catalogue for data relationships.
Hi
Oh, i thought that in the manual boxes each piece of music would have 3 cards, one in each box.
One problem being that some pieces only had a card in one or 2 of the boxes. It might be frustrating trying to find where the holes are in the data. Moving to a database solves that as each piece of music would only have 1 card but could be viewed in each of the 3 different orderings quite easily and missing data would be easier to spot and fix.
Regards from
Tom
In general terms, Jay's given you the answer.
To be a little more specific, here's an outline.
You have a music table.
It has columns
id (some people would call it musicid), integer will probably be ok
title
composer
arranged
publisher
name of collection
file number (what's this? Is it vital?)
Instrument notes (hmmm, probably, if this pertains only and
necessarily to a specific piece of music)
You have an instrument table. It fields
id (those same people would probably call it "instrumentid"), integer
instrument
Then, to associate instruments with pieces of music you'd have an
instrumentLink table. It's fields are
id (instrumentlinkid) integer
musicid integer
instrumentid integer
Then, when you want to know which instruments are required for a
specific piece of music you ask
SELECT title, instrument FROM music
JOIN instrumentlink ON instrumentlink.musicid = music.id
JOIN instrument ON instrument.id = instrumentlink.instrumentid
And then similarly for seasons & occasions.
To come back to what I suspect was the actual thrust of your initial
post, I think ("think" being very operative here) you can have a
multi-select dropdown box, so you can scrap the (very untidy) "other"
and "additional" boxes for instruments. I "think" you can get LO to
handle setting each relevant line in such a dropdown box when the
links are already made. I "think" you'll need to write a little bit
of code to crete those links when you're setting up a piece of music.
Entirely resist the thought that some things are so fixed (seasons,
for example) that you don't need a table and you can hard code them.
Next thing you know you'll be up on a high place being shown all the
peoples of the earth.
Does that do it?
Mark Stanton
One small step for mankind...
Am 13.01.2012 06:15, Marni Wilson wrote:
I am new to LibreOffice, and downloaded it initially for Base. The main purpose for my database search is to create a music library, with details such as vocal parts, church season, instruments, etc. Some of these would be best suited as a multiple select option in the form, but I seem to be having difficulty creating that. Am I missing something? This is my third attempt to find a database program that will give me this option...is it possible in Base?
Thank you for your help....
Marni
Each and every relational database can depict many-to-many (m-n) relationships. This is no matter of Base. Simply create the usual three tables and two relations in your database. The type of relation has absolutely nothing to do with Base.
Once you have implemented your m-n relation in your MySQL, Microsoft, Oracle, PostgreSQL, HSQLDB or whatever database (see status bar of your database document) you can make the m-n relation accessible through LibreOffice forms attached to an embedded Writer document or attached to arbitrary stand-alone documents. This makes no difference since the "form wizard" for embedded forms will not help you by any means.
There is a section of Base tutorials and Base examples on http://user.services.openoffice.org/en/forum/index.php
*The* tutorial: http://openoffice.org/projects/documentation/downloads/directory/Base/Mid%20level%20Base%20tutorial
Most common misconceptions with example database: http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&t=40444