In the data properties of a text box you can define “Filter proposal” which turns the text box into a list box while in form filter mode.
Yes, the form based filter is awkward but useful. It accepts normal wildcards instead of regular expressions.
And then there is the magic filter where you simply select something and click a button to filter by the selected item.
So called “power filtering” also works with dBase. This filters a data form by a criteria form. Quite advanced but possible.
And if you have one of these form layouts where you only view and edit one record at a time, the last button on the toolbar turns on an additional table view on the same input form, analog to the data source window but with all form controls in place.
@Villeroy I’m not doubting your answer.
.
Base query is so lame. A query is a grid, like a spreadsheet, so you shouldn’t need a form to view, sort. filter, edit etc. As for data constraints, that should be part of table design.
So how is a table different? No form there either? A Query is a result set. It is up to the user as to how it is used.
My point is the grid can be used for input as well as output, so it’s a basic form with the table constraining the data and the query the presentation.
HUH?
So is the table.
Sorry - don’t get your point. I must be using queries all wrong.
No reason you would have to use all possible functionality. Discussion sits better at Attributes of Queries in Base SQLite Database
I give up - your comments make no sense to me.
Edit - quote corrected - still no sense!
1 Like
Classic misquote! Like from me.
Forms have a lot more to offer than the raw data grids. You can save them as separate documents outside the Base container. I have integrated some of them in spreadsheets, so I can enter new data and then refresh calculated scenarios and pivot tables with a single click. Calc does even adjust formula ranges automatically since the size of an imported record set is always known.
In case of a relational database you can not use the raw table/query for anything because most of the tables would contain references (ID numbers) pointing to rows in other tables. My simplistic dBase table can be edited in place but without calendar control, without auto-complete by combo box.
P.S. just have a look at this guy: [Solved] Date field changing (View topic) • Apache OpenOffice Community Forum
He created an input form 15 years ago and at the beginning of this year he ran into trouble because back then he set a max. date value 31 Dez 2020 in that form. After 15 years of using the form he completely forgot how to edit a form. This is how things should work.
My experience with MS-Access is this isn’t always the case. It depends on table and relationship design, and have always been able to edit tables with no relationships through a query.
Why? If you know your data it can be fast and convenient, even if you need to add a record in the table with the key before the data of interest. With referential integrity a delete deletes all related records.
MS Access is a billion Dollar project. Base is a tiny addition to an OSS application with a footprint of 40 MB incl. several drivers.
What you are missing is called “lookup fields”: The Access Web - The Evils of Lookup Fields in Tables
@Villeroy your point is well made on inferring anything about Base based on experience from MS-Access.
Rearrange the data. Change the order of the columns on the sheet. The form will display what is on the sheet.
-
New button (guide): fill the record (table row cells) with the form fields contents and jump to the next record (?) or add a new record in the bottom of the table.
The action of the New button seemed illogical to me. There should be a transition to a new record, but this button first works as Next. If you go to a new record (use the scroll bar for a large list) and enter it, and then go to the previous one, the new record will not be saved, which is also incorrect (contradicts the principle of working with a record in the database). It is necessary to press the New button again or the Enter key.
Who came up with this? - If a certain record is selected on the sheet, it should be displayed when the form is opened. But the first record always opens. And how do I go to a new record without a mouse? Press and hold the Enter button. Wait… In this case, the same New button will be used with repetition.
It all looks unfinished.
Edit: Perhaps it is better for the questioner to stay on the sheet and use freezing of columns and rows for the convenience of scrolling the range.
Also be aware that there is an open bug → tdf#58062
Yes, indeed. I couldn’t display more than 6 fields.
data-form.ods (10.9 KB)
Conclusion: the service has limited use for small lists when it (the service) is not required.
Version: 7.2.0.4 / LibreOffice Community Build ID: ce769e3009755dcf0082844e386f5dca4c8ecb2f CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3 Locale: ru-RU (ru_RU.UTF-8); UI: en-US Calc: threaded
Depends upon system and display - I got around 16. Also you can enter into other fields but cannot see what you are entering.
Version: 7.2.1.2 / LibreOffice Community
Build ID: 87b77fad49947c1441b67c559c339af8f3517e22
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
I believe the window initially shows 6 fields, but you can enlarge the window.
You’re right. The display of fields is limited by the height of the screen (in my case, more than 16). There is no scrolling for the fields, however.
I don’t want to just change the order the columns are listed in the Form, I’d like to rearrange the Form. For example have column A in the form displayed next to column B, not above or below it.
Then the solution above is from @Villeroy. But you will have to deal with something additionally (LO Base). What I dealt with, I did for the first time, and everything worked out. Go ahead.
The fields in the Base form can be moved freely in layout mode.
The data form in Calc is not subject to change.
And here’s something else. The solution from @Villeroy looks quite reliable, despite the complaints about the work of Base.
A fragment of the form. The fields can be easily moved when designing the form.
You will have 2 files: data.dbf (LO Calc file saved as dBASE) and data-form.odb (LO Base file).
Small demo: dBase
Extract the zip.
Open the database document dBase.odb.
Open the form therein.
You can navigate with the push buttons I have added, with the attached navigation bar and with the floating toolbar. You may also remove, rename rearrange any of the form controls. The data are separated from the user interface.
You can easily sort, enter data, use the grid view, filter, filter by year and month.
This demo only scratches the surface of Base with one simple dBase table saved from a spreadsheet.
The design may be difficult but you can use such things easily for many years without loading everything into memory, without file format trouble, without dragging around formulas.