Indexseek

[MaryG]

Hi,

I am trying to use Indexseek() to find duplicate records in the Valid Event of a field. No matter what I do, Indexseek() is returning .F. even though I know the record exists in the table.

Has anyone used this successfully?

Thanks for any help

 

[rajeevnandanmishra]

Hi there,
There are a few other things to get noticed while you use IndexSeek().
First and most important thing is having a index on the table (either through IDX or CDX) and to open/set the index to a correct tag.
Second Check if SET EXACT is ON. Because if set exact is on it is going to search the exact expression.
Third, if you had not given the alias and index parameters in IndexSeek(), it will search in the current workarea and for the current open/set index/tag.

 

[ChrisRChamberlain]

MaryG

Just to add to Rajeevnandanmishra's comments, you could try a convential SEEK in the command window first to establish you are locating the record.

Then, if successful, try an INDEXSEEK() in the command window.

If that is successful, then you need to examine the code in your Valid() event.

Something that's easily overlooked is if the index is on UPPE(fieldname) and your variable is mixed or lower case.

Once you have it working, you will find it an invaluable function.

Chris

 

[ramani]

Hi MaryG

You can use the following to get your duplicate records.
Replace the myDBF, myKeyField suitably.

SELECT a.* FROM myDBF a WHERE ;
EXISTS (SELECT b.* FROM myDBF b ;
GROUP BY b.myKeyFiled ;
HAVING COUNT('b.myKeyField')=1 ;
WHERE a.myKeyField=b.myKeyField) ;
ORDER BY a.myKeyField

Hope this helps

ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[ramani]

Hi MaryG

In above there is a small error and so you will get all nonduplicate records in above. The following will give you the duplicated ones.
========================================================
You can use the following to get your duplicate records.
Replace the myDBF, myKeyField suitably.
-----------------------------------------------
SELECT a.* FROM myDBF a WHERE ;
EXISTS (SELECT b.* FROM myDBF b ;
GROUP BY b.myKeyFiled ;
HAVING COUNT('b.myKeyField')>1 ;
WHERE a.myKeyField=b.myKeyField) ;
ORDER BY a.myKeyField
-----------------------------------------------
Hope this helps


ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[MaryG]

Thanks for your comments. I am able to kind of get it to work, but not correctly. What I have found is that indexseek() will work correctly if a value is hardcoded in there - the record will be found if I type a valid value in there, the record won't be found if I type an invalid value. I have tried using the textbox.value, tried moving this to a variable, tried using quotes just in case, but when I use a variable in the indexseek() command, it doesn't work consistently. I am using SET EXACT ON so I assume it will only find the record if the exact value I type in matches what is in the file, but sometimes it reports that a record is found when it doesn't exist in the file. Maybe I am doing something stupid, but I can't see why it would work when I hardcode a value to seek and not work when I use a variable.

Thanks for your code Ramani but I can't use it because I don't want the record pointer to move. I am adding a record to the table when I am checking the value, and if I use a select statement or locate I can figure out if there is a duplicate value in the table, but then if it is a valid value and I go ahead and hit 'Save' the record isn't saved to the table. If I remove my validation, it is saved so I assume that because the record pointer moves, the record can't be added properly? That is why I wanted to use and indexseek.

Thanks for your help anyway. I may have to think of another way to do this.

Mary

 

[wsobers]

Hi Mary,
If you just want to know if a record with a value is already in your table, use
dimension tmp_array[1]
tmp_array[1] = 0
select count(*) ;
where lookupfield = value ;
into array tmp_array

This will not move the record pointer, and the number of times the value is found will be in "tmp_array[1]"

Wayne