In article <48a52ba04c.B21306@...>,
<B21306@...> wrote:
> > You would like to search that field by allowing the user to specify
> > strings such as "5M" for 5 months, "2Y/J" for 2 years/jars etc.
> >
> > For example, writing "> 5M" in the query form would search the file for
> > all records where the "age" was more than 5 months.

> So it is.

> A Example is search for all calf is > 6 months , or search for a cow <10
> years , or in my new Apps search for food in my ice refrigerator >
> 8months.

OK. I've been having a little think about this and I believe it will be
possible, though a bit tricky.

First of all, is it necessary to store an age field with the file? It is
directly dependant on another field (date_of_birth) so can be easily
calculated whenever you need it, and is out of date almost immediately, so
needs constant re-calculation to be relevant.

My own (VH) opinion, would be to ditch the "age" field, and calculate the
age on the fly whenever you need it. If you look at the S-Base2 Tutorial
manual on page 158, or at the entry for "print" command in the Reference,
you can use the "interval" format - /i - for -displaying- ages. So
assuming today is 16th April,

print (@date - @timefoy(@date)):/i
3M1w

which means 3 Months, 1 week. Note though that although this is very quick
and simple to implement, it isn't absolutely accurate, as it assumes all
months are of equal length (31 days in this case). Most of the time it's
not that critical, but if you really need accurate ages for display
purposes then use the function I sent you earlier.

Searching for "age" though, using aY bM cD in the search expression is a
bit more complex, whether or not you save an "age" field with the file.

First of all, we have to convert the "aY bM cD" into something S can use
to compare with. Previously, you mentioned that using a search expression
of "> 5M" would return values of "14D" as acceptable. That's because 14 is
greater than 5 - S has no idea that the letters M and D have any meaning
in this context and simply compares the numbers.

Secondly, to allow the user all of the flexibility of the current query
operators (contains, equals, more than, etc) it would be useful not to
meddle with that. What we need is for the user to input something like

age = 5M {or}
age is greater than 1Y {etc}

and for our custom function only to process the bits it needs to.

I have attached a function which will do this for you.

The instructions for using it are also in the attached file, as some of
the lines are quite long.

When you set up a query using SAM, you are provided with 2 extra entry
fields - one for the record number of the file being searched, the second
for an arbitrary expression which can be applied to all the records in
turn, to see if they match the expression. This second extra field is what
I'm using - the function proc age_query_convert converts whatever is
written in this final slot to something S-Base can work with. However, it
needs some cooperation from the user, as detailed in the instructions.

For now, until you are happy with it, I've simplified the process and
sacrificed absolute accuracy, allowing constant periods for years and
months. If you want to, these can be made more complex.

Let me know how it works. Be careful though - I haven't had a chance to
test it fully yet.

Regards,

--
Robin Hampshire
robin@...

| Find the procedure resource for the query card, where the
| query search is launched, and find the line which starts:
|
| query load array <queryhandle>, <array_name>, <flags, etc>
|
| add the following lines /before/ this one:

local var1
for var1 = 0 to <number of lines you have in query - 1>
array_name[var1][@fieldcount(filename) + 2] = proc
age_query_convert(@trim(@ltrim array_name[var1][@fieldcount(filename) +
2]),"r_froster_card.g_date")
next

| What we are doing here is intercepting the array containing the
| query lines before it is passed to the query, and changing them
| if necessary.
|
| you will have to alter the array_name to suit, and discover
| what variable SAM (or you) uses to hold the number of lines in the
| query. What the lines above do is go through each line of the query
| and if necessary convert the final expression into one that S-Base
| can use. After this has happenned, "query load array" loads the
| amended query lines into the query
|
| Also change "r_froster_card.g_date" to whatever your date of birth field
| is called. Don't forget the quotes "" as you must pass this field_name
| as a literal string. Your example (from the excerpt you sent recently)
| would be "r_froster_card.g_date"
|
| The procedure age_query_convert goes through the string passed to it.
| If it starts with "age", then it processes it, otherwise it sends it
| back unchanged.
|
| The way I have written it, you MUST put a "$" sign on each side of the
| "aY bM cW dD" sequences, so that the procedure knows what to look for.
|
| For example, the following strings typed into the query "expression" field
| would return:
|
| age equals $1d$
| returns "(@date - r_froster_card.g_date) equals 1"
|
| age is between $1j 3m 2w 1d$ and $1m 2d 1w$
| returns "(@date - r_froster_card.g_date) is between 470 and 39"
|
| some_other_expr_that_doesnt_start_with_age
| returns "some_other_expr_that_doesnt_start_with_age"
|
| age equals 100
| returns "(@date - r_froster_card.g_date) equals 100"
|
| age equals $rubbish$
| returns "(@date - r_froster_card.g_date) equals 0"
|
| Don't forget to use two "$"'s for each sequence of ages.
|
| Another thing to watch is that now there is an expression which has to be
tested
| against every record, you have to turn off "constant expressions" in the query
| resource. If necessary, either turn it off in the query resource itself or
change
| the <flags> of query load array to suit. If you don't turn it off, then once
the
| query is run, it will only be evaluated once for the first record, and
| not for any of the rest, with the result that either every record will be
| accepted or every record rejected.


def proc age_query_convert(string, dob_field)
local rtn_str = "", proc_str = string, process_age_data

def proc process_age_data(age_str)
local y = 0, m = 0, d = 0, w = 0, temp_str = ""
| go through each letter looking for digits.
repeat
if "0-9" >=< @left(age_str,1) {first letter is a digit}
temp_str += @left(age_str,1)
else
| no more digits in this sequence, now look for Y/J, M, W, D
case @toupper @left(age_str,1)
when "Y", "J"
y += @val(temp_str)
when "M"
m += @val(temp_str)
when "W"
w += @val(temp_str)
when "D"
d += @val(temp_str)
endcase
temp_str = "" {reset the temp_str for next pass}
endif
age_str = @right(age_str,@len(age_str) - 1)
until !@len(age_str)
| we should now have numeric values for the years, months, weeks and
| days required. We have to convert all these to days, and return them
| as a string. This following sequence assumes constant values for
| years(365) and months(30) but can be changed for absolute accuracy if
| required
return @str(y * 365 + m * 30 + w * 7 + d)
endif
enddef


| if the string passed to the function starts with the word
| "age" then process it, otherwise send it back unchanged
| after all, the user might want to search on a non age-related
| expression.
if proc_str >* "age"
| first thing to do is remove the word "age" from string
| and start the return_string with an acceptable expression
proc_str = @right(proc_str, @len(proc_str) - 3)
rtn_str = "(@date - " + dob_field + ")"
| now we will search for instances of "$aY bM cW dD$".
| This next bit will fail if the delimiters "$" are not
| in pairs.
| There may be more than one of them, but we must also
| allow for nil instances, such as the expression
| "age equals 100", where the user has done the conversion for us.
repeat
if @instr(proc_str,"$") {a delimiter has been found}
| add anything to the left of the delimiter to the return_string
rtn_str += @left(proc_str, @instr(proc_str, "$") - 1)
proc_str = @right(proc_str,@len(proc_str)- @instr(proc_str,"$"))
| process the bit between the delimiters
rtn_str += proc process_age_data(@left(proc_str,
@instr(proc_str,"$")-1))
proc_str = @right(proc_str,@len(proc_str) - @instr(proc_str,"$"))
endif
until !@instr(proc_str, "$")
return rtn_str + proc_str
else
return proc_str
endif
enddef