Hi Victor,
It was probably my wording that was confusing, it was a very late and I
rushed through the reply.
There are two scenarios.
1. Where you need to include everyone between say Jones 3 and Smith 24, and
anyone else that you would humanly logically expect to be inside that range.
2. Where you are just searching on a range within the same name, eg Smith 21
to Smith 22
If you want to treat the Smith part as text and the part after which looks
like a number but it currently treated as text (ie "Smith 2" is somewhere in
between "Smith 17" and "Smith 23" then you need to reprocess the numeric
looking part into being a number eg convert["GZZZ9",numeric_looking_text]
and then do the comparisons on the two separate parts, ie compare the text
parts and then compare the numeric parts
In the case of scenario 1, you would need to split the search_text1 lower
part of the range into 2 RV's and the search_text2 into 2RV, and then on
each record in the report body do the same with the field you are searching
for and separate it into the text and numeric parts, and then use the
formula to see if the field values fall between the lower and upper bounds./
You formula will be comparing the 6 RV's that were created.
In the case of Scenario 2, you are still using the same basic concept,
except that you recombine the numeric part back to the text part by again
converting the numeric looking part into the number with the convert
function, but then turning it back to a text that can be correctly camopared
as text. So for the "Smith 17" to "Smith 23" search that was also finding
"Smith 2", you are now really searching for "Smith 0017" to "Smith 0023",
and when treated as text "Smith 0002" does not fall between them, so you
have a simple search formula and only need 3 RV's.
I should have also mentioned that if you want your output to be sorted the
same way, then you might need to create two hidden fields in the panel which
do the same breaking up of the text and numeric part, and create an index on
these two fiels and use that index in the report.
Does this clear it up... If not then I will send you an STR and you can
enter some data and then look at the report
Regards
Brian
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Victor Warner
Sent: Wednesday, 25 February 2009 1:53 AM
To: Dataperfect Users Discussion Group
Subject: Re: [Dataperf] How to run report which selects range inalpha
numericfield?
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
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
