Brian,
Thank you for the detailed and very helpful reply.
I think I am getting most of this but there is one point where I am lost.
The part I get relates to eg if I am searching for a range between Smith
21 to Smith 24, I would break down Smith 21 into 2 elements (RV11 and
RV12 with subfields), and then Smith 24 into 2 elements (RV 21 and RV 22
with subfields). This part of the explanation I understand.
But where I am have difficulty is the following paragraph:
"On each record you are testing you need to parse the record into 2 fields
say RV31 for the text and RV32 for the numeric, similar to how you parsed
the searchtext ranges above"
Thereafter I think I understand what is happening, but could you explain the
paragraph in a little more detail?
Victor Warner.
Brian Hancock wrote:
Hi Victor,
Lets assume that as well as Smith you have Jones, Obama as well as a stack
of other names, and they all take a similar format eg Jones 23
The general idea would be to parse the two search range criteria into the
two components and then compare those with the split up data in the records
So for example with the searchtext assign them to two RVs
RV11=
subfield[searchtext1;" ";1]
and the numeric component
RV12=
convert["GZ9";subfield[searchtext1;" ";2]]
and similarly with the 2nd searchtext2 into RV 21 and RV22
Remember to scope the convert "GZ9" to fit the largest expected numeric, I
would probably allow a factor of 100 so if you expected less than 99 Smiths,
then I might still opt for GZZZ9. (If Bill Gates had scoped DOS for 100
times more than the 640k he thought was enough, today it still wouldn't be
anywhere near enough)
The searchtext1 and 2 (ie upper and lower range), can come from a Prompt for
RV or it could be from a panel, it really is academic where it comes from
On each record you are testing you need to parse the record into 2 fields
say RV31 for the text and RV32 for the numeric, similar to how you parsed
the searchtext ranges above
To provide a match you need to a store the test result into an RV, and the
formula would look something like:
RV100=
((RV31=RV11 AND RV32>=RV12) OR RV31>RV11)
AND
(RV31<RV21 OR (RV31=RV21 AND RV32<=RV22))
/* gee I hope I got that formula right, give it a go */
This will return a true 1 or a false 0 depending on whether the record is
inside or mathing either the lower or upper range
And then use SkIP record if RV100 is false 0
This then allows disjointed search ranges, so that a search range of
Jones 4 to Smith 3, would output :
Jones 4
Jones 5
Jone ...
Obama 1
Obama 2
Obama ...
Smith 1
Smith 2
Smith 3
You might want to add some more sophistication of checking that the input
ranges are in the right order and swapping them if necessary, or checking
that they are in the correct format. If the database is large you might want
to use an indexed sort, and skip to the first RV11, and then Stop the
subreport if the main logic test for RV100 is false rather than looping
through lots of records.
I hope this helps.
Oh yes one other point, if you only ever wanted to search through Smiths or
to search through Jones but not have searches overlapping then you could
make a much simpler search expression, by recombining the search expression
into just string eg
RV 11=
Cat.t[
Subfield[Searchtext1;" ";1];
apply.format["GZZZ9";convert["GZZZ9";Subfield[Searchtext1;" ";2]]]
]
This would reformat Smith 23 as Smith0023 and once similarly done for the
upper searchtext2 and the data you are looping though then you could just
search on the text. RV31>=RV11 and RV31<=RV21, but you would not be able to
use the SKIP TO and STOP Report so easily
Regards
Brian
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Victor Warner
Sent: Tuesday, 24 February 2009 11:14 PM
To: Dataperfect Users Discussion Group
Subject: [Dataperf] How to run report which selects range in alpha
numericfield?
I would like to run a report on a panel which selects a range of records
where the selection is done on an alphanumeric field.
The field in question contains text entries such as
Smith 1
Smith 2
Smith 3
Smith 4
and so on until
Smith 20
Smith 21
Smith 22
Smith 23
Smith 24
If I wished to select the range Smith 20 to Smith 22, using the Specify
Range in Search Options for Reports does not work (because it is a text
field).
Using a formula such as min[P1F1="Smith 20"] will produce
Smith 2
Smith 20
Smith 21
Smith 22
Smith 23
Smith 24
and I get the same result if I specify, say min[P1F1="Smith 21"].
I have read Ralph Alvy's Mastering DataPerfect, Formulas chapter on
"obtaining perfect matches and identiy operator", but hat does not
appear to make any difference.
Help with this would be much appreciated.
Victor Warner.
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
