Today we are going to discuss a feature that technically isn’t part of the FileManager classes but demonstrates how you can tweak and extend them without change their core functionality, all thanks to the magic of OOP. Often on browses I like to include a generic search edit control that search multiple fields, so the end use can just type in Smith for example, and it will find any customers with Smith in the Company Name, Contact Name, Address Fields, etc.

Of course that is pretty easy to do with SQL code, but gets a bit more complicated if you also have other filter controls etc. on your browse. So I created a technic for adding the generic search to my browses using the File Manager classes so it could be done in a consistent fashion.

First you will need a small global procedure isNumber, this tell if the value you have passed in is an number. You will see how this is used later. You might be saying but wait there is already a WL function isNumberic that does that, however it returns a true for things that would cause errors in SQL for example the string “3plus2” returns a ture from isNumberic. You can add this to your existing set of global procedures.

PROCEDURE isNumber(LOCAL inValue)

// The WX isNumberic function return true for things that would cause errors in SQL !!!


ResultValue is boolean = True
// If first is + or - remove it for testing

IF inValue[[1]] = "+" OR inValue[[1]] = "-" THEN
	inValue = inValue[[2 TO Length(inValue)]]
END

// If there is anything other than 0-9 or . then it is not a number
// if there is a , then it is not a number as the , would cause SQL error on the Search anyway

FOR x = 1 _TO_ Length(inValue) 
	tmpValue is int = Asc(inValue[[x]])  
	IF (tmpValue < 48 OR tmpValue > 57) AND tmpValue <> 46 
		ResultValue = False
		BREAK
	END
END

RESULT ResultValue

For this example we are going to handle a generic search on our Company browse, which already has a few checkboxes for filtering based on company type. To that we are going to add a generic search that will look at the following fields (CompanyName, ShortName, UniqueName,Account,Notes,Contact Person, and the Address fields) so lets get started!

We add an edit control to our browse (edtSearch)

On the exit event we call GatherData() which is a local procedure that already existed to setup the filtering code for all the check boxes.

Now we create a method in our manager class called FilterStringWithGenericSearchClause (or whatever you want to call it). And start with this basic code that we will tweak shortly. Pro Tip: Add this code as a code brick and you want have to search for where you used it last to copy and paste.

PROCEDURE FilterStringWithGenericSearchClause(inSearch)

// Adds to existing filterstring for Generic search button on browses

IF inSearch = "" THEN
	RESULT Filterstring
END

FilterClause is string

StringSearch is string = [
	
]
NumberSearch is string = [

]

IF isNumber(inSearch)  AND NoSpace(NumberSearch) <> ""  THEN
	FilterClause = "[%NumberSearch%] OR [%StringSearch%]"
ELSE
	FilterClause = StringSearch
END

IF NoSpace(Filterstring) = "" THEN
	FilterClause = "WHERE " + FilterClause
ELSE
	FilterClause = "[%Filterstring%] AND ([%FilterClause%])" 
END

RESULT FilterClause

Let’s start with the easy fields (CompanyName, ShortName, UniqueName,Account,Notes) as those are actually in the Company Table. We simple modify the StringSearch on lines 11-13 to be like this:

StringSearch is string = [
 (CompanyName LIKE '%[%inSearch%]%' OR ShortName LIKE '%[%inSearch%]%' OR UniqueName LIKE '%[%inSearch%]%' OR Account LIKE '%[%inSearch%]%' OR Notes LIKE '%[%inSearch%]%' )	
]

Note the SQL % wild cards and LIKE statement being used. You can tweak the SQL for your situation, you might want only starts with, etc. I won’t go into details about how you would do that as this isn’t meant as a SQL primer.

NOTE: the NumberSearch I rarely use, but have a few times, what it is for is if you happen to have a generic search on a number field (Price) and you want to do some specific SQL code if they type a number into the generic search field.

Our already existing GatherData procedure looks like this:

All we have to do

PROCEDURE GatherData()

mgrBrowse.Filterstring = ""  // Clean any previous filter

 IF NOT ckShowAll AND NOT ckGeneral AND NOT ckSponsor AND NOT ckSupplier AND NOT ckCustomer AND NOT ckManufacturer THEN
	mgrBrowse.Filterstring = "Company.CompanyID = -1"   // Don't show anything
ELSE
	IF ckGeneral THEN
		mgrBrowse.Filterstring += [" OR "] + "Company.General = 1"
	END
	IF ckSponsor THEN
		mgrBrowse.Filterstring += [" OR "] + "Company.Sponsor = 1"
	END
	IF ckSupplier THEN
		mgrBrowse.Filterstring += [" OR "] + "Company.Supplier = 1"
	END
	IF ckCustomer THEN
		mgrBrowse.Filterstring += [" OR "] + "Company.Customer = 1"
	END
	IF ckManufacturer THEN
		mgrBrowse.Filterstring += [" OR "] + "Company.Manufacturer = 1"
	END	
	IF mgrBrowse.Filterstring <> "" THEN
		mgrBrowse.Filterstring = "([%mgrBrowse.Filterstring%])"
	END
END
IF mgrBrowse.Filterstring <> "" THEN
	mgrBrowse.Filterstring = "WHERE " + mgrBrowse.Filterstring
END

mgrBrowse.FetchData()
TableDisplay(TBL_COMPANY,taInit)

All we have to do is add a line right before the mgrBrowse.FetchData() like this:

mgrBrowse.Filterstring = mgrBrowse.FilterStringWithGenericSearchClause(edtSearch)

What that is going to do is call the method we just added to our Manager class, which in turn will build our generic search clause and then intelligently add it to any existing filter if it exist and return exactly what the FilterString should be set to.

For example if we have our checkbox set to Show All and type cell into the search box the FilterString generated is:

WHERE  (CompanyName LIKE '%cell%' OR ShortName LIKE '%cell%' OR UniqueName LIKE '%cell%' OR Account LIKE '%cell%' OR Notes LIKE '%cell%' )	

However if there the General and Customer checkboxes were checked along with typing cell in the search box we get a FilterString like this:

WHERE (Company.General = 1 OR Company.Customer = 1) AND ( (CompanyName LIKE '%cell%' OR ShortName LIKE '%cell%' OR UniqueName LIKE '%cell%' OR Account LIKE '%cell%' OR Notes LIKE '%cell%' )	)

And if we look at the browse we see all is working as it should

So let’s get a bit more fancy and add the address fields into the generic search. You might think that would be difficult since in this particular database the address fields on in a separate table called Address. However those fields have already been added to our Company record class as lookups so they could be included on the browse. Be sure to checkout this article for more information on how to do that.

A quick peek at our Company record class shows the fields included

And the FetchData method in the manager class shows the SQL joins that are linking the tables up

So lets go back and add the address fields to our FilterStringWithGenericSearchClause method. Our StringSearch definition is now

StringSearch is string = [
(
 CompanyName LIKE '%[%inSearch%]%' OR ShortName LIKE '%[%inSearch%]%' OR UniqueName LIKE '%[%inSearch%]%' OR Account LIKE '%[%inSearch%]%' 
 OR Notes LIKE '%[%inSearch%]%' OR Address.Address1 LIKE '%[%inSearch%]%' OR Address.Address2 LIKE '%[%inSearch%]%' 
 OR Address.Address3 LIKE '%[%inSearch%]%' OR Address.City LIKE '%[%inSearch%]%' OR Address.County LIKE '%[%inSearch%]%' 
 OR Address.CountryISO LIKE '%[%inSearch%]%' OR Address.PostCode LIKE '%[%inSearch%]%'
)
]

NOTE: You want to be careful about how complex you get with your generic search since all of these LIKEs and ORs are not going to be the best performing SQL statements, but for this database and use case we are fine.

So now if we run the program and type in London, we get all of the companies with London anywhere in the selected fields

You might be wondering about the one that says South Cambridge, but if we look at the details we find out that they are on London Road, which is why it was selected

Now for some real bonus material! We also want to select records based on the Contact Name which is really challenging since with this database design Contact is a child table and there can be several contacts for one company. But if you have a bit of SQL experience it’s really not that challenging at all. Again we just tweak our StringSearch in the FilterStringWithGenericSearchClause like so:

StringSearch is string = [
 (
 CompanyName LIKE '%[%inSearch%]%' OR ShortName LIKE '%[%inSearch%]%' OR UniqueName LIKE '%[%inSearch%]%' OR Account LIKE '%[%inSearch%]%' 
 OR Notes LIKE '%[%inSearch%]%' OR Address.Address1 LIKE '%[%inSearch%]%' OR Address.Address2 LIKE '%[%inSearch%]%' 
 OR Address.Address3 LIKE '%[%inSearch%]%' OR Address.City LIKE '%[%inSearch%]%' OR Address.County LIKE '%[%inSearch%]%' 
 OR Address.CountryISO LIKE '%[%inSearch%]%' OR Address.PostCode LIKE '%[%inSearch%]%'
 OR EXISTS(select * from Contact where Contact.CompanyID = Company.CompanyID and ( Surname LIKE '%[%inSearch%]%' or FirstName LIKE '%[%inSearch%]%'))
 )	
]

And now if we run it and type in Marie we get any companies with a Contact named Marie or Marie in any of the other fields.

Hopefully this has insprired you and also shown soem of the reason why we like using the FileManager classes so much. Once you have the basics in place it is very easy to extend the functionality of your application all at the class level with out having to do a bmuch of coding at the UI level.

For review and clarity the final version of our FilterStringWithGenericSearchClause method is:

PROCEDURE FilterStringWithGenericSearchClause(inSearch)

// Adds to existing filterstring for Generic search button on browses

IF inSearch = "" THEN
	RESULT Filterstring
END

FilterClause is string

StringSearch is string = [
 (
 CompanyName LIKE '%[%inSearch%]%' OR ShortName LIKE '%[%inSearch%]%' OR UniqueName LIKE '%[%inSearch%]%' OR Account LIKE '%[%inSearch%]%' 
 OR Notes LIKE '%[%inSearch%]%' OR Address.Address1 LIKE '%[%inSearch%]%' OR Address.Address2 LIKE '%[%inSearch%]%' 
 OR Address.Address3 LIKE '%[%inSearch%]%' OR Address.City LIKE '%[%inSearch%]%' OR Address.County LIKE '%[%inSearch%]%' 
 OR Address.CountryISO LIKE '%[%inSearch%]%' OR Address.PostCode LIKE '%[%inSearch%]%'
 OR EXISTS(select * from Contact where Contact.CompanyID = Company.CompanyID and ( Surname LIKE '%[%inSearch%]%' or FirstName LIKE '%[%inSearch%]%'))
 )	
]
NumberSearch is string = [

]

IF isNumber(inSearch)  AND NoSpace(NumberSearch) <> ""  THEN
	FilterClause = "[%NumberSearch%] OR [%StringSearch%]"
ELSE
	FilterClause = StringSearch
END

IF NoSpace(Filterstring) = "" THEN
	FilterClause = "WHERE " + FilterClause
ELSE
	FilterClause = "[%Filterstring%] AND ([%FilterClause%])" 
END

RESULT FilterClause

and our GatherData() procedure

PROCEDURE GatherData()

mgrBrowse.Filterstring = ""  // Clean any previous filter

 IF NOT ckShowAll AND NOT ckGeneral AND NOT ckSponsor AND NOT ckSupplier AND NOT ckCustomer AND NOT ckManufacturer THEN
	mgrBrowse.Filterstring = "Company.CompanyID = -1"   // Don't show anything
ELSE
	IF ckGeneral THEN
		mgrBrowse.Filterstring += [" OR "] + "Company.General = 1"
	END
	IF ckSponsor THEN
		mgrBrowse.Filterstring += [" OR "] + "Company.Sponsor = 1"
	END
	IF ckSupplier THEN
		mgrBrowse.Filterstring += [" OR "] + "Company.Supplier = 1"
	END
	IF ckCustomer THEN
		mgrBrowse.Filterstring += [" OR "] + "Company.Customer = 1"
	END
	IF ckManufacturer THEN
		mgrBrowse.Filterstring += [" OR "] + "Company.Manufacturer = 1"
	END	
	IF mgrBrowse.Filterstring <> "" THEN
		mgrBrowse.Filterstring = "([%mgrBrowse.Filterstring%])"
	END
END
IF mgrBrowse.Filterstring <> "" THEN
	mgrBrowse.Filterstring = "WHERE " + mgrBrowse.Filterstring
END
mgrBrowse.Filterstring = mgrBrowse.FilterStringWithGenericSearchClause(edtSearch)
mgrBrowse.FetchData()
TableDisplay(TBL_COMPANY,taInit)

• wxFileManager