Thanks David,
I'll see if I can understand what the code is doing. At this point in time I don't know the size of the tables I'll be dealing with either.
>Frank,
>
>I don't know how big the tables are that your dealing with here, but rather than all those seeks and replaces I think I'd tackle it like this:
>
>
>* warning: untested pseudocode off the top of my head
>
>select mergetable
>index on field1 tag field1
>go top
>lnKey = mergetable.field1
>lnFields = afields( laFields )
>scatter to laData
>scan
> if ( lnKey != mergetable.field1 )
> insert into mergedtable from array laData
> lnKey = mergetable.field1
> scatter to laData
> else
> scatter to laAddRow
> for i = 2 to lnFields
> if ( ! empty( laAddRow[m.i] ) )
> laData[m.i] = laAddRow[m.i]
> endif
> endfor
> endif
>endscan
>insert into mergedtable from array laData
>
>
>>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.
