Hi David,

it's a bit long to get into the reasons why, but it can't be done like that.

I've come up with some code that seems to work OK:

LPARAMETERS tc_CardsToBeSent, tc_MergedCardsToBeSent, tnDataSession

LOCAL lnOldDataSession
m.lnOldDataSession = SET("Datasession")

SET DATASESSION TO (m.tnDataSession)

SELECT * ;
	FROM (m.tc_CardsToBeSent);
	WHERE 1=0;
	INTO CURSOR (m.tc_MergedCardsToBeSent);
	READWRITE 

INDEX ON edc_cardno TAG edc_cardno
	
SELECT (m.tc_CardsToBeSent)
AFIELDS(laFields, m.tc_CardsToBeSent)
SCAN 
	SCATTER MEMVAR memo 
	IF SEEK(m.edc_cardno, m.tc_MergedCardsToBeSent)
		FOR lnFieldCount = 1 TO ALEN(laFields,1)
			IF NOT EMPTY(EVALUATE(laFields[m.lnFieldCount,1]))
				replace (laFields[m.lnFieldCount,1]) WITH EVALUATE(laFields[m.lnFieldCount,1]);
					IN (m.tc_MergedCardsToBeSent)
			ENDIF 
		ENDFOR 
	ELSE
		INSERT INTO (m.tc_MergedCardsToBeSent) FROM MEMVAR 
	ENDIF
ENDSCAN

Can anybody see any possible problems with this code?


>Frank,
>
>Why can't the code that does the second batch of inserts first check to see if the Field1 row already exists and simply update the Field4 and Field5 columns?
>
>>I have a system where a user can enter different batches of data at different times. I then need to merge the different batches together into one record based on an identifying field value.
>>
>>For example, I have a table with these fields:
>>
>>Field1, Field2, Field3, Field4 and Field5 (not their real names just used for the example).
>>
>>Field1 is the field that identifies a particular record.
>>
>>User 1 enters data in Field1, Field2 and Field3. This gets stored in one record. User 2 then enters Field1, Field4 and Field5, which gets appended to the same table as User 1's record.
>>
>>I then need to go through this table and pull out all the records that have the same value in Field1 and merge them into one record. I won't know which fields have values in for each record.
>>
>>Has anybody done anything similar and have any suggestions as to the best and most generic way to approach this? The part that I am seeing as being tricky is ensuring that I don't overwrite a field with an empty value.