My first attempt at a useful project in EWB is a simple module, but I need to learn a bit more about EWB before making silly decisions in the design of a major project.

The module is to be used on a smartphone or desktop and will be used to search for "contacts" within 3 tables.  e.g. missed call on the phone so want to look up the number to find out who it was.  e.g. got a phone message to call "Sue" ... Hmmm..  "Sue" who?

If you use Skype, something like search for contacts, where you start entering a name or number and it pops up all matching items so far (not just the initial characters).

To I have concocted this view of my EDB tables like this:-
--------------------------------------------------------------------
CREATE VIEW "SearchALL" AS
SELECT SortKey COLLATE UNI_CI, LastName COLLATE UNI_CI AS Name, ContactName COLLATE UNI_CI,
      DayTimePhone, AfterHoursPhone, MobilePhone, 'L' AS Type, LandlordCode AS Code
FROM Landlords
WHERE not Hide
UNION ALL
SELECT SortKey, LastName AS Name, ContactName, DayTimePhone, AfterHoursPhone, MobilePhone, 'T', TenantCode
FROM Tenants
WHERE not Hide
UNION ALL
SELECT SortKey, Name, ContactName, DayTimePhone, AfterHoursPhone, MobilePhone, 'S', SupplierCode
FROM Suppliers
WHERE not Hide
ORDER BY SortKey
DESCRIPTION 'Used to search for a "person" in the Landlord, Tenants and or Suppliers'
VERSION 1.00
=======================================================================

and was going to use a query like this (assuming '0274' had been typed so far):-
-----------------------------------------------------------------------
SELECT * FROM SearchALL
WHERE SortKey LIKE '%0274%'
  OR Name LIKE '%0274%'
  OR ContactName LIKE '%0274%'
  OR REPLACE( '-' WITH '' IN REPLACE( ' ' WITH '' IN DayTimePhone)) LIKE '%0274%'
  OR REPLACE( '-' WITH '' IN REPLACE( ' ' WITH '' IN AfterHoursPhone)) LIKE '%0274%'
  OR REPLACE( '-' WITH '' IN REPLACE( ' ' WITH '' IN MobilePhone)) LIKE '%0274%'
=======================================================================

BUT, BUT,  BUT  I go to EWB to define the dataset and it seems that I can only use tables and Queries and NOT Views.

Initially I was stumped, but while typing this I have thought of doing the same thing by using my above View as a query and then using a filter on the result set like the query above.

Now I'm stumped again as I can't see a way set a filter on my query.

Am I missing something?  Is there a more straight forward way?

An average user might have about 1500 rows in the view, so I want to avoid sending too much of that down the line at one time.

AND will views be in the much anticipated EWB 2?

Cheers

Jeff

Jeff,

you should use a query like this:

SELECT * FROM SearchALL where SortKey LIKE {SortKey=''} OR Name LIKE
{Name=''} ...

Then set the params in EWB accordingly:

MyDataset.Params.Clear ;
MyDataset.Params.Add ('SortKey =''%' + value + '%''');
MyDataset.Params.Add ('Name =''%' + value + '%''');
Database.Load (MyDataset);

Uli

Uli Becker wrote:
-------------------------------------------------------------------------
SELECT * FROM SearchALL where SortKey LIKE {SortKey=''} OR Name LIKE
{Name=''} ...

Then set the params in EWB accordingly:
---------------------------------------------------------------------

Thanks, Uli.

I was making things too complicated again.  Been getting to grips with DBISAM3>EDB changes and now EWB as well ... poor old brain.

Cheers and Thanks again

Jeff