-------- Originele bericht --------
Onderwerp: [users] OO Base Specialists... Search Queries...
Van: Troy <[email protected]>
Aan: [email protected]
Datum: Mon, 17 Aug 2009 19:12:04 -0400
> Good evening, everyone:
>
> I've been a long-time user of OpenOffice and recently rejoined this great
> list in hopes that someone may be able to assist me with a little project...
>
> Being a tech, not a developer or database admin, I'm hoping someone can give
> me a little guidance with this troubling step in a project I've been working
> on...
>
> I have a client that's asked me to design a small, simple database where
> they can keep track of their customers... Names, addresses, etc. I've
> generated simple queries to search by particular data, let's say to list
> customers in a particular state, then by city. I've tied these to reports,
> and everything is working beautifully.
>
> However, we are looking for a way to generate a report based off of certain
> search criteria that will be changing on each search. Let's say, if we
> wanted to list each client with the last name of Jones... We'd like to have
> a search box, type in the name "Jones" and the particular field we are
> searching, and then generate the report accordingly...
>
> If anyone has any idea how this can be done, if this can even be done, I
> would greatly appreciate someone throwing me a bone. Digging through
> documentation, different forums has produced nothing useful as of yet.
>
> Thanks in advance for any assistance.
>
>
This type of questions can be better asked at
http://user.services.openoffice.org. I did something like that a while ago.
1. I determined the general search criteria for each field: e.g.
equal(=), BETWEEN, LIKE
2. Made sure that for each criteria there is a possible entry that
selected all possible values for that field. The trick here is that you
can change equal into an LIKE. E.g. field LIKE '%' selects all and LIKE
'One Match' selects one record. Note also that HSQLDB (and other
databases) implicitly convert all kind of data types to match with LIKE
operator. I tried CHAR, INT, NUMBER and DATE. The latter seems to work
when I assume 'yyyy-mm-dd' and use e.g. LIKE '%-12-%' to catch all
December dates.
3. Create an table that holds all the filter fields. Note the field type
for all LIKE-filters is (VAR)CHAR. For the between use the matching
field type.
4. Create a form for this filter table and give each field the default
value so that all data is selected.
5. Create a sub form that shows the real data and join it against the
filter-table.
6. You might support multiple records in the filter table so 'searches'
can be saved. In that case give the filter table an ID and a description
as well.
7. Use the search-ID as relation between the main and sub-form.
Note: I prefer not to work with NULL values in this case. But I think it
can be done via check-boxes next to each filter field.
A query might look like (table definition can easily be reconstructed
from query)
SELECT N1."ID", N1."TEXT_FIELD", N1."INT_FIELD", N1."DATE_FIELD"
, N1."NUMBER_FIELD", N1."DOUBLE_FIELD"
FROM N1
inner join N1_filter
ON N1."TEXT_FIELD" LIKE N1_filter."TEXT_FIELD"
AND N1."INT_FIELD" LIKE N1_filter."INT_FIELD"
AND N1."NUMBER_FIELD" LIKE N1_filter."NUMBER_FIELD"
AND N1."DATE_FIELD" LIKE N1_filter."DATE_FIELD"
AND N1."DOUBLE_FIELD" BETWEEN N1_filter."DOUBLE_LOW" AND
N1_filter."DOUBLE_HIGH"
AND N1_filter.ID = :filter_id
I used e.g. the following filter criteria:
N1_filter."TEXT_FIELD" = '%'
N1_filter."INT_FIELD" = '1%' selects 1, 10, 134 etc.
N1_filter."NUMBER_FIELD" '%.34'
N1_filter."DATE_FIELD" = '%-12-%
N1_filter."DOUBLE_LOW" = 3.0
N1_filter."DOUBLE_HIGH" = 4.0
N1_filter.ID = 0
Success,
Erik
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
