Can I reload a form based on a parameter query?

ptoye1 · January 23, 2017, 7:24pm

I have a form which at present is based on a parameter query - the user types
in a criterion and only the relevant database rows are shown.When I press a
button a macro does various actions, including deleting the currently
selected row. After all this I want to reload the form to get the latest
version (without the deleted row). But a straight Form.Reload asks again for
the criterion, which I don't want.

Is there any way of doing this? The only way I can think of is to use a
dialogue or input box to allow the user to input the criterion and base the
query on a SELECT clause incorporating the criterion.

RobertG · January 23, 2017, 7:36pm

Hi Peter,

I have a form which at present is based on a parameter query - the
user types in a criterion and only the relevant database rows are
shown.When I press a button a macro does various actions, including
deleting the currently selected row. After all this I want to
reload the form to get the latest version (without the deleted
row). But a straight Form.Reload asks again for the criterion,
which I don't want.

Is there any way of doing this? The only way I can think of is to
use a dialogue or input box to allow the user to input the
criterion and base the query on a SELECT clause incorporating the
criterion.

You could better put the parameter-values in a form and the query in a
subform. The values in the form could be saved in a table with only
one row. Call it "filter" ore something else, primarykey is a boolean
yes-no-field. So you could type the parameter, change to the subform
and the subform will be reloaded. It will also be reloaded without
asking again for the paramter, because this is written down in the
mainform.

Regards

Robert
- --
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3

ptoye1 · January 24, 2017, 2:48pm

Robert,

That sounds great - I'm sure I should have thought of this all by myself.

But there's an added complication which I didn't mention before. When the user types in a criterion, this needs to populate a table on the form. He/she then selects a row which fills in some fields in the form. the user then has the option of changing some of them before clicking the button. So it seems that I need two levels of subform - one for the table and one for the fields (rough diagram below). Do you know if LO can handle this or will I have to experiment?

Criterion
    >
    >----Table
           >
           >----Fields

Best regards,

Peter
mailto:lo@ptoye.com
www.ptoye.com

RobertG · January 24, 2017, 4:47pm

Hi Peter,

have a look here:
http://robert.familiegrosskopf.de/lo_hb_en/Example_Search_and_Filter.odb

You don't need anything elese but a form and a subform. The example
includes different forms, some of them working without any macro.

Regards

Robert
- --
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3

ptoye1 · January 26, 2017, 11:20am

Hi Robert,

Thanks very much for the database example. A lot to look at there - I'll have to take some time to work out exactly how your forms and macros work.

Best regards,

Peter
mailto:lo@ptoye.com
www.ptoye.com

Tom_Davies1 · January 26, 2017, 2:49pm

Hi
Chapter 13 of the "Getting Started with LibreOffice" Guide may help with
understanding macros. There is an outstandingly good entire book about
LibreOffice/OpenOffice macros by Andrew Pitonyak but that probably goes
into far more detail than you need right now.
Regards from
Tom

ptoye1 · January 27, 2017, 12:36pm

HI Tom,

Thanks for this, but my basic question was whether there's an easier way than using macros I'm not frightened by macro programming (I wrote my first ever program in about 1961, and was in the profession for far too many years). Except for the difficulty in working out which method applies to which object, but that's a documentation issue.

Best regards,

Peter
mailto:lo@ptoye.com
www.ptoye.com

ptoye1 · January 27, 2017, 3:21pm

Hi Robert,

I've had a look at some of your forms now, especially filter_without_macros and I'm a bit puzzled. I see that the table in the Display part of the form is populated by the "filterquery" query, which references "filter". The table "filter" has one entry - the selection(s) I made in the listbox. But I can't see how the listbox selections get put into the "filter" table. Can you please help me there? I also can't understand why there are two columns in the "listbox_n_simple", but that's of less importance.

Best regards,

Peter
mailto:lo@ptoye.com
www.ptoye.com

drewjensen · January 27, 2017, 5:43pm

howdy,

Ah, considering that the project is actively working on the Base module at
the moment this might be a great time to open a Request For Enhancement.
In this case a request to add a ReloadWith (for example) function on the
data set (form) which would reload the data using the current parameter
variables entered on the initial load.

Just a thought and it couldn't hurt. I think your use case is common enough
that merits a discussion at least.

RobertG · January 27, 2017, 6:04pm

Hi Robert,

I've had a look at some of your forms now, especially
filter_without_macros and I'm a bit puzzled. I see that the table
in the Display part of the form is populated by the "filterquery"
query, which references "filter". The table "filter" has one entry
- the selection(s) I made in the listbox. But I can't see how the
listbox selections get put into the "filter" table. Can you please
help me there?

Could be this is a special behavior of Base-forms. There are two forms
beneath each other. One form for "filter" and one for "display". I
change values in "filter". I move to a button in "display". The
content of "filter" will be saved when I push the button in "display"
before the action of the button will happen. So there is needed only
one button, which should refresh the form "display". The query for
this form will be filtered by the entries in the table "filter".

I also can't understand why there are two columns in the
"listbox_n_simple", but that's of less importance.

Most listboxes will show some text-content, but are linked to a table
by an Integer-primarykey. So queries for listboxes look like
SELECT "name", "ID" form "table_names"
"name" will be shown. "ID" will be saved in the column of the form.

You must have a look in the properties > data of the listbox. The
"bounded field" will be '1' by default - and '1' is (a little be
confusing, because it begins with '0') the second field.

If the same content should be shown as it will be saved you could set
this to '0' - so there must be only asked for one field.

You could also set this to '-1'. This will save the numer of the row.
I have never used this, but it works ...

Regards

Robert
- --
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3

ptoye1 · January 28, 2017, 10:37am

Hi Drew,

I'm very happy to hear that Base is being worked on. It's always struck me as the Cinderella app in the suite.

Would you like me to issue a RFE or will you do it yourself?

IMHO, even more important than this would be Update and Delete queries. It's been decades since I used MS Office, but I remember them as being available in about 2000. And very useful as one doesn't have to learn SQL.

Best regards,

Peter
mailto:lo@ptoye.com
www.ptoye.com

ptoye1 · January 28, 2017, 4:45pm

Friday, January 27, 2017, 6:05:01 PM, you wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Robert,

I've had a look at some of your forms now, especially
filter_without_macros and I'm a bit puzzled. I see that the table
in the Display part of the form is populated by the "filterquery"
query, which references "filter". The table "filter" has one entry
- the selection(s) I made in the listbox. But I can't see how the
listbox selections get put into the "filter" table. Can you please
help me there?

Could be this is a special behavior of Base-forms. There are two forms
beneath each other. One form for "filter" and one for "display". I
change values in "filter". I move to a button in "display". The
content of "filter" will be saved when I push the button in "display"
before the action of the button will happen. So there is needed only
one button, which should refresh the form "display". The query for
this form will be filtered by the entries in the table "filter".
But how is the listbox connected to the table? The only connection I can see is by the name. I think it would be strange to say that a control with a name it automatically linked with at table of the same name. Also, the "ID" field in the "filter" table is set to YES, while its default is NO, which implies that something is changing it.

I also can't understand why there are two columns in the
"listbox_n_simple", but that's of less importance.

Most listboxes will show some text-content, but are linked to a table
by an Integer-primarykey. So queries for listboxes look like
SELECT "name", "ID" form "table_names"
"name" will be shown. "ID" will be saved in the column of the form.
The query is SELECT "Sys.", "Sys." FROM "searchtable" GROUP BY "Sys." ORDER BY "Sys." ASC
So both columns will have the same contents.

You must have a look in the properties > data of the listbox. The
"bounded field" will be '1' by default - and '1' is (a little be
confusing, because it begins with '0') the second field.
Some people start with 0, others with 1. It gets confusing when they're both working on the same project.

If the same content should be shown as it will be saved you could set
this to '0' - so there must be only asked for one field.

You could also set this to '-1'. This will save the numer of the row.
I have never used this, but it works ...
I've not heard of that. Thanks. Could be useful.

Regards

Robert
- --
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQIcBAEBAgAGBQJYi4ucAAoJELDKYwaoS9AITnoQAKhjm6204tHi/aeQy2N9cIZp
jzmXXg6NnQDwy6nGgSMjdoBjtFBMbwwfl76qNJMAQKtN5qigKPah3mBlnR04Yize
NWRCpFuQdYXyv6FqChXh3EdGktfS4eMwpMAgLDaey3Zcbno7tXtetQ7YlJc7iGjd
0eVEw/4Bk0TtDJ3hBLQ3aBe61RKwZnt2h+1V8rJQfvPbWPiSMNr/YznotaKIXSbJ
zLrm6u97BeIcXd5E2K+lXxdmvDHacH5zKbv6j8B3h+D3JrHo5OzG62hYpYptmuh8
dpKgC1D9Wpe3x96Of/5vz82E+csMJqk7VNrT0WgULdrPUy6oqxGoRZviKnS9QwmQ
Et8mDm2PPZGdnvcns4lIaGLwkCPU9ZRyk08Oq1aeWvLp6I5E8M1KKblEURDbMr9W
CTKJVkrEEiVj+MJux+b02GPrLCWIMv0TSoV7L8yiOQLSCb2EPh0DOsm/w3SMYo+t
qV/+nXgdwHztVMZ5F8y33CaWl6LuMMiQJCHZAIY3Wu4qEQlD668J/zVDOA+I/m7I
UG0K96RKNtXGYvf8tWidBhCLaBxaxadmmgfb0yD2r6pWipL6kBJdRWDSlOBczHJT
/BEJQixwe0RgleThXF9AO9hWepq811WOXUF7Sr/XXrIt3ojzYhVgopqHttf5ofJ3
93NMpdUU7TC9gmjU8JVp
=u2Z4
-----END PGP SIGNATURE-----

drewjensen · January 28, 2017, 6:18pm

Howdy Peter,

I'm only a user of the software. Anyone can request a change and it is
pretty easy to do.
Likely getting one of the developers to want to code it will be a bit more
of a trick, but there is a process and if no one ever enters a request it
certainly won't happen.

Just in case:
- the project uses a ticket system called Bugzilla to track defect reports
and enhancement requests found at https://bugs.documentfoundation.org
- you don't need to create an account in that system if all you want to do
is view what is in the works, but you will need to create one to open a new
request or report a bug.
- there is an explanation of the bug entry process here:
https://wiki.documentfoundation.org/QA/BugReport
-- for an enhancement the only difference is enter a severity level of
'enhancement'

Anyway, I haven't said boo here in a long while but recently have been
looking at the issue tracker and I noticed that there is some talk of
adding a 'search and replace' feature for Base which is what got me
thinking it might be a good moment. On the other hand there is a big push
to finish up the support for a different db engine so an RFE might get lost
in the noise of that..

But hey it sounds like a good idea and if you want to start the process
great, if not, I just might..

Best wishes,

ptoye1 · February 13, 2017, 3:41pm

Robert,

Sorry not to have been back sooner - I got distracted by some more important work than managing my wine.

I've worked out now how your filter_without_macros works now, and am in the process of adding the procedures into my database.

I've also found your Base handbook on the LO website. It's really useful, and a pity it wasn't there when I started working with Base (some years ago now).

So double thanks.

Best regards,

Peter
mailto:lo@ptoye.com
www.ptoye.com