Filter form with combobox

Discuss the database features
Skierarc
Posts: 9
Joined: Fri Nov 20, 2009 1:59 am
Location: Rochester, NY, USA

Filter form with combobox

Post by Skierarc »

Hi Everyone,

At work we are trying to create a database that will keep track of customer info and projects. I have a separate form for entering project and customer info. They all work just as I want them to, but I want to be able view the record indicated by a combobox value.

I've searched around a lot, but I couldn't find anything that really suited my needs, which are simple. From what i've seen filtering would be the simplest method, but I don't know how to call a combobox value into the SQL command

The SQL command is rather long because there are so many fields on the project form, so I shortened it for less confusion.

Code: Select all

SELECT "ProjectName", "ProjectDescription" FROM "Projects" WHERE "ProjectName" = 'Project A'
So I want to change 'Project A' to something like combobox.value (VBA). Is this possible?
Windows 7 X64 - OpenOffice 3.1
User avatar
voobase
Volunteer
Posts: 97
Joined: Tue Jan 15, 2008 3:07 pm
Location: Australia

Re: Filter form with combobox

Post by voobase »

Hi there,

I don't think that feature exists in base yet. Hopefully one day. Until then there are still a couple of ways to do what you want.

The first involves creating a "dummy table", purely for holding the boundfield value from the list box or combo box you use. A dummy table is a table of just two fields, a PK field and a data field that will hold the boundfield value. You then base your mainform around this table and put the combobox in that form, linking its boundfield to the second data field. The form you want filtered you just hang off the mainform as a subform. You do a join between the two forms from the field that holds the combobox's bound value to the field in your subform you are filtering by. A very simple commit and reload macro is required to reload the subform when ever you make a selection. Further details here... http://www.oooforum.org/forum/viewtopic.phtml?t=72134

The second way would require a bit more macro code and would actually change the select statement your form is based on as per the bound value from the combobox, sort of similar to what you are trying to achieve, but the whole statement would need to be programatically changed and then the form reloaded. I have not tried this but it should work. The code would be similar to that which we write when wanting to use one listbox to narrow the contents of a second listbox, except we would be changing the datasource statement of a form rather than of another listbox. You would still need to use a dummy table to hold the boundfield of the combobox but a second mainform could be used to accomodate the dummy table. I'll see if I can fashion a quick example database to show how this would be done.
(Edit. You don't need to use a dummy table if you are using a combobox, as you can use the CurrentValue property instead. If using a listbox and wanting to use its separate bound field to filter with, it's boundfield has to be put somewhere so a dummy table comes in handy.)

Example...

First change the content type of your MainForm to SQL Command and fashion an appropriate statement by pressing the [...] next to content. Write the statement down because you will need it for the macro code below.

Next create a dummy table as discussed above (non auto incrementing). Go to the table in the table editor and fill in the ID field for just the first row by putting in a 1

Create a second MainForm in your form called "MainForm2" and base it on your dummy table.

Making sure you have the wizard button from the "FormControl" toolbar pressed, draw a ComboBox on your form. If it is drawn in the MainForm rather than MainForm2 form then you will need to move it with the "FormNavigator" and edit its properties to leave its boundfield in the dummy tables field.

Copy the code below into the macros area of the database and customize any names and the SQL statement.

In the ComboBox properties, go to the events tab and make the ItemStatusChanged event point to the "Combobox_Limit_Form" macro.

Code: Select all

sub Combobox_Limit_Form( oEv as object )

dim oSubForm As object
dim oControl as object
dim new_list_sql(0) as string

	oMainForm2 = oEv.Source.Model.Parent Rem... MainForm2
	oControl = oEv.Source.Model

	if oControl.CurrentValue <> "" then
 
	oControl.commit Rem ... Commits current value to boundfield            

	Rem... Calls the LimitItems sub and passes the Form event object and the boundfield value of the selected item.
	limitItems( oMainForm2, oControl.BoundField.String )

	End if

End sub



sub limitItems( oMainForm2 as variant, oControlBoundfield as String )

dim oMainForm as variant
	  
	oMainForm = oMainForm2.parent.GetByName("MainForm")

	oMainForm.Command = "SELECT * FROM Names AS Names WHERE Surname = '" & oControlBoundfield & "'"

	oMainForm.reload() Rem loads the the MainForm so as to display the value selected in the combobox
	
end sub
Here is a link to an example database called Surnames3. It will work only in OOo V3.1 or greater due to the macro code being included inside the database.
Surnames3.odb
Surnames3 Example Database
(14.33 KiB) Downloaded 1700 times

Cheers

Voo
Last edited by voobase on Sun Nov 22, 2009 7:02 am, edited 1 time in total.
OOo 2.3.X on MS Windows Vista
Skierarc
Posts: 9
Joined: Fri Nov 20, 2009 1:59 am
Location: Rochester, NY, USA

Re: Filter form with combobox

Post by Skierarc »

Wow thanks for all the info. It's gonna take me awhile to figure out those steps, but it makes sense.

I tried your file, but changing the combobox didn't do anything. I'm using OOo3.1
Windows 7 X64 - OpenOffice 3.1
User avatar
voobase
Volunteer
Posts: 97
Joined: Tue Jan 15, 2008 3:07 pm
Location: Australia

Re: Filter form with combobox

Post by voobase »

I tried your file, but changing the combobox didn't do anything. I'm using OOo3.1
Try closing down the form and opening it up again. I noticed that sometimes the combobox did not have a list and think there might be a intermittent bug in there somewhere. Also check your Macro Security in Tools>Options>Security>MacroSecurity. It needs to be on Medium or Low for the macros to work.

I've just modified the example a little. If using a Combo Box you don't necessarily need to use a dummy table. On the other hand if using a List Box and wanting to take advantage of its separate bound field (to what it displays) then you would need to use a dummy table (as it has to bind its bound field somewhere). I've also simplified the macro a little...

Code: Select all

sub Combobox2_Limit_Form( oEv as object )

dim oMainForm as object
dim oMainForm2 as object
dim oControl as object

	oMainForm2 = oEv.Source.Model.Parent Rem... MainForm2 for Dummy table
	oMainForm = oMainForm2.parent.GetByName("MainForm")
	oControl = oEv.Source.Model

	if oControl.CurrentValue <> "" then

	oControl.commit Rem ... Commits current value to boundfield            

	oMainForm.Command = "SELECT * FROM Names AS Names WHERE Surname = '" & oControl.CurrentValue & "'"

	oMainForm.reload() Rem loads the the MainForm so as to display the value selected in the combobox

	End if

End sub
Surnames4.odb
Surnames4 Example Database
(14.57 KiB) Downloaded 1056 times
Cheers

Voo
OOo 2.3.X on MS Windows Vista
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Filter form with combobox

Post by Arineckaig »

So I want to change 'Project A' to something like combobox.value (VBA)
FWIW you could try a method that is closer to the VBA route. Instead of using a CombBox in the main form use a Grid/Table control that displays a Text Box column. Selecting an entry from this pseudo list box can then display the required record in a suitably linked sub form. Thus the main form can act as either an index or a filter to the records that are to be available in the sub form.

The main form's grid/table control can also be supplied by data from a different table to that of the sub-form provided the two tables are related. In that case the linking field should also be included as an extra column in the grid/table control if it differs from the column that is displayed for making the selection - such as a Name and its related ID - but the ID column can be hidden if preferred. The effect is then similar to that of a Listbox control.

This use of a pseudo list box does not require a macro and can serve as a staging point when migrating from MS Access.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Skierarc
Posts: 9
Joined: Fri Nov 20, 2009 1:59 am
Location: Rochester, NY, USA

Re: Filter form with combobox

Post by Skierarc »

I guess my problem is this "Mainform" and "Mainform2" business. I'm not sure what you guys mean by subform and mainform. I'm going to attach my database file, but I don't know how to attach my macros to the database just the "My Macros". So this is the macro I am using, I have a open form macro in here too.

I really appreciate your help with this, but I'm missing a little something here.

Code: Select all

REM  *****  BASIC  *****

function OpenForm( formContainer as variant, oConnection as variant, sFormName as string) as variant 
Dim aProp(1) As New com.sun.star.beans.PropertyValue 
aProp(0).Name = "ActiveConnection" 
aProp(0).Value = oConnection 
aProp(1).Name = "OpenMode" 
aProp(1).Value = "open" 
OpenForm = formContainer.loadComponentFromURL(sFormName,"_blank",0,aProp()) 
end function 

function getFormsTC() as variant 
getFormsTC = thisComponent.Parent.getFormDocuments 
end function 

function getConnectionTC() as variant 
getConnectionTC = thisComponent.Drawpage.Forms(0).ActiveConnection 
end function 

sub OpenForm_Customers( oev as variant ) 
sFormName = "Customers" 
OpenForm( getFormsTC, getConnectionTC, sFormName )
end sub 

sub OpenForm_Projects( oev as variant ) 
sFormName = "Projects" 
OpenForm( getFormsTC, getConnectionTC, sFormName, command )
end sub 



sub Combobox2_Limit_Form( oEv as object )

dim oMainForm as object
dim oMainForm2 as object
dim oControl as object

   oMainForm2 = oEv.Source.Model.Parent Rem... MainForm2 for Dummy table
   oMainForm = oMainForm2.parent.GetByName("MainForm")
   oControl = oEv.Source.Model

   if oControl.CurrentValue <> "" then

   oControl.commit Rem ... Commits current value to boundfield            

   oMainForm.Command = "SELECT * FROM Projects AS Projects WHERE ProjectName = '" & oControl.CurrentValue & "'"

   oMainForm.reload() Rem loads the the MainForm so as to display the value selected in the combobox

   End if

End sub
Attachments
Projects.odb
(32.9 KiB) Downloaded 807 times
Windows 7 X64 - OpenOffice 3.1
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Filter form with combobox

Post by Arineckaig »

Code: Select all

I'm not sure what you guys mean by subform and mainform
Merely as an example I have attached your file with no changes other than the addition of a form document called "ProjectsAdj". You will see that in this form document I have added an form called "IndexFrm" with a Grid/Table control with a single column. I have made your original "MainForm" a sub form of the "IndexFrm". I suggest you open this "ProjectsAdj" form document in Edit mode and examine the Form Navigator to examine how the two form are related and the relevant properties of the "IndexFrm" and its components. I have left the rest of the from document unchanged even though the sub form is now improperly named and some of its components could be redundant. You will also see that no macros are required. Selecting a name from the added control will display that record in the sub form.
Attachments
ProjectsAdjusted.odb
(43.88 KiB) Downloaded 1055 times
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Skierarc
Posts: 9
Joined: Fri Nov 20, 2009 1:59 am
Location: Rochester, NY, USA

Re: Filter form with combobox

Post by Skierarc »

Oh wow thats perfect, thanks. There are some bugs I need to work out now, but this is a great start.

[Update] I just added the table control to my customers form, works perfect thank you very much for all your help. No macro = yay!
Windows 7 X64 - OpenOffice 3.1
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Filter form with combobox

Post by Arineckaig »

At the risk of extending gratuitous (even if not erroneous) advice, I offer some further comment on the relationship in OOo Base between a form and its sub forms to explain how the former can be used as an index or as a filter to the records displayed in the latter.

The Base Help file includes this explanation:
“The subform is based on an SQL query; more specifically, on a Parameter Query. If a field name is entered in the Link master fields box, the data contained in that field in the main form is read to a variable that you must enter in Link slave fields. In an appropriate SQL statement, this variable is compared to the table data that the subform refers to. Alternatively, you can enter the column name in the Link master fields box.”
This extract from the help file may not be too explicit, and at risk of introducing further confusion I offer the following:

As I understand it, an SQL SELECT query is applied to the sub form that has the effect of a WHERE clause based on the current content of the Main Form's Link Master Field. That content is used as the parameter for the WHERE clause so that only those records are shown in the Sub Form whose Link Slave Field matches this parameter.

In effect the SQL query has the following format:
SELECT [RequiredFields] FROM [SubformDataSource] WHERE [LinkSlaveField] = [ContentOfLinkMasterField]

Hence the Table/Grid Control in the Main Form can be used as an index to the sub form where there is only one matching record, or as a filter where there are several matching records. In the latter case the data source for the Main Form is best supplied by an SQL SELECT DISTINCT query.

This relationship in Base between a Form and its Sub Form permits considerable flexibility. For example, the content of the Main Form can be supplied by a different data source from that of the Sub Form provided the respective Link Fields can match. Similarly the Main Form can hold more than one column so that one field/column is displayed while another (hidden) field/column is used as the Link – in effect, a pseudo List Box. Furthermore several levels of Link Fields are provided by the GUI so it is even possible, but not necessarily advisable, to design a Main Form to filter a Sub Form which in turn filters a Sub Sub Form down to four generations without resort to macros.

Hopefully others more skilled than I will correct the errors and improve the clarity of this submission. Moreover I must stress that use of the form/sub form relationship is only one method of indexing or filtering. Depending on the circumstances other methods such as the use of a dummy table, macros or even the from document navigator bar are more suitable.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
mhatheoo
Posts: 17
Joined: Fri Nov 27, 2009 12:46 am

Re: Filter form with combobox

Post by mhatheoo »

==> Arineckaig
but I hope you do not stop to continue in finding a solution - right?

I have the same "question" - so far unanswered, you may find it in here, when searching for "Lookup fields", but finally a diffent approach, so I like to explain some guesses how it should work - as a workflow, as I make concepts, I am no programmer, and even that idea is not mine, it is the rebuild of something quite famous - in DOS and about 25 years ago.

MOSTLY when the situation happens as described, one is in the situation to add records in a scenario where
-- the actual file is sort of a detail-table (the n:-side of a relation)
-- and you are looking for something related in the master-table (the 1:-side)
For the record one is actualy dealing with, it is the establishing of a one:one-relation based on equality between
- a field-content in the target (or detail)-table
- and the source-(master)-table.
To establish the equality the condition is MATCH, meaning, the "search-value" in the form related to the target should MATCH to the appropriate Value in the source-table.

So my story goes like this (if this could made possible, it would be great):
HOW CAN I CHOOSE FROM THE MASTER-TABLE?
"Enter the search-value and hit ENTER" and see what happens:

a) MATCH: nothing happens: criteria are fullfilled cursor leaves field (and updates value in target-fields!)

b) NOMATCH
-- one needs to have a list to scroll: UP (!) and down in the column with the lookup-value for doing a point-and-shoot
-- a horizont-scroll in the choosen source-record, to see, whether it is the right record
The results could be (a VIEW in a table-grid):
-- record found (== point+shoot)
-- record NOT found
---- ESC and backwards to edit searchvalue (which should not be neccessary, as one can scroll the table)
---- EDIT the source-record (!)
---- ADD a new record to the source-table and return to the Grid to choose
Returning from that, the value in the search(lookup)-field is updated. If all went right, the check from item "a)" could be done again, just see above.

As that scenario needs to have the ability to ADD records to the source-(or lookup)-table, so the workflow becomes different, but all cases that could happen could be solved by putting the required info in very few parameter (which makes it possible to create a reusable Macro!)
--- a lookup-field: taking and updating the searched value and whereto jump back
--- a search-form-name: where the search-grid is (this should be a new window, NOT sub-form, as the feature sub-form might be needed for something else)
--- the functions within search-form: nav-keys and buttons for cancel/edit(start external form)/add(start external form)
--- a view(query)-name: the table/fields one wish to see in the browse(search)-grid (read only ofcourse)
--- the source-file-form-name: the external form to view/edit/add records - start outside the search-grid
--- a pointer-value in the search-grid (showing the whole table but pointing to the right record)
--- a list of variables that takes and holds the data (for that I would prefer a different behavior, however, it might be easier so)

Of course it could be possible to add/alter data in the search-grid, but within a good workflow the concept should be the same in all stages, so may be the "adding" in the source-file needs a lookup too, this must be made possible in the same behaviour.
And, as it is such a problem to do a "data-refresh" within a Base-form, I guess it is much easier to put all "external opperations" outside the Mainform.

in various commercial programms, that is solved as build-in or in simple macros (few parameters only), called lookup or zoom or something like that.
Or it is the basis of a commercial application using allinall simple data-engines (like using ADO from MS etc.) Why shouldn't it be possible to have this in OOO?
(I hope that this is read by people that knew more about sql than the folks, that think only about foreign-keys to solve stuff like that, which is not really a good approach )

Any comment is welcome

Martin
mh with ooo 3.3.0 + LO 3.3.2 on w2k
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Filter form with combobox

Post by Arineckaig »

I have the same "question" - so far unanswered,
I have to confess I do not fully understand what you are looking for. FWIW I have attached a meaningless Base file to illustrate use of form/subform as a means of filtering a form where two of the criteria are 1:n relationships - this requires no macro, but some care in linking the subforms. The file also illustrates drilling down by means of a double click which does require a macro. The other form illustrates the use of a search box that does require a macro. In the particular file there are few duplicate names, but where they exist the search box can act as filter as demonstrated by entering just part of a name.

Generally I prefer to use the Navigation Tool Bar for finding and filtering records but there can be circumstances where a macro may be suitable. Again I would stress that use of a dummy table for filtering or finding records has its merits and can frequently be a more suitable solution.

Let me know where I am off beam in answering your question.
Attachments
IndexFilterForms.odb
(43.2 KiB) Downloaded 1263 times
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filter form with combobox

Post by Villeroy »

I have the same "question" - so far unanswered, you may find it in here, when searching for "Lookup fields", but finally a diffent approach, so I like to explain some guesses how it should work - as a workflow, as I make concepts, I am no programmer, and even that idea is not mine, it is the rebuild of something quite famous - in DOS and about 25 years ago.
Well, this is the problem. You describe what you have in mind rather than your actual database.
There are no "Base databases" since the main intention of Base is to serve as a frontend to existing relational databases which can be described in the same terms, following a uniform logic.
A database is a set of tables, tables consist of strictly typed fields with indices and keys.
The keys make one-to-many relations (1-n), one-to-one relations in rare cases, and you can implement many-to-many relations with the help of a mapping table, connecting the 3 tables with 2 one-to-many relations.
This is the same scheme in all common relational, SQL-capable databases since decades and Base forms are designed to let a user edit relations of such bread-and-butter databases. Manual relation editing through input forms maps keys to mnemonic names. This is the core of Base forms as I understand it and list boxes and subforms are the most important parts of the concept. All the rest is about the ergonomic convenience of type-ahead combo boxes, calendar controls, numeric spin button fields, pattern fields and so on.

Base has one important precondition: A valid database conformant to some formal rules.
Main problem with many Base users seeking for help this forum: They seem to have no database behind their forms. They always describe the forms, preferably through screen shots.
Quite often their database is a lose collection of fields derived from an informal mind map. Base forms do not work with this. Base forms are very, very simplistic. Each control serves one distinct purpose and nothing else. In particular you can not use a list/combo to filter records. Knowing that a list can select values into records and a subform can filter records, you may develop quite advanced forms combining the features of the given tool set.
All in all Base is not a replacement for any of the commercial tool sets and worse: Most of the wizards are misleading.
Just 3 of the problems:
The table wizard does not help you to build up a valid relational database. It just saves you some clicks.
The form wizard covers relations in one way only (one subform to reflect the n-side of a one-to-many relation). Manual creation of forms gives you far more options.
The built-in database engine which allows you to create a backend database from scratch is poorly supported by the Base frontend. You can add some important features to your database structure by means of the command line (Tools>SQL): Timestamps, constraints, sequences.
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
mhatheoo
Posts: 17
Joined: Fri Nov 27, 2009 12:46 am

Re: Filter form with combobox

Post by mhatheoo »

==> villeroy

quite long reply. but sorry, probably you did not catch the point.
Your are welcome to ask again, how that rather simple DBMS-stuff should work, however, please read the above first.

Martin
mh with ooo 3.3.0 + LO 3.3.2 on w2k
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filter form with combobox

Post by Villeroy »

-- the actual file is sort of a detail-table (the n:-side of a relation)
-- and you are looking for something related in the master-table (the 1:-side)
For the record one is actualy dealing with, it is the establishing of a one:one-relation based on equality between
- a field-content in the target (or detail)-table
- and the source-(master)-table.
To establish the equality the condition is MATCH, meaning, the "search-value" in the form related to the target should MATCH to the appropriate Value in the source-table.

So my story goes like this (if this could made possible, it would be great):
HOW CAN I CHOOSE FROM THE MASTER-TABLE?
"Enter the search-value and hit ENTER" and see what happens:
A list box lets you pick a foreign key value (other table's ID) by mnemonic name.
Define the data properties of a list box as follows:
Data field: ForeignKey (the field we want to write some ID into)
Type of content: SQL
List content: Hit the [...] button for the graphical tool or type manually
SELECT "Name","ID" FROM "OtherTable" ORDER BY "Name"ASC (this type of query will always work regardless of the table's order of fields and rows)
Bound Field: 1 (surprise, surprise: #1 this is the ID, the visible name is field #0)

Does this help?
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
mhatheoo
Posts: 17
Joined: Fri Nov 27, 2009 12:46 am

Re: Filter form with combobox

Post by mhatheoo »

==> Arineckaig

thanks for reading. Yes, what I have in mind is something different.
as the searchfunction is similar, I droped my complaint here,
that might have been a mistake.

However, if you like some hints to your odb-file:
-- the query-result is a set of records, but you show only one record.
probably you need to have a table-control and a record-view in one form
-- the like-function is a beast.
Example: you search "meier" and enter "M"
you get:
AM
CDM
HIMMM
MEIER
If you believe the function is useful, you may use the query-result but point firstly to the record start with the "M"

Martin
mh with ooo 3.3.0 + LO 3.3.2 on w2k
mhatheoo
Posts: 17
Joined: Fri Nov 27, 2009 12:46 am

Re: Filter form with combobox

Post by mhatheoo »

==> Villeroy

please, the concept is different, no chance to rescue anything by using listboxes.
You will never win the opportunity to ADD data in a situation with listboxes only.
However, at least a look-up field is more similar to a combo-box than to a listbox
(define a source- and a target-field)

Martin
mh with ooo 3.3.0 + LO 3.3.2 on w2k
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Filter form with combobox

Post by Arineckaig »

thanks for reading. Yes, what I have in mind is something different.
as the searchfunction is similar, I droped my complaint here,
that might have been a mistake.
I am sorry but I have no proper idea of what you have in mind. If (and only if) I understand correctly your criticism of my example, it should be no great problem to adapt the search box to permit the user to enter wild card(s) in whatever way he/she may wish or even to exclude then entirely if a LIKE clause is unacceptable.

I am happy to try again but would suggest it might be more productive if you sent me a PM. I could then reply in detail to get a better understanding of what you have in mind rather than continue unproductive posts that can be of little value to others.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filter form with combobox

Post by Villeroy »

I think he wants a search-as-you-type sort of feature. Type something into the Firefox side bar or Thunderbird's search box. A text box combined with one of the above macros on event "Modified".
Or this thing using a spreadsheet's "advanced filter": http://user.services.openoffice.org/en/ ... php?id=136
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
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Filter form with combobox

Post by Arineckaig »

I think he wants a search-as-you-type sort of feature.
If that is the case it should be no great problem to change the macro to read each key as entered and run the key part of the SQL filter as " LIKE entered text & '%' ". I will play with it but hopefully we will get some more definite instructions.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Filter form with combobox

Post by Arineckaig »

If I am thinking along the right lines, Villeroy, the attached could be a crude start that could be developed further. Its too late to test it fully.
Attachments
SearchNames.odb
(34.84 KiB) Downloaded 899 times
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
mhatheoo
Posts: 17
Joined: Fri Nov 27, 2009 12:46 am

Re: Filter form with combobox

Post by mhatheoo »

hm, I could not be misinterpreted, probably you are lacking in some experience in DBMS-development.
However, the attached example may help. You can see, that search is not only useful in retrieval
but also when adding new records. Comments are welcome

Martin
Attachments
europa.odb
example with listbox - and additional comments in the form
(25.87 KiB) Downloaded 938 times
mh with ooo 3.3.0 + LO 3.3.2 on w2k
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Filter form with combobox

Post by Arineckaig »

As you say, I am indeed
lacking in some experience in DBMS-development
and thus doubt I can be of any help to you.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filter form with combobox

Post by Villeroy »

"country" and "language" need recorded in the apropriate table in advance
Yes, of course. A new record for country or language may require more than a name plus auto-ID.
You can add as many forms to the same form document as you need. Needing a new country or language, you enter a new name (plus other mandatory field values) into another form on the same form document, refresh the initial form's list box and continue editing the relation. This is why there are 2 refresh buttons on the user's toolbar. One for a focussed list control, the other for the entire form.
Movies_3.odb by Drew Jensen with a little addition of mine. Form "Movies" has editable movies associated with multiple genres in a subform plus a third form to enter new genre names. Then you refresh the list box of genres in order to be able to pick the previously entered genre.
As I tried to outline, Base is very simplistic, thus difficult to handle for the developer as well as for the user.
Things like transactions and triggers are not supported. Even if the backend supports this, Base has no complimentary feature. You've got to enter contents from the bottom up. First enter items before you can associate them with other items.

All you can do is: Turn off menu:Edit>Database>Advanced..."Form data input checks for required fields". This option being turned off may help in some way. I'm no database developer neither.
Turning off this option allows you to leave out a required value and let the backend handle this by means of a field's default value. Base will not complain about the missing form value then.
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
mhatheoo
Posts: 17
Joined: Fri Nov 27, 2009 12:46 am

Re: Filter form with combobox

Post by mhatheoo »

==> villeroy

the movie-obd describes the problem quite perfect
-- from looking at the form the structure is hardly to understand
-- it lacks in the ability to edit content
Even if one can not see it, it is a
-- one table-/one record show ( table movie ) (editable)
-- as well as a n:m stuff (non-editable)
-- has a second subform to enter data in a foreign table (genre) (non editable - and strange habits for new entries on old records too)
that mix is rediculous. Although it shows a quite common example, on how things are solved in OOO right now, it should not be one, it is not really professional and has no potential for the future.

Note:
Lookup-field have the potential to keep a form "flat", which makes it much simpler to develope tables/forms and makes also much simpler to use normalized structures in OOO. By that it would be much easier to deal with "real data" meaning with linking records that consist of 10 and plus fields per record - with three/four/five lookup-fields per record. Think about invoices or bookkeeping.
if the example is such weak and uneducational, OOO will never win pro's to give OOO a try - and thereof bring Base some steps ahead.

Just a question for understanding: Did you noticed, where - bound to which file - in your example the lookupfields are needed?

Martin


P.S.:
(Bytheway: ENTER should be at no stage an option to store records/data!)
mh with ooo 3.3.0 + LO 3.3.2 on w2k
DYoung
Posts: 2
Joined: Wed Dec 16, 2009 12:23 pm

Re: Filter form with combobox

Post by DYoung »

Hi all,

I'm quite new to OpenOffice Base but I'm finding my feet quite quickly (a case of having to!)

I have used Arineckaig's IndexFilterForms.odb (attached) in my own database and have got it to work perfectly, ''Drilling Down' the database entries.

I was wondering if there was a way of adding a tick box to each criteria table to enable each one. If neither box is ticked all entries will be shown in the display table.

Many thanks in advance,
Don
Attachments
IndexFilterForms[1].odb
Arineckaig's file...
(44.12 KiB) Downloaded 908 times
OpenOffice Version 3.1.0
Windows XP SP3
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Filter form with combobox

Post by Arineckaig »

I was wondering if there was a way of adding a tick box to each criteria table to enable each one. If neither box is ticked all entries will be shown in the display table.
The method of using sub-forms and sub-sub-forms to filter records does not readily lend itself to removing the filters to show all records in the sub-form or even the sub-sub-form. As I tried to explain in my post of Mon Nov 23, 2009 12:07 pm further up this thread, the relationship between a sub-form and its form has the characteristics of a SQL SELECT .....WHERE command. The result is essentially a permanent filter built into the structure. In short, I would avoid this filtering method if you wish to disable it at times to show all records.

I suggest other filtering methods would be far better suited for your requirement: for example, the use of a dummy table as described by Villeroy. I would also suggest the auto-filter and other icons provided by the form navigation bar give quick and simple methods for enabling and disabling single or multi filters: the merits this flexible tool are sometimes overlooked.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
malik
Posts: 10
Joined: Mon Jun 28, 2010 1:59 am

Re: Filter form with combobox

Post by malik »

I've been trying to do something very similar, only upon making a selection from combo box1 would then re-populate the options of combo box2. Then that selection would in turn would re-populate selections in combo box3. then show the selected data in a table control and populate 4 other text boxes. I can populate all the combo boxes with the correct data fields but cant filter the data in this way. I've had Quick basic and Visual basic classes of which i failed, but i think i'm getting a understanding of how SQL works but no promises. I attached a file with a database and form.

I've been trolling this forum and other places for months and i just dont get it. I have a hard time understanding where the syntax ends and the examples begin.
Simple things like What the address of a combo box on a form if the form is named Form1 and the combo box is called Combo Box1? I know a combo box saves a selection in it. how to you call that selection for a nifty strSQL SELECT? My DB is Relational related correctly? i don't know. I have a sub form but, I'm unsure what exactly to do with it. Any help would be appreciated a lot.
<<CheerS>>
 Edit: - File too large for upload figures- 
Open Office 3.3 Windows XP Pro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filter form with combobox

Post by Villeroy »

In 90% of all cases you do not want a combo box. What you want is a list box in most cases. Anyhow, the attached document "administrates" two kinds of shops for clothings and for fruits. The selectable products depend on a preselection.
Attachments
shopping.odb
Multi form with filtered list box for fruit and clothing
(26.65 KiB) Downloaded 1102 times
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
malik
Posts: 10
Joined: Mon Jun 28, 2010 1:59 am

Re: Filter form with combobox

Post by malik »

Thanks For your reply Villeroy
After i made a model of my DB i was re-examining ListBoxes and pouring over the threads/forms/TFM again. Then this morning i saw you post. Good to know I was on the right track it would seem. I went with Combo Box because i read somewhere that it can store a selection as a Variable or String and sounded like the way to go. But i'm far from being dead set on it. As long as can build basically a query based on 3 strings and have each selection limt the results of the next selection and the next selection in a format of some sort of pull down menu on a form.... :bravo:

I think i get it, But i've been there before. When i think i get something and then run it and get spamed with error messages... anyway.. I made a stripped down version of my DB the original is 4000+ records (stopped looking at the main table much past that). Anyway going to read through your reply and your attachment few more times and try and figure out what the 3 pop-up windows really mean when i create a listbox(should be in SG/TFM) again. A little disclaimer, I'm not the record keeper/data maintenance guy for this database. data is in wrong fields and erroneous in almost every possible way. But it's just a small part of it so it may not be too bad. And Again Thank You So Much For Your Time Villeroy and others whom may respond.
Attachments
EXPDB.odb
small piece of the real thing.
(21.22 KiB) Downloaded 735 times
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Filter form with cascading List Boxes using macros

Post by DACM »

malik wrote:..TFM again...should be in SG/TFM) again
Really?
I have no idea what your shorthand means ;)

Getting down to business.
Like you said -- your underlying database is a mess -- but simply working from your Form1 to filter your "TLoc" table using cascading List Boxes...
Villeroy's post is invaluable for teaching various Base Form-components and SQL integration.
But using a series of cascading List Boxes to filter/search a Table in a Form almost insists on some macros. I had a very similar Form with 3 cascading List Boxes where I used macros to apply filters progressively, just as you described. So applying those macros to your Form renders the follows:
Attachments
EXPDB_macro_filtering.odb
(22.02 KiB) Downloaded 1291 times
Last edited by DACM on Fri Oct 18, 2013 7:20 pm, edited 1 time in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Post Reply