Hello guys.
I was reading a lot of 'old threads', before deleting them from my computer.
And i came across this one, which answers did not satisfy me :
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
Michael Savage : (tuesday, 20 septembre 2011 19:03)
I have a table, with two indexes. (In a cdx file)
I need to know the new place in the table when the order changes.
ie if it is the second record in the table in sort order #1, what is it
in order #2.
Let say that in order 1 it is the 2nd record, but in order #2 it would
be the fifth record.
How do I find out when I switch order, what position it's in?
Can't use recno(), what other functions is there.
Mike
------------------
Fred Taylor :
There are no functions that return a position relative to the index. If
there aren't too many records, you may be able to SELECT RECNO() FROM table
ORDER BY your_order INTO ARRAY xxxx, then search the array for the RECNO()
you want. The found index into the array will be your relative position.
This is limited to about 65000 records for VFP8 or earlier. VFP9 is only
limited by available RAM.
In further thinking, you could do the SELECT ... INTO CURSOR and find your
RECNO() field value. The RECNO() of the cursor would be the relative
position. Should work in all versions of VFP.
-----------------
Tracy Pearson :
Michael,
The only sure way I came up with is using some select statements
LOCAL currentrow, record_position_01, record_position_02, keyvalue
SET ORDER TO 2
m.currentrow = EVALUATE(KEY(1))
m.keyvalue = KEY(1)
SELECT (KEY(1)) FROM (ALIAS()) TO SCREEN NOCONSOLE WHERE
&keyvalue.<=m.currentrow
m.record_position_01 = _TALLY
SET ORDER TO 2
m.currentrow = EVALUATE(KEY(2))
m.keyvalue = KEY(2)
SELECT (KEY(2)) FROM (ALIAS()) TO SCREEN NOCONSOLE WHERE
&keyvalue.<=m.currentrow
m.record_position_02 = _TALLY
?m.record_position_01,m.record_position_02
I'm not sure why position is needed, unless you are dealing with a TreeView
type of positioning.
-----------------
Alan Bourke :
Select mytable
set order to tag order1
goto top
lvKeyvalue = mytable.field1
locate for field1 != lvKeyvalue
lnResult1 = Recno()
set order to tag order2
goto top
lvKeyvalue = mytable.field1
locate for field1 != lvKeyvalue
lnResult2 = Recno()
-------------------------------------------------------------
-------------------------------------------------------------
Well, you know,
there IS a solution using VFP - SQL and valid RECNO() abilities
(when recno() can be formally anchored within a data subset) :
Using the following example :
[VFP]
CREATE CURSOR test ;
(lib c(10),mt i)
INSERT INTO test VALUES ("john",10)
INSERT INTO test VALUES ("jennifer",15)
INSERT INTO test VALUES ("stella",25)
INSERT INTO test VALUES ("sophia",40)
INSERT INTO test VALUES ("greg",50)
INSERT INTO test VALUES ("jeff",20)
[/VFP]
(in this example the tow indexes criterias are set on 'lib' and 'mt')
We can create a dataset collating all the information needed,
like this :
[VFP]
SELECT xx.lib, xx.rank1,yy.rank2 FROM ;
(SELECT lib,mt, RECNO() as rank2 FROM ;
(SELECT lib,mt, RECNO() as rank FROM test ;
ORDER BY 2) as yy) as yy, ;
(SELECT lib,mt, RECNO() as rank1 FROM ;
(SELECT lib,mt, RECNO() as rank FROM test ;
ORDER BY 1) as xx) as xx ;
WHERE xx.lib = yy.lib
[/VFP]
Then, it is possible to restrict the result set using additional
WHERE clauses like AND lib = "john", or rank1 = 3.
Furthermore, using macrosubstitution, it is possible
to completely parametrize the request using EVAL(KEY(n)) ...
SQL is great.
Have good fun !
Gérard.
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/EED16589D39B498BA4E5009F9FECE1A2@MuriellePC
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
