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
