How to: Merge more than one record and the memo field

[keepingbusy]

All

Where to start?

Ok, I have a table with let's say 50 records. Within that table are some records (They are orders) from the same person for example three. All three seperate orders have different prices and different order requirements but I want to be able to merge the three orders into one, total the price and also the order details into that one memo field in one record.

I have found a way of using a Do while not eof() procedure that accomplishes this but it falls down when the procedure loops back to the original table.

Can this be done with an SQL statement or would anyone suggest this question is better placed in another forum? If so, please let me know.

This is what I have all be it, the long way around I'm sure.

tempfile=SYS(3)
USE TEMPDATA SHARED
COPY STRU TO WORKABLE
COPY STRU TO tempfile+'.dbf'
USE

CREATE TABLE TEMPMEMO (DETAILTEXT M)
USE TEMPMEMO SHARED
APPEND BLANK
USE

* SORT DUPLICATES

USE TEMPDATA SHARED
GO TOP

* The mtransact variable is the same when there
* is more than one order from the same person

DO WHILE NOT EOF()
  STORE SPACE(40) TO mtransact
  STORE TRANSACT  TO mtransact
  mrecno=0
  mrecno=RECNO()

  USE

  SELECT * FROM TEMPDATA WHERE TRANSACT=mtransact ;
    INTO TABLE tempfile+'.dbf'
	
  STORE "" TO morderdesc
  msubtotal=0
  mshipping=0
  mtotprice=0
  
  USE tempfile+'.dbf' EXCL
  GO TOP
	
  DO WHILE NOT EOF()
    mthis=0
    mthis=RECNO()
    STORE SPACE(120) TO morderdesc
    STORE TRIM(COLUMN6)+" - SKU: "+TRIM(COLUMN8)+ ;
      " - Price:"+STR(SUBTOTAL,7,2)+chr(13) TO morderdesc
		
    USE TEMPMEMO SHARED
    REPLACE DETAILTEXT WITH morderdesc ADDITIVE
    USE
		
    USE tempfile+'.dbf' EXCL
    GO mthis
    msubtotal=msubtotal+SUBTOTAL
    mshipping=mshipping+SHIPPING
    mtotprice=mtotprice+TOTPRICE
    SKIP
  ENDDO

  USE TEMPMEMO SHARED
  STORE SPACE(254) TO morder
  STORE TRIM(DETAILTEXT) TO morder

* BY NOW THERE SHOULD ONLY BE ONE TOTAL RECORD

  USE tempfile+'.dbf' EXCL
  GO TOP

  REPLACE SUBTOTAL WITH msubtotal
  REPLACE SHIPPING WITH mshipping
  REPLACE TOTPRICE WITH mtotprice
  REPLACE DETAILTEXT WITH morder

  DELE FOR RECNO()>1
  PACK

* NOW APPEND THE ONE RECORD BACK TO THE
* WORKABLE TABLE SO THERE IS ONLY ONE 
* RECORD THAT COULD CONTAIN MULTIPLE ORDERS

  USE WORKABLE SHARED
  APPEND FROM tempfile+'.dbf'

  USE TEMPMEMO EXCL
  REPLACE DETAILTEXT WITH ""
	
  USE TEMPDATA EXCL	&& GO TO THE NEXT RECORD
  GO mrecno
  SKIP
ENDDO

The above works but when you go back to the original table, it looks for the next record which could have the same transaction number.

Some suggestions not the sollution would be very helpful guys.

Many thanks
Lee

 

[mikeopolo]

Here's an idea (developed in vfp9):

I have a cursor which holds transaction lines. This code sums the line values into one field, and in the second step gathers all the transaction line descriptions into one field:

&& step 1 - index source cursor and group by transaction
SELECT tmptable
INDEX on transactionnumber TAG trxnum

SELECT ;
transactionnumber,;
CAST(" " as M) as Desc2,;
SUM(NVL(taxexclusiveamount ,0000000.00)) as taxexclusiveamount ;
FROM tmptable ;
GROUP BY transactionnumber ;
INTO CURSOR tmptable22 readwrite

&& Step 2 - Combine transaction line descriptions to one field

SELECT tmptable22
SCAN
  SCATTER NAME oSource
  && Look up tmptable
  IF SEEK(oSource.transactionnumber,'tmptable','trxnum')
    SELECT tmptable
    && Select only transactions matching the key
    SET KEY TO oSource.transactionnumber IN tmptable
    GO top
    lcMemo = "" 
    SCAN
      IF NOT ISNULL(tmptable.Description)
        STORE lcMemo + NVL(ALLTRIM  (tmptable.Description),"") + CHR(10) TO  lcMemo
      endif
    Endscan
    SET KEY TO 
  ELSE
    MESSAGEBOX("Warning...error?")	
  ENDIF 
	
  SELECT tmptable22
  replace desc2 WITH TRANSFORM(lcMemo) IN tmptable22
				
ENDSCAN 
	
SELECT tmptable22
browse

hth
Mike

 

[keepingbusy]

Mike
Thank you for the post. I'll check this out later on today and let you know the outcome.

Kind regards

Lee

 

[keepingbusy]

Hi Mike

I have copied your coding into a prg but before I start changing it with my table names and fields etc, I came across an error when I compiled it.

At the line shown in bold:

SELECT ;
transactionnumber,;
[b]CAST(" " as M) as Desc2,;[/b]
SUM(NVL(taxexclusiveamount ,0000000.00)) as taxexclusiveamount ;
FROM tmptable ;
GROUP BY transactionnumber ;
INTO CURSOR tmptable22 readwrite

the error message Function name is missing ).
I cant see why this, can you shed some light on it?

Many thanks
Lee

 

[mikeopolo]

Hello Lee

Not sure why you get that error, but all I am doing is creating a blank memo field, so you may know other ways to do that.

Regards
Mike

 

[TamarGranor]

The CAST() function was added in VFP 9. If you're using an earlier version, you'd get that error.

Tamar

 

[keepingbusy]

Mike
Thanks for the reponse.

Tamar
Yes, it's version 6 hence the error!

I'll look into this a bit deeper over the next few days and post back when I've got a resolution.

Thank you guys.
Lee