Bug 49145 - EDITING: Form-Wizard should propose listfields for foreign keys in a table
Summary: EDITING: Form-Wizard should propose listfields for foreign keys in a table
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.4.4 release
Hardware: x86 (IA32) All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Forms
  Show dependency treegraph
 
Reported: 2012-04-25 08:20 UTC by fioddor
Modified: 2017-09-28 10:39 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description fioddor 2012-04-25 08:20:09 UTC
I guess this is a feature request rather than a bug report. Please let me know if I should move it somewhere else.

Sw: LibreOffice 3.4.4 portable (from portableapps.com)

When editing the contents of a table, LibreOffice Base should help the user to edit the fields having a foreign key by showing a dropdown list, if the ammount of acceptable values is not far too big.
Comment 1 Robert Großkopf 2012-07-07 12:32:24 UTC
How do you want to create this listfields? 
The only behaviour of an automatic wizard could be: Set the key of the second table the second SQL-value and the first field of the table, which isn't the key, as the shown value in the listfields.But the first thin, that must be done by everybody, who wants to have such a function: The ralationships between the tables must be declared.
I have set the Importance to "enhancement" and the status to "needinfo".

Robert
Comment 2 Jochen 2012-07-27 16:18:54 UTC
Hi fioddor,

Please answer the questions.
Comment 3 fioddor 2012-08-16 09:13:31 UTC
(In reply to comment #1)
> How do you want to create this listfields? 
I'm describing the request from a pure user's perspective.


> The only behaviour of an automatic wizard could be: Set the key of the second
> table the second SQL-value and the first field of the table, which isn't the
> key, as the shown value in the listfields. But the first thin, that must be
> done by everybody, who wants to have such a function: The relationships 
> between the tables must be declared.

> Robert


Sure, the relations between the fields in both tables must be declared in menu Tools->Relations before the table editor is called/open.

Then, as the table editor opens/loads table tbl_userData, it could find out that its field fld_X is related to a primary key field fld_A in table tbl_masterValues.

Then, the table editor could check if the ammount of values is usable and if so, instead of using a text box it could present a drop-down list containing the valid values. A null value should also be added to the list if allowed by the relation's declaration.

No value translation is hereby requested.
Comment 4 Jochen 2012-08-16 09:57:27 UTC
@Robert:
What is your opinion: is the request reproducible?
Comment 5 Robert Großkopf 2012-08-16 13:36:39 UTC
@fiddor: That, what you describe, is an enhancement. It helps many people to create forms for more than one table. You are right: most people have problems by creating listfields, when the table has a foreign key.
I will change the title of this bug, so that everyone knows, that it should be a feature of the form-wizard in base.

@jochen: At this moment the wizard produces only forms with formatted field, textfields, datefields ... but not with listfields or comboboxes. The right SQL-code for listfileds kann be reproduced, when the relationships had been declared. The wizard uses the same way to connect a subform with a mainform. There must be a step in the wizard, where you could choose the right content that the listfield should show.
Comment 6 fioddor 2012-08-17 07:09:37 UTC
(In reply to comment #5)
> @fioddor: I will change the title of this bug, so that everyone knows, that it should be a feature of the form-wizard in base.

In fact I wasn't thinking of the form-wizard. I was thinking of the plain grid-like table data editor: I was thinking of enhancing it to become an intelligent grid, containing listboxes in some columns, plain text boxes in other columns, ...and in the future perhaps also dropdown-calendar boxes for date fields.
Comment 7 Robert Großkopf 2012-08-17 15:45:51 UTC
(In reply to comment #6)
> (In reply to comment #5)
> > @fioddor: I will change the title of this bug, so that everyone knows, that it should be a feature of the form-wizard in base.
> 
> In fact I wasn't thinking of the form-wizard. I was thinking of the plain
> grid-like table data editor: I was thinking of enhancing it to become an
> intelligent grid, containing listboxes in some columns, plain text boxes in
> other columns, ...and in the future perhaps also dropdown-calendar boxes for
> date fields.

I don't know what you mean with "plain grid-like table data editor". The only way to link one table to another with listflieds is in a form. When you think about a form there is no difference between a listfield in a tablecontrol or a form itself.

When you think about input direct in the table in the table-view (not a form) it will confuse everybody who creates tables. When there is a foreignkey, given as numberfield INTEGER, I must see this number in the table, not the text, which is connected to it. A form could hide it, not the table itself.

When you will use listfields or combofileds you have to choose a form.
Comment 8 Jochen 2012-08-17 15:56:29 UTC
Hi fioddor,

IMHO is your request neither a bug nor an enhancement but possibly a misunderstanding how base in this detail works.
What is your opinion?
Comment 9 Alex Thurgood 2012-08-19 09:44:37 UTC
(In reply to comment #6)


> In fact I wasn't thinking of the form-wizard. I was thinking of the plain
> grid-like table data editor: I was thinking of enhancing it to become an
> intelligent grid, containing listboxes in some columns, plain text boxes in
> other columns, ...and in the future perhaps also dropdown-calendar boxes for
> date fields.


Something like what is possible in MS Access or FMPro then ? 
In other words, having data aware controls in the table view or the grid control. 

I know that this has already been requested in the past (I would need to look up the bug number(s)), certainly at least for OOo, or on the previous OOo dba mailing lists, and I seem to recall that Lionel Mamane, the main base dev of the LO project had also considered that as one possible enhancement to the Base feature set.

However, I would imagine that that would be no mean feat, as it would probably require extending the current model-view-control paradigm. 

I'm not utterly convinced that this would be confusing for users, since other db UI programs seem to do OK out of having such functionality, and it is hypocritical of us to say that we would be blurring the lines between data presentation/maniuplation via the form paradigm and simple data display via the table paradigm, when a user can actually enter data directly into the table anyway. If one were to be really strict about the separative approach, a user would just be able to create a table by hand or with the wizard, and then BE OBLIGED to create a form to enter data into it. At the moment, that simply isn't the case, and certainly doesn't represent what happens in other equivalent products. 

I would say that this is a valid RFE, but IMO it is a duplicate of one that is already somewhere in the system or at least has been discussed on the dev list (or IRC) previously.


Alex
Comment 10 Jochen 2012-08-19 11:18:01 UTC
@Rainer:
what should we do with this bugreport: "WONTFIX" or "DUPLICATE" (of what) or ...?
Comment 11 Alex Thurgood 2012-08-19 12:36:46 UTC
(In reply to comment #10)
> @Rainer:
> what should we do with this bugreport: "WONTFIX" or "DUPLICATE" (of what) or
> ...?

Jochen,

IMHO you can't say WONTFIX until a dev has had a chance to say something about it, or until we can show that a decision has been made by the dev group to sort it out or ignore it.

If you want to see whether it is a duplicate, search in bugzilla or the dev mailing list (ESC minutes ??) for "data aware control" or something like that.

It is a recurring request, and has been since OOo days. The fact that nothing has been done about it is irrelevant, other than an indication that to implement the desired features requires more work than would appear on the surface.


Alex
Comment 12 Alex Thurgood 2012-08-19 12:45:46 UTC
Reading this :

http://api.libreoffice.org/common/ref/com/sun/star/form/DataAwareControlModel.html

would seem to indicate that the API only supports data awareness via the FormControlModel and the FormComponent service.

If that is really the case, then I would guess that implementing the requested feature for Table View editing would require a new API or a modified API of the MVC used for Table Views.

Alex
Comment 13 Alex Thurgood 2012-08-19 12:58:53 UTC
Also note that data aware controls in general for LO are being worked upon, albeit for Calc for the time being :

http://lists.freedesktop.org/archives/libreoffice-commits/2011-December/024675.html

+    // Need to flesh this out, currently we will only support data-aware controls for calc
+    // and only handle a subset of functionality e.g. linked-cell and cell range data source. Of course later
+    // we need to disambiguate for writer ( and others ? ) and also support the generic form (db) bindings
+    // we need some more work in xmlscript to be able to handle that


So, it is definitely do-able.


Alex
Comment 14 Jochen 2012-08-23 18:37:25 UTC
Status changed to "NEW"
Comment 15 Alex Thurgood 2015-01-03 17:38:24 UTC Comment hidden (no-value)
Comment 16 mhonline 2017-04-09 02:39:01 UTC
to solve the weakness of linking records via listbox (catchword: horizontal scrolling records to find selfexplaining keys) the requested enhancement would really be helpful. So what is the status about this?

martin
Comment 17 gmolleda 2017-09-28 10:39:01 UTC
I believe that in this subject would enter:
And Base does not know how to use ENUM and SQL SET when connecting to MySQL. Base should use a drop-down list to choose a value.
Mi kredas, ke en ĉi tiu temo eniros:
Kaj Bazo ne scias uzi ENUM nek SET kiam konektiĝas al MySQL. Bazo devus uzi malsupren-liston por elekti valoron.
Creo que en este tema entraría:
Y Base no sabe usar ENUM y SET de SQL al conectar con MySQL. Base debería usar una lista desplegable para elegir un valor.