[Solved] Using a form to search and export data

[fabersum]

Hello everybody

I hope this is the right place to post this...

thanks to sheepdodgguides and the help I got on the forums (thanks again Villeroy, Voobase, MSPhobe, bschaich) I was able to start setting up a very simple database - it's a contact list, with just one table and one data entry form.. so simple that I am shy talking about it here

however, here it is:
http://www.mediafire.com/?mgjm9bnxfvx

We are a small industrial design and architecture firm. The database will be used to search for a given client name to see his contact data or to search for all the clients that operate in a given field to export all their emails to a csv file to create a mailing list to send them our related newsletter. There are three checkboxes too in the data entry form and the same should be used as filters for the search.

Now I need to set up a user-friendly way to search for the data stored in the table, and the best thing would be to be able to use a form similar to the data entry one, with some text box and some listbox to fill, the checkboxes to check/uncheck and a search button...

I am sure it's something possible to do with queries, forms and reports, but the stuff to learn is so much! Setting up this database is up to me now because I was the one who wanted to use OOBase instead of Access, but my job is to work on CNC machinery... I won't be making other databases and studying so much for just one application is frustrating, so the more help I can get here...

thanks thanks thanks!

Andrea

[voobase]

Hi Andrea,

Your form looks very good.

Creating a searchbox would require some use of macro code. I am not sure how much you have played with macros yet so I have given you a bit of a head start and put some macro code in your database with a search box. The macro code is based around a design I use myself and which I detailed in this post... http://www.oooforum.org/forum/viewtopic.phtml?p=299738

Now, the macro code is stored within the database form. This means you will get a macro warning when ever you launch the form. I recommend you have a go at moving the macro code into your "my Macros" area of Open Office. After you have done so you will need to relink the "events" from the searchbox controls so as to point to your "my macros" area instead. After that is done you can remove the macro code from your form and that should stop the macro warnings. Additionally, when Open Office V 3.1 comes along, it will want to migrate all macros stored in forms into a new macro container inside the database file. Another problem with macro code in the form which I find using OOorg 2.31 is that the first time you run the form the macro code is not found. You have to close it and open the form again. Also, when using design mode and toggling back to the data entry mode the macros won't work. Yes, I do recommend moving the macro's to the "My Macro's" section or waiting for OO3.1 and storing them in the database file.

To get to "My Macros" press ALT F11

The events in use are "Text Modified" and "Key Pressed" of the text search box and "When Initiating" of search button.

Here is the macro code. It got a bit tricky to integrate the checkbox's into the filter code but it seems to work. You might be able to neaten it up a bit if you like.

Code: Select all

Rem... To make a "return" from search box do the search. Attached to the key pressed event of textbox.
Sub Key_Pressed_Main (oEv as object)

	dim oForm as object

	oForm = oEv.source.model.parent

		if(oEv.KeyCode=1280) then
			searchbutton_Main	
		end  if
End sub


Rem... From Text Modified event of text box
Sub searchbox_Main

dim oFilter as object
dim oFormCtl as object
dim mystring as string

	oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm")
	oFilter = oFormCtl.getByName("txtSearchBox")

		if oFilter.Text <> "" then
		
			mystring = oFilter.text
			FrstCHR = LEFT(mystring, 1)
			strLen = LEN(mystring)
			
			If FrstCHR = SPACE( 1 ) then
				msgbox "Please don't leave space in start position of search box. It will catch you out later!"
				oFilter.Text = ""
			End if

		else

'msgbox "about to reload"
		oFormCtl.ApplyFilter = False
		oFormCtl.Reload
		Rem... set checkbox state to 0
		oFormCtl.getByName("chkclient").state = 0
		oFormCtl.getByName("chkinter").state = 0
		oFormCtl.getByName("evit").state = 0

		end if
End Sub



Sub searchbutton_Main

dim oFilter as object
dim oFormCtl as object
dim varCHK1 as boolean
dim varCHK2 as boolean
dim varCHK3 as boolean

		oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm")
		oFilter = oFormCtl.getByName("txtSearchBox")
		oCHK1 = oFormCtl.getByName("chkclient")
		oCHK2 = oFormCtl.getByName("chkinter")
		oCHK3 = oFormCtl.getByName("evit")
		oFormCtl.filter = ""
		
		Rem... Construct the filtering you like here by addding in extra lines
		If oFilter.Text <> "" then
			oFormCtl.Filter = "( UPPER(""Cognome o ragione sociale"") LIKE " + "UPPER('%"+oFilter.Text+"%')"
			oFormCtl.Filter = oFormCtl.Filter & " OR UPPER(""Nome"") LIKE " + "UPPER('%"+oFilter.Text+"%') )"	
		End if
		Rem... need to use If's for the checkbox's as the SQL strings are OR'd together. Otherwise a 0 state would select an unchecked field.
		Rem... Gets a bit messy here. There is probably a neater way somehow.
		If oFilter.Text <> "" AND ( oCHK1.state <> 0 OR oCHK2.state <> 0 OR oCHK3.state <> 0 ) then
			oFormCtl.Filter = oFormCtl.Filter & " AND ( "
		End if
		Rem... load up variables to use True false instead of state (1 or 0)
		If oCHK1.state = 1 then
			varCHK1 = true
		End if
		If oCHK2.state = 1 then
			varCHK2 = true
		End if
		If oCHK3.state = 1 then
			varCHK3 = true
		End if
		
		If oCHK1.state <> 0  then
			oFormCtl.Filter = oFormCtl.Filter & " ""cliente sì/no"" = " + "'"+varCHK1+"'"
		End if
		If oCHK2.state <> 0 AND oCHK1.state <> 0 then
			oFormCtl.Filter = oFormCtl.Filter & " OR ""interessato sì/no"" = " + "'"+varCHK2+"'"
			elseif oCHK2.state <> 0 then
			oFormCtl.Filter = oFormCtl.Filter & " ""interessato sì/no"" = " + "'"+varCHK2+"'"
		End if
		If oCHK3.state <> 0 AND ( oCHK1.state <> 0 OR oCHK2.state <> 0 ) then
			oFormCtl.Filter = oFormCtl.Filter & " OR ""evitare"" = " + "'"+varCHK3+"'"
			elseif oCHK3.state <> 0 then
			oFormCtl.Filter = oFormCtl.Filter & " ""evitare"" = " + "'"+varCHK3+"'"
		End if
		Rem... Close the bracket if both text and a checkbox used.
		If oFilter.Text <> "" AND ( oCHK1.state <> 0 OR oCHK2.state <> 0 OR oCHK3.state <> 0 ) then
			oFormCtl.Filter = oFormCtl.Filter & " )"
		End if
'		msgbox oFormCtl.Filter
		oFormCtl.ApplyFilter = True
		oFormCtl.Reload 
		
		Rem... If nothing was found by filter
		If oFormCtl.IsBeforeFirst OR oFormCtl.IsAfterLast then
			oFilter.Text = "Nothing Found"
		End if
		Rem... Put some text in the searchbox to remind user that the filter is on if check box used only.
		If oFilter.Text = "" AND ( oCHK1.state <> 0 OR oCHK2.state <> 0 OR oCHK3.state <> 0 ) then
			oFilter.Text = "Filter On"
		End if
End Sub


Sub saveDBDoc (oEv as object)
dim oForm as object
	oForm = oEv.source.model.parent
	Rem... first save changes to the form
	If oForm.IsNew then
		oForm.InsertRow()
	ELse
		oForm.UpdateRow()
	End if

	Rem... Store the database
	oForm.parent.parent.parent.store()
 '   ThisComponent.Parent.store()
	msgbox ("Database Stored")
End Sub

I've posted the database back on mediafire...
http://www.mediafire.com/?sharekey=4f71 ... b9a8902bda
(Database_Multiplo with searchbox)

Cheers

Voo

PS... Because you are using this for your business, be careful because many people don't realise that the database is actually running in memory on your computer. This means you can loose data if your computer was to crash, even if you have been pressing save regularly. (see this post... http://user.services.openoffice.org/en/ ... =13&t=7446 ) I have put a button down the bottom which does a store() which should save it to disk for you. It's also good to do periodic backups of your database file as well of course.

[fabersum]

Voo

many thanks by now.. I just found your post and still have to dig into it - it will take me time and I didn't want to wait so much to thank you for your help!

A.

[fabersum]

Ok, macros moved to "My Macros" and working - I changed the event "when initiating" of the "Store DB" button too linking to My Macros and everything is so smoothly working now that I spent half an hour just doing useless searches and smiling happy
amazing, this is really something that would have taken me months to come out with - your help is unvaluable! thanks again.
Now studying your macros about the possibility to filter the records through one or more of the listboxes. Should I be able to modify the macros you wrote for me so to add the possibility to search for all the clients who have - let me say - "studio" in the field "tipologia contatto" AND/OR have "design" in one of the three fields "attività"?

Then I should be able to export the results of the search in a spreadsheet document, or in a .csv format...

How can I thank you? WOuld you like a wooden plate for your door? This is my website http://www.fabersum.it..

Andrea

[fabersum]

Hmmm,I started to try modifying the macros to implement the possibility to filter the results through the "tipologia contatto" and "settore attività" listboxes too. Of course, it's not working anymore.. not a big surprise since this is the first time I put my hands at macros. I am not sure about the syntax, I am just trying to write something the more similar I can to what you wrote...

I started duplicating the two listboxes I'll use as a filter, changing their names (as you did for the checkboxes I suppose) then declaring two more variables (oFilter1 and oFilter2)

dim oFilter1 as object
dim oFilter2 as object

and assigning them these new listboxes contents

oFilter1 = oFormCtl.getByName("txttipo_contatto")
oFilter2 = oFormCtl.getByName("txtsett_attività")

then trying to add these filters to the one you wrote... here is the modified searchbutton_main sub:

Code: Select all

Sub searchbutton_Main

dim oFilter as object
dim oFilter1 as object
dim oFilter2 as object
dim oFormCtl as object
dim varCHK1 as boolean
dim varCHK2 as boolean
dim varCHK3 as boolean

      oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm")
      oFilter = oFormCtl.getByName("txtSearchBox")
      oFilter1 = oFormCtl.getByName("txttipo_contatto")
      oFilter2 = oFormCtl.getByName("txtsett_attività")
      oCHK1 = oFormCtl.getByName("chkclient")
      oCHK2 = oFormCtl.getByName("chkinter")
      oCHK3 = oFormCtl.getByName("evit")
      oFormCtl.filter = ""
      
      Rem... (Voo) Construct the filtering you like here by addding in extra lines
      If oFilter.Text <> "" then
         oFormCtl.Filter = "( UPPER(""Cognome o ragione sociale"") LIKE " + "UPPER('%"+oFilter.Text+"%')"
         oFormCtl.Filter = oFormCtl.Filter & " OR UPPER(""Nome"") LIKE " + "UPPER('%"+oFilter.Text+"%') )"   
      End if
      Rem... (Andrea) these are the lines I added
      If oFilter.Text <> "" AND oFilter1.Text <> "" then
         oFormCtl.Filter = oFormCtl.Filter & " AND ""tipologia contatto"" = " + "'%"+oFilter1.Text+"%'"
      End if
      If oFilter1.Text <> "" then
         oFormCtl.Filter = oFormCtl.Filter & " ""tipologia contatto"" = " + "'%"+oFilter1.Text+"%'"
      End if
      If ( oFilter.Text <> "" OR oFilter1.Text <> "" ) AND oFilter2.Text <> "" then
         oFormCtl.Filter = oFormCtl.Filter & " AND ( ""settore attività"" = " + "'%"+oFilter2.Text+"%'" 
         oFormCtl.Filter = oFormCtl.Filter & " OR ""settore attività2"" = " + "'%"+oFilter2.Text+"%'"
         oFormCtl.Filter = oFormCtl.Filter & " OR ""settore attività3"" = " + "'%"+oFilter2.Text+"%'"
         oFormCtl.Filter = oFormCtl.Filter & " )"
      End if
      If oFilter2.Text <> "" then
         oFormCtl.Filter = oFormCtl.Filter & " ""settore attività"" = " + "'%"+oFilter2.Text+"%'"
         oFormCtl.Filter = oFormCtl.Filter & " OR ""settore attività2"" = " + "'%"+oFilter2.Text+"%'"
         oFormCtl.Filter = oFormCtl.Filter & " OR ""settore attività3"" = " + "'%"+oFilter2.Text+"%'"
      End if
 
      Rem... (Andrea) from here on the subroutine is unchanged 

      Rem... need to use If's for the checkbox's as the SQL strings are OR'd together. Otherwise a 0 state would select an unchecked field.
      Rem... Gets a bit messy here. There is probably a neater way somehow.
      If oFilter.Text <> "" AND ( oCHK1.state <> 0 OR oCHK2.state <> 0 OR oCHK3.state <> 0 ) then
         oFormCtl.Filter = oFormCtl.Filter & " AND ( "
      End if
      Rem... load up variables to use True false instead of state (1 or 0)
      If oCHK1.state = 1 then
         varCHK1 = true
      End if
      If oCHK2.state = 1 then
         varCHK2 = true
      End if
      If oCHK3.state = 1 then
         varCHK3 = true
      End if
      
      If oCHK1.state <> 0  then
         oFormCtl.Filter = oFormCtl.Filter & " ""cliente sì/no"" = " + "'"+varCHK1+"'"
      End if
      If oCHK2.state <> 0 AND oCHK1.state <> 0 then
         oFormCtl.Filter = oFormCtl.Filter & " OR ""interessato sì/no"" = " + "'"+varCHK2+"'"
         elseif oCHK2.state <> 0 then
         oFormCtl.Filter = oFormCtl.Filter & " ""interessato sì/no"" = " + "'"+varCHK2+"'"
      End if
      If oCHK3.state <> 0 AND ( oCHK1.state <> 0 OR oCHK2.state <> 0 ) then
         oFormCtl.Filter = oFormCtl.Filter & " OR ""evitare"" = " + "'"+varCHK3+"'"
         elseif oCHK3.state <> 0 then
         oFormCtl.Filter = oFormCtl.Filter & " ""evitare"" = " + "'"+varCHK3+"'"
      End if
      Rem... Close the bracket if both text and a checkbox used.
      If oFilter.Text <> "" AND ( oCHK1.state <> 0 OR oCHK2.state <> 0 OR oCHK3.state <> 0 ) then
         oFormCtl.Filter = oFormCtl.Filter & " )"
      End if
'      msgbox oFormCtl.Filter
      oFormCtl.ApplyFilter = True
      oFormCtl.Reload 
      
      Rem... If nothing was found by filter
      If oFormCtl.IsBeforeFirst OR oFormCtl.IsAfterLast then
         oFilter.Text = "Nothing Found"
      End if
      Rem... Put some text in the searchbox to remind user that the filter is on if check box used only.
      If oFilter.Text = "" AND ( oCHK1.state <> 0 OR oCHK2.state <> 0 OR oCHK3.state <> 0 ) then
         oFilter.Text = "Filter On"
      End if
End Sub

when I try to launch the macro (pressing the "search" button in the form) I get the "BASIC runtime error. Property or method not found" message at this line (the first one I added to the filtering)

Code: Select all

If oFilter.Text <> "" AND oFilter1.Text <> "" then

What's wrong?

Here is the database with the modified form (called "Tabella Contatti search Andrea"):
http://www.mediafire.com/file/vjyw2ojoh ... oo_mio.odb

Thanks again

cheers!

Andrea

(Villeroy! Italy and Germay are so close... I can hear you laugh from here! )

[Villeroy]

I can not tell what's wrong. Of course you can use your database as a list of more or less structured data and write a program to sort things out in the Base frontend. Then everything depends on your programming skills and Base.

If I had to design any type of database, HSQLDB, MySQL or whatever, I would try to use the standardized software tools which work reliably for millions of databases since decades. This is a completely different standard compared to Base and Basic.

For now I won't attach a new version of your work. Instead I show you how I manipulated the version of your first mediafire-link in order to get close to an export of filtered record sets.

The following SQL-commands could be triggered from the Base-GUI entirely, but that would be extremely difficult to explain. ALTER and CREATE-commands have their counterparts in the design-views of tables and relations. INSERT, DELETE, UPDATE are commands to manipulate data as you would do in the grid view of a table.
SQL has not nothing to do with macros. SQL is the normal way to operate databases, even without a desktop environment (most databases run on servers). The same SQL is supposed to work with all standard databases.
Base-menu:Tools>SQL... paste and execute:

Code: Select all

-- allow case-insensitive matching for 2 fields:
alter table "Tabella Contatti" alter column "Cognome o ragione sociale" VARCHAR_IGNORECASE(50);
alter table "Tabella Contatti" alter column "Nome" VARCHAR_IGNORECASE(50);

-- update all booleans from Null to False before we can ...
UPDATE "Tabella Contatti" SET "cliente sì/no"=ifnull("cliente sì/no",FALSE);
UPDATE "Tabella Contatti" SET "interessato sì/no"=ifnull("interessato sì/no",FALSE);
UPDATE "Tabella Contatti" SET "evitare"=ifnull("evitare",FALSE);

--  ... change the column type from BOOLAN to BOOLEAN DEFAULT FALSE NOT NULL;
alter table "Tabella Contatti" alter column "cliente sì/no" BOOLEAN DEFAULT FALSE NOT NULL ;
alter table "Tabella Contatti" alter column "interessato sì/no" BOOLEAN DEFAULT FALSE NOT NULL;
alter table "Tabella Contatti" alter column "evitare" BOOLEAN DEFAULT FALSE NOT NULL;

-- add filter table for the new form controls:
create table "Filter_Contatti" (
"Filter_ID" integer primary key,
"Search" VARCHAR_IGNORECASE(50),
"CheckClient" BOOLEAN,
"CheckInterest" BOOLEAN,
"CheckEvitare" BOOLEAN);

-- insert one set of filter fields at record-ID #0
insert into "Filter_Contatti" ("Filter_ID") VALUES (0);

Go to tables and call menu:View>Refresh Tables. Now you see a new filter-table where you can bind form controls to. In the design view of the main table you see the other changes.
Call menu:Insert>View(simple)... and paste the following SQL:

Code: Select all

-- select all the long list of fields (you can change the list and it's order) ...
SELECT "T"."ID", "T"."Cognome o ragione sociale", "T"."Nome", "T"."via 1", "T"."CAP", "T"."Città", "T"."Prov", "T"."Nazione", "T"."tel", "T"."sito", "T"."email", "T"."nome referente", "T"."ruolo referente", "T"."email referente", "T"."tel referente", "T"."contatto interno a Multiplo", "T"."note", "T"."tipologia contatto", "T"."settore attività", "T"."cliente sì/no", "T"."interessato sì/no", "T"."importanza oggettiva 1-10", "T"."importanza soggettiva 1-10", "T"."titolo referente", "T"."via 2", "T"."fax", "T"."altro (skype?)", "T"."P. IVA", "T"."lingua madre", "T"."evitare", "T"."settore attività2", "T"."settore attività3"

-- ...form the two tables with aliases "T" and "F"...
FROM "Tabella Contatti" AS "T", "Filter_Contatti" AS "F"

-- ... and the single record with ID=0 where we bind our form to ...
WHERE ("F"."Filter_ID" = 0)

-- ... compare boolean fields or disregard if filter is null ...
AND(( "T"."cliente sì/no" = "F"."CheckClient" ) OR ("F"."CheckClient" IS NULL))
AND (( "T"."interessato sì/no" = "F"."CheckInterest" ) OR ("F"."CheckInterest" IS NULL))
AND (( "T"."evitare" = "F"."CheckEvitare" ) OR ("F"."CheckEvitare" IS NULL))

-- ... and compare two case-insensitive text fields if they start with the same characters as in the search box:
AND (
 ( "T"."Cognome o ragione sociale" LIKE "F"."Search" || '%' )
 OR ( "T"."Nome" LIKE "F"."Search" || '%' ) 
);

- Store this as "ViewExport".
- Go to the queries, create a new one in SQL view, turn on "direct SQL mode" and store the same SELECT-query as a backup. This is really important. If you want to modify the tables structure later, you have to delete the view. A backup in the query container is the right place to test and adjust the SELECT statement before you recreate the view then.

Create a second query for easy testing:

Code: Select all

SELECT "ID", "Cognome o ragione sociale", "Nome", "cliente sì/no", "interessato sì/no", "evitare" FROM "Tabella Contatti"

This shows only the main table fields we are currently interested in. Store under any name. I used "TestFilterFields"

Open the filter-table, the test-query and the view side by side in small windows. The view slurps it's data through the filter from the main table. Edit filter values in the single row with ID #0, add/modify data in the main table and refresh the view.
The filter works as described in the SQL-comments. Boolean options are disregarded if the corresponding filter-value is Null. The search-string matches case-insensitively in both fields "Cognome o ragione sociale" and "Nome".

A third query "qFilterZero" for the form to come:

Code: Select all

SELECT * FROM "Filter_Contatti" WHERE "Filter_ID" = 0

All you have to do now in order to get a fully functional filter-form is:
Create a new form by wizard. I used the following settings:
Source: qFilterZero
Fields Include all except for the ID which must not be editable nor visible.
No subform
I used the 4th layout (in Blocks, labels above)
Data entry: Allow modification only.
Style and name: anything you like.
At the end of the wizard check the option to modify the form.

The form navigator makes it easier to select a group of controls without their associated labels, but you may also try:
Control-Click the firlst check box (without it's label)
Ctrl+Shift+Click the other two, so you have all 3 check boxes selected and set their common property "Tristate" to "Yes".
Or set it one-by-one for each box.
Save the form.

Now for the export. My intention was:

Code: Select all

drop table "export" if exists;
select * into text "export" from "ViewExport";

It would be trivial to bind a button to a macro, calling these statements.
This (or something similar) used to work yesterday. It is supposed to delete an existing link to a text-file and create a new text-file "<odb-path>/export.csv" with a link in the database. Currently I get Java Null-Pointer exeptions and an empty table-link, but no csv-file.
BUT: I can easily drop the view into a spreadsheet and save as csv.

EDIT: How to add a result-preview to the filter-form:
Get the form navigator from the 5th button of toolbar "Form Design".
Right click the root ("Forms" ) and add a new form.
Bind that form to table "ViewExport" and keep it selected.
The 3rd button from the right on toolbar "Form Controls" gets another bar with additional controls. Select the tool to draw a table control.
The last button on toolbar "Form Controls" turns on the automatic wizards when you draw new controls.
Make the window wide and draw a "Table Control" across the form with the wizard button turned on and the new, blank form selected in the navigator. A wizard pops up and offers to include all the fields of the source table "ViewExport". Do so.
Add a push button to the same form and set it's "Action"-property to "Refresh Form".
Turn off design mode (button #2) and test the form. Set up a filter and hit the button to see the result. If it's fine store the form and then the datbase. Until now I did not find a way to copy or drag data out of a form, but the preview shows the result of the filter, so you can modify the filter until you can expect the right data when you finally copy the view.

[fabersum]

Holy everything, Villeroy, many thanks!

I am amazed from how much time you are investing to help me... I don't know if I could do something for you to thank you, but let me know!
I can't understand all your post now - I work at night, I just woke up and my friends are waiting - but I'll dig it later and will spend next night on it.
I'll be sooo happy to put [solved] in the thread's subject!
Thanks again

A.

[fabersum]

I finished to follow your GREAT step by step instructions.. I'll follow them step by step again to get a better understanding of what happens in each step.
I think this thread could be useful to a lot of people...
Many thanks again.
I think I could call this thread "[solved]" but I prefer to wait one day more, should I still have something to ask you!

cheers

Andrea

[tommaso.b.bianchi]

sorry seems that my previous upload does not contain queries and forms

Having the same problem of andrea I've followed Villeroy instruction to create a reduced version of his db.

Everything is perfect except I can't insert any string into the qFilter0 query; I've tested it also in win xp; This way the search test field in the search form does not allow insertion of text..

here it is
test_finalizzato_ricerche02
http://www.mediafire.com/?sharekey=9686 ... f6e8ebb871

What if I create a form based on the search field of Filter_Contatti Table? How can I create a search box only linked to that search field?

any help really appreciated

Tommaso

[Villeroy]

Thank you for clarification. The source of your form "qFilterZero" is read-only because it uses "direct SQL mode" (query-menu:Edit>Run SQL directly). Until now I was not aware that direct SQL implies read-onlyness and I do not understand why this is so and if this used to be so in former versions as well. You can turn off "direct SQL" for this simple query and it becomes writable.

P.S. Just tested with 2.4.2. It's the same problem. SELECT * FROM "Writable_Table" is read-only in direct mode. This is a petty because there used to be many cases where more or less complex queries use to work in direct mode (interpreted by the database) and fail to be interpreted correctly in Base.

[tommaso.b.bianchi]

Just controlled. It works!! Villeroy you are a GENIUS!!

Since the database I work with is hosted in a MS SQL server, do you think that this could affect your SQL queries?

Moreover, please look at test_finalizzato_ricerche03.odb at http://www.mediafire.com/?sharekey=9686 ... f6e8ebb871

Now, do you know how can I invoice the "Referti" form ( by means of double clicking to one of the search results of the Search form) in order to fill table "Referti", that has a 1 to n relationship with ID (1 ID to n IDcliente) of the Table "Pazienti"?

Thanking in advance,

Tommaso

[Villeroy]

STOP! I'm not a genius and I won't take one database after the other for debugging just because I tried to explain in this thread how databases can be used by non-programmers. I have a little bit of experience with MSAccess ('97 and 2000) and did some experiments with MySQL. I use a single, flat dBase table with Calc as "reporting engine" to monitor private expenses. That's all. Whatever you want to do on your MS SQL-server is completely up to you and totally off topic here, but I would aprechiate if you would tell us the result of your own efforts.

[tommaso.b.bianchi]

Dear Villeroy:

again, thanks for the explanation.
I am forced by my company to use MS SQL server as db engine, but all the client work is done by OO base. Simply it connects to SQL server using odbc, the same way it should do with mysql. That's all. I frankly do not believe it's OT.

Sure my intention is not to make you debug my db. Following your lesson I think I am starting to interiorize the logic of SQL programming.

Cheers

T

[fabersum]

Hi Villeroy, hello everyone reading

I am back on your work trying to take full advantage of your efforts.
Your post really was a mine of precious infos.

I'll start with some question as they come...

-- ... and compare two case-insensitive text fields if they start with the same characters as in the search box:
AND (
( "T"."Cognome o ragione sociale" LIKE "F"."Search" || '%' )
OR ( "T"."Nome" LIKE "F"."Search" || '%' )
);

- how should I modify the view to have the filter catch values if the characters in the search box are found in the middle of the field too?
I mean: if I write "Giu" in the search field, the filter will find "Giugiaro" and "Giu Finto" but not "Argiuno" nor "Design Giugiaro".
I did some try and I see I can get those records too simply writing "%Giu" in the search field, but there's maybe another way - and I like to understand and know more about SQL syntax. Something like ( "T"."Cognome o ragione sociale" LIKE '%' || "F"."Search" || '%' ) ?
(Well, I just tried to modify the view, and it seems to work as I wanted! So does % indicate a wildcard? and ||?)

- is it possible to use booleans operators in the search? I wasn't able by now.
You maybe remember there's a "Note" field in my table. It's of the Memo [LONGVARCHAR] TYPE. The users will put some generic description text there, let me say i.e. "produttore di cucine molto attento al design".
Then they would like to find that record searching "cucine design". I suppose I should write "cucine AND design" in the search box, but is not working. Any hint on this?

Going on studying your post here...

You already know - many thanks!

A.

[Villeroy]

DB-objects in "double quotes", literal text in 'single quotes', || does the concatenation, % in SQL is the same as the better known joker * .
"FieldX" || ' and ' || "FieldY" concatenates the content of FieldX with the literal " and " and the content of FieldY.

"Field1" LIKE "Field2" || '%'
compares all content of Field1 with the content of Field2 and the literal string '%' which is the joker for "anything or nothing" when used with "LIKE" instead of "=".
If the respective table field has "Giuseppe" and the search box has "giu" (stored in field "Search" of filter table "F") then the comparison evaluates to:
Giuseppe LIKE giu%

Put %giu into the search box and the comparison evaluates to:
Giuseppe LIKE %giu%

Change the query to ...
"Field1" LIKE '%' || "Field2" || '%'
... and giu in Field2 always searches for:
Giuseppe LIKE %giu%

Personally, I would leave it up to the user if he/she wants to search for giu, %giu, giu% or %giu%. Just put a note on the form that % is the joker char instead of *. That would be ...
"Field1" LIKE "Field2"
... and the user can use either one of giu, %giu, giu% or %giu%

Then they would like to find that record searching "cucine design". I suppose I should write "cucine AND design" in the search box, but is not working. Any hint on this?

If you want the user to search for anything he/she likes forget this entire thread, show your form together with toolbar "Form Navigation" and advise the user to call the built-in search tool from that toolbar.

[fabersum]

But... when do you sleep???

Thank you for the clarifications about the SQL
By the way: I was able to do what I was looking for searching for %cucine%design

ok, until you won't get bored by me I go on asking...

is there a way to make the ViewExport to filter data when the search field is empty? I mean searching through the checkboxes only?
I tried to modify the SQL from this

AND ( ( "T"."evitare" = "F"."CheckEvitare" ) OR ( "F"."CheckEvitare" IS NULL ) ) AND ( ( "T"."Cognome o ragione sociale" LIKE '%' || "F"."Search" || '%' ) OR ( "T"."Nome" LIKE '%' || "F"."Search" || '%' ) )

to this

AND ( ( "T"."evitare" = "F"."CheckEvitare" ) OR ( "F"."CheckEvitare" IS NULL ) ) AND ( ( ( "T"."Cognome o ragione sociale" LIKE "F"."Search" || '%' ) OR ( "T"."Nome" LIKE "F"."Search" || '%' ) ) OR ( "F"."Search" IS NULL ) )

but is not working

Of course the user could just put "%" in the searchbox and do the search, or I could make a brand new view that doesn't take care of the search field, but it's a chance to learn something more...

show your form together with toolbar "Form Navigation" and advise the user to call the built-in search tool from that toolbar.

hmm. Another thing to learn about. Are you talking about the "form based filters"?

Now what I am studying about is how to insert in the Filter_Contatti table two fields to be compared with "tipologia contatto" and "settore attività". I used listboxes in the data entry form for "data validation" purposes, and I would like to use the same listboxes in the FilterForm. Maybe I'll have something to ask about this soon too

By now, one more time, thank you!

A.

[Villeroy]

Train your AND/OR skills with a simple query like:

Code: Select all

SELECT "TextField" 
FROM "SomeTable"
WHERE <complex conditions>

Yes, Base supports complex, user-defined, form-based filters as well as a search-tool (scanning fields) on toolbar Form Navigation, but the filtered form would need a macro to export it's filtered results (or a may be a simple macro to manipulate the export-view?)

Regarding form-based filters: Check out field-property "Filter Proposal" together with a form based filter.