Bug 103557 - Add syntax to reference form input boxes from queries (pass values)
Summary: Add syntax to reference form input boxes from queries (pass values)
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
Hardware: All All
: low enhancement
Assignee: Not Assigned
Depends on:
Blocks: Database-Forms Database-Queries
  Show dependency treegraph
Reported: 2016-10-28 13:34 UTC by edonkey2001-libreoffice
Modified: 2017-11-02 22:10 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description edonkey2001-libreoffice 2016-10-28 13:34:55 UTC
There's currently no way I know of of directly passing the value one can input in, for instance, a calendar date picker in a form, to a query.

Suppose I have a series of events, each one having a Date field.
Now I want to query for all events happened between 2016-01-01 and 2016-12-31.
I can write a query with :parameters so, when I run it, a dialog window appears to ask me to write the values I want to use as parameters.

But I want the user to supply the start date and end date in a calendar picker, like the one you can have in Forms, where you select Dropdown Yes and get a calendar-like interface.

So I don't want to use :parameters anymore, because doing so would bring up the window asking to enter dates in a text field.

What I want to do is to write a query where I say to select all the events where the Date is between the two values I specify in the form calendar field.
But currently I see no way of directly referencing the value of a form input box in a SQL query (the fact that you can't do it in the GUI is probably another separate enhancement request).

From what I've read, MS Access should have a similar feature which syntax is [Forms]![Form1].[field]
I'm proposing to add this, or a similar syntax, in Base.
There should be another solution, from what I've read, involving a filter table and so, but I find this method to be simpler from a user perspective.

This way, ideally, I select all the events happened between [Forms]![Form1].[startdate] and [Forms]![Form1].[enddate] in my query, then create a form based on this query, and finally simply add 2 calendar dropdowns called startdate and enddate. When I open the form, I fill in the dates and somehow update the underlying query.
Comment 1 Robert Großkopf 2016-10-28 20:05:14 UTC
At this moment you could
1. Create a table "filter" only for one row with 
ID - BOOLEAN, primarykey
date_start - DATE
date_end - DATE
2. Put in the first value for ID: TRUE
3. Create a form for this table with
(there you could choose the dropdown you wish)
4. Set this form by the filter to show only the values for ID = TRUE
5. Set the cycle of the form "only this row" (don't know how it is called in English GUI)
6. Add the query to a form beneath this one - mustn't be a subform.
7. Add a button to this form, which should refresh the query.

That's the way to get what you want.

You might get the same result with macros - but this one works without and you could also get the content of the last input when only opening the query, because the values of your table would be read out of "filter"
( ... WHERE "start_date" = IFNULL(SELECT "date_start" FROM "filter" WHERE "ID" = TRUE), "start_date") AND "end_date" = ...)
Comment 2 Alex Thurgood 2016-11-03 09:45:17 UTC
@edonkey2001 : forms and queries are separate objects in the MVC paradigm of LibreOffice - queries via the GUI have only a limited number of interactions and a date control is not one of them, however that is not what you are requesting here if I've undersood correctly.

What you want if I've understood correctly is for the values of date controls in a form, to be passed to a query, although you don't specify where that query might be - currently that is only possible by the workaround suggested by Robert, or else via macro using the UNO API.

I shall confirm your RFE but I have no idea whether in the current scheme of things what you are asking for is remotely possible or even desirable. For that, a developer would be required to give their input.