Using COUNT inside SCAN? 3

[JasonZ10]

Hi,

I'm trying to modify duplicate entries in a Visual Foxpro standalone DBF Table, and the simplest/most efficient way I could conceive is not working. Here is the code:

select tablename
scan
	tempid = userid
	COUNT TO tjz FOR tempid $ tablename
	if tjz > 1
		replace userid with fixid(userid)
	endif
		
endscan

Everything within the scan seems to work, but when the program reaches endscan for the first time, it breaks the scan without any errors. If I comment out the COUNT line it cycles through just fine, but obviously doesn't function as I need it to.

Is there a way to fix this, or does anyone know of an efficient workaround? I'm trying to avoid nested loops as I'm working with pretty massive tables, and I don't want to just sort by userid then store the previous one, as there can be multiple duplicates and I'm not just adding an incrementing number.

Thanks!

 

[mm0000]

select tablename
scan
	tempid = userid
	COUNT TO tjz FOR tempid $ tablename
	if tjz > 1
           locate for userid = tempid    		
           replace userid with fixid(userid)
	else
           locate for userid = tempid
        endif
endscan

 

[Mike Lewis]

The reason that the SCAN is terminating is that the COUNT moves the record pointer to the end of the table. So after the first time round the loop, you are at EOF(), so the ENDSCAN kicks in.

Another problem would occcur if the table is indexed on UserID. If it is, replacing the UserID while in the loop is going to affect the order of the records that are scanned.

It should be easy to come up with an alternative approach, but before I try to do that, could you explain exactly what you are trying to achieve. It looks like you want to change the uesr ID for all records where there is more than one instance of the user ID being part of the table name, but that doesn't look right to to me. Could you clarify.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,

In order not to have to scan "massive tables", I'd try something like below

LOCAL ARRAY laOldUserIds[1]

SELECT COUNT(UserId), UserId FROM TableName GROUP BY 2 HAVING COUNT(UserId) > 1 INTO ARRAY laOldUserIds

IF  ALEN(laOldUserIds) >= 2
	SELECT tablename

	FOR i = 2 TO ALEN(laOldUserIds) STEP 2
		
*!*			LOCATE OR INDEXSEEK laOldUserIds[i-1, 2] AND then REPLACE
		
	ENDFOR
ELSE

= MESSAGEBOX("No twins found", 64, "Searching for twins") 

ENDIF 

more code

Nevertheless the question remains: how many of the old IDs you'd like to replace - ALL, ONE or a FEW. You'll have to adapt the code accordingly.

hth

MarK

 

[Mike Lewis]

Another approach would be to replace the COUNT with SELECT. Although SELECT also moves the record pointer, it operates on a second instance of the table, so it won't disturb the SCAN loop.

In other words, instead of this:

COUNT TO tjz FOR tempid $ tablename

do this:

SELECT COUNT(*) FROM TableName WHERE tempid $ tablename INTO ARRAY laCount
tjz = laCount(1)

But I'm still confused, not least regarding "tablename". You seem to be using that as an alias of a table (in your first line of code) and also as a variable which holds a value that you are comparing to the ID.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JasonZ10]

Great, thank you very much mm0000 and Mike Lewis, these are exactly the types of solution I was looking for.

mjcmkrsr, I appreciate your help! The code you shared is a very resourceful way to solve my issue. I'll definitely be using it/something similar on my larger datasets, and implementing part of it in this script

Mike Lewis, the confusing extra variable is just a result of the data I'm using. The userid key shares names with the .dbf so I added the variable to make the code a bit easier to understand.