Copy cells from 1 table to another using an array?

[johann59]

From the beautiful land of Southern California! I've been looking into previous posts, but have been unable to find some code. I need help figuring out this problem. My main goal is to transfer data from 1 table (T1) into another table (F1). A client gives me a file with codes and the quantities for each code.

Example of text file given by client:
Code,Qty
A01,541
A02,234
A03,702
A04,300
and so on...

I am to print a report every 100 pieces. I already made a program that does the operations and splits it by 100's. Table T1 shows what I have so far:

Table T1
Code1 TQty Q1 Q2 Q3 Q4 Q5 Q6 Q7

A01 341 100 100 100 41
A02 234 100 100 34
A03 502 100 100 100 100 100 2
A04 300 100 100 100

I need to copy each field Q1, Q2, Q3, etc. to table F1 and each of those values needs to be a single record. My end result should look like below:

Table F1
Code1 Qty
A01 100
A01 100
A01 100
A01 41
A02 100
A02 100
A02 34
A03 100
A03 100
A03 100
A03 100
A03 100
A03 2
A04 100
A04 100
A04 100

I have to do it this way because we use a special Report writer to print the reports. It allows me to only print from single fields.

1. Do I copy the entire T1 table to an array and then copy each element to table F1?
I've been looking into
INSERT INTO F1(qty) VALUES (array1(1,5))
INSERT INTO F1(qty) VALUES (array1(1,6))
...
I'm thinking of checking empty elements with
IF EMPTY(array1(1,8))
***code
ENDIF

But how do I tie all of it?

2. Is it better to use a FOR...LOOP along with SCATTER & GATHER? Never have used these commands, but I can look into them. Have to figure out how to copy a specific cell from 1 table into another specific cell of a second table.

Maybe I'm over-complicating it. All I need are some ideas.

Using VFP 9

Thanks as always.

*Have to go back to work. I'll be checking back later this evening.

 

[TamarGranor]

Can you explain better what you mean by "I am to print a report every 100 pieces." What kind of report? Are you supposed to do this as you import the data? Etc. Etc.

For sure, I can't see why you'd ever want to create this:

Table T1
Code1 TQty Q1 Q2 Q3 Q4 Q5 Q6 Q7

A01 341 100 100 100 41
A02 234 100 100 34
A03 502 100 100 100 100 100 2
A04 300 100 100 100

Tamar

 

[vgulielmus]

Solution 1 (without the table T1)

CREATE CURSOR F1 (Code1 C(3),Qty I) && the result

CREATE CURSOR TableFromClient (Code1 C(3),Qty I)
INSERT INTO TableFromClient VALUES ('A01',541)
INSERT INTO TableFromClient VALUES ('A02',234)
INSERT INTO TableFromClient VALUES ('A03',702)
INSERT INTO TableFromClient VALUES ('A04',300)

lnVal = 100
SELECT Code1,CEILING(Qty / m.lnVal) as ngroup,Qty % m.lnVal as nlast FROM TableFromClient INTO CURSOR TableTotal ORDER BY 1
CALCULATE MAX(ngroup) TO lnMax IN TableTotal

SELECT TableTotal
SCAN
	IF TableTotal.nlast <> 0
		FOR lni = 1 TO TableTotal.ngroup - 1
			INSERT INTO F1 VALUES (TableTotal.Code1,m.lnVal)
		NEXT
		INSERT INTO F1 VALUES (TableTotal.Code1,TableTotal.nlast)
	ELSE
		FOR lni = 1 TO TableTotal.ngroup
			INSERT INTO F1 VALUES (TableTotal.Code1,m.lnVal)
		NEXT
	ENDIF
ENDSCAN

SELECT F1
BROWSE

Solution 2 (using table T2)

CREATE CURSOR T1 (Code1 C(3),TQty I,Q1 I,Q2 I,Q3 I,Q4 I,Q5 I,Q6 I,Q7 I)

INSERT INTO T1 (Code1,TQty,Q1,Q2,Q3,Q4) VALUES ('A01',341,100,100,100,41)
INSERT INTO T1 (Code1,TQty,Q1,Q2,Q3) VALUES ('A02',234,100,100,34)
INSERT INTO T1 (Code1,TQty,Q1,Q2,Q3,Q4,Q5,Q6) VALUES ('A03',502,100,100,100,100,100,2)
INSERT INTO T1 (Code1,TQty,Q1,Q2,Q3) VALUES ('A04',300,100,100,100)

CREATE CURSOR F1 (Code1 C(3),Qty I)

INSERT INTO F1 ;
	SELECT Code1,Q1 FROM T1 WHERE !EMPTY(Q1) ;
	UNION ALL ;
	SELECT Code1,Q2 FROM T1 WHERE !EMPTY(Q2) ;
	UNION ALL ;
	SELECT Code1,Q3 FROM T1 WHERE !EMPTY(Q3) ;
	UNION ALL ;
	SELECT Code1,Q4 FROM T1 WHERE !EMPTY(Q4) ;
	UNION ALL ;
	SELECT Code1,Q5 FROM T1 WHERE !EMPTY(Q5) ;
	UNION ALL ;
	SELECT Code1,Q6 FROM T1 WHERE !EMPTY(Q6) ;
	UNION ALL ;
	SELECT Code1,Q7 FROM T1 WHERE !EMPTY(Q7) ;
	ORDER BY 1
SELECT F1 
BROWSE

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

Solution 3

CREATE CURSOR F1 (Code1 C(3),Qty I) && the result


CREATE CURSOR TableFromClient (Code1 C(3),Qty I)
INSERT INTO TableFromClient VALUES ('A01',541)
INSERT INTO TableFromClient VALUES ('A02',234)
INSERT INTO TableFromClient VALUES ('A03',702)
INSERT INTO TableFromClient VALUES ('A04',300)

lnVal = 100
SELECT Code1,CEILING(Qty / m.lnVal) as ngroup,Qty % m.lnVal as nlast FROM TableFromClient INTO CURSOR TableTotal ORDER BY 1
CALCULATE MAX(ngroup) TO lnMax IN TableTotal

FOR lni = 1 TO m.lnMax
	INSERT INTO F1 SELECT TableTotal.Code1,IIF(m.lni = TableTotal.ngroup AND TableTotal.nlast <> 0, TableTotal.nlast, m.lnVal) as Qty FROM TableTotal WHERE TableTotal.ngroup >= m.lni
NEXT

SELECT F1
INDEX on Code1 TAG code1
BROWSE

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[johann59]

Hi Tamar. I mean once I have the table F1 completed, I will use the 'Report Writer', a program that handles the printing, to create specific reports that have the Code, and the corresponding quantities. Each report (page) cannot have more than 100 pieces, Even if the remainder is a single piece. Hence the need to have each of qty values in a single record.

Thanks,
------------------------------------------------------

Vilhelm,
THANKS! Both of the suggestions work.

That was exactly what I needed. Highly appreciated.

 

[Olaf Doschke]

Like Tamar, I don't see a benefit of creating T1 from the initial text file given by the client. I also don't see why 541 changed to 341 and 702 changed to 502.

In general copying a cell is easy with [pre]INSERT INTO target (fieldA) VALUES (source.fieldX)[/pre]

If you have a quantity and want records to have each max 100 and sum up to the original quantity, do a loop like this:

lnQuantity = 502
Do While lnQuantity>0
   If lnQuantity>100
      Insert into f1 (code,qty) VALUES ('A03',100)
      lnQuantity= lnQuantity-100
   Else
      Insert into f1 (code,qty) VALUES ('A03',lnQuantity)
      lnQuantity = 0
   Endif
Enddo

This needs to be put into an outer loop on the initial read in text file (either with FREAD or on the table/cursor created from the file). The code can be read from there instead of the 'A03' literal. And the Quantity Variable is initialized by the text file quantity.

Bye, Olaf.

 

[johann59]

Thanks Olaf. I just keep on learning.

I appreciate your post.