[Solved] Query with user input and code formatting

Creating tables and queries
Post Reply
BrianME
Posts: 18
Joined: Sun Jan 03, 2021 9:30 pm

[Solved] Query with user input and code formatting

Post by BrianME »

I'm relatively new to databases and OpenOffice Base. I've learned a lot from this forum including how to get user input into a query. I haven't seen this topic directly discussed but have seen mentions of it.

I've written a query that has user input that I want to be able save in a readable and commented format, but to run it the comments get deleted and the formatting changes back to "run-on sentence" code.

Since the code formatting is preserved in SQL direct mode but user inputs don't work in that mode, my workaround is to open the query in "Edit in SQL View", de-select the "Run in SQL directly" button, save it but DON'T CLOSE the window. Then I go back to the main database window and run the report that uses this query. Everything works, it asks for the user input and the report looks as it should. Then I go back to the query window, re-select "Run in SQL directly" button and save it. Then I can close the query window and all the comments and formatting are retained. The only exception is you can't use /* Comment */, only -- Comment.

I'm hoping someone can show me the error of my ways and tell me how to get code formatting AND user input in a query without going through the extra steps of opening the query, etc.
Last edited by BrianME on Thu Jan 07, 2021 5:32 pm, edited 1 time in total.
OpenOffice 4.1.7 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query with user input and code formatting

Post by Villeroy »

Use an input form with a main form and a subform where the subform's parameters are substituted by the parent form's stored values. Today's database with filter form from topic viewtopic.php?f=20&t=104073
Search this forum for "power filtering"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
BrianME
Posts: 18
Joined: Sun Jan 03, 2021 9:30 pm

Re: Query with user input and code formatting

Post by BrianME »

Villeroy -
Well, I downloaded your sample database and took a quick look. Wow, there's a lot for a database newbie like myself to figure out and comprehend. It seems like an awful lot of work to create a form with extra queries and tables just to get a simple input or two from the user since a query input already does such a good and simple user input.

I'll take your word for this working (since it's not obvious with none of your queries being formatted or commented) and mark my post as solved even though I'm far from figuring it all out.

If there's a wish list for future enhancements, I'd sure like to see query formatting with user input on that list.
OpenOffice 4.1.7 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Query with user input and code formatting

Post by Villeroy »

You know the built--in filtering tools?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
BrianME
Posts: 18
Joined: Sun Jan 03, 2021 9:30 pm

Re: [Solved] Query with user input and code formatting

Post by BrianME »

I've used a filter to find a particular record for a form I use, but that's about it.
OpenOffice 4.1.7 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Query with user input and code formatting

Post by Villeroy »

On the form's navigation toolbar you find buttons for a so called "auto filter" and a "form based filter".

Autofilter: Select a value and click the button in order to see more of the same value in the same field.

Form based: The button switches the form into some kind of "criteria input mode" with a small additional toolbar. Enter search criteria, click the "Apply" button and see the filter result.

The last button on the navigation toolbar opens a docked window with an additional grid view on the form. On top of that view there is a toolbar button opening a dialog based filter tool similar to Calc's standard filter.

On the navigation toolbar there is a button to turn the last applied filter of any type on and off and a button to delete all filter criteria. The sort buttons are self explaining, I think.
-------------------------
All the "power filtering" examples on this forum (including the one I've linked yesterday) promote a self made solution to make form controls usable for direct input of filter criteria into a separate table exclusively made to take filter criteria. These criteria stored in a separate table can be used in many ways.
Quick recipe: viewtopic.php?f=39&t=103782&p=502336#p502336
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply