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: ProFox@leafe.com

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.