Creating multiple instances of a single record

[anayanzi]

I'm fairly new to VFP. Currently working with VFP 9. Took over some previous projects. Know really the basics and trying to catch up, so been reading and practicing.

But right now, this problem is beyond my ability and need to find a solution fairly soon. Have to pass the information from 1 table to multiple others, but for now, let's say I have to pass it to a single output table. Been able to do that with 1 record using the INSERT command, but I want to be able to convert a record with 20 fields into another table that has a record for each of the f5-f19 fields (15 fields) of the input table. For the first record in the input table, this would translate into 15 records. Record two from the input table would output 5 records, and so on. Basically I want to create a single record for each of the f5 thru f19 fields, with each record of course, having the information from fields f1-f4.

Been trying but have not been able to make it work.

My Input table (Tab1) has 20 fields
f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20
1 CAN A12 15000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 1000 15
2 MEX B12 4320 1000 1000 1000 1000 320 5
3 US C24 6850 1000 1000 1000 1000 1000 1000 850 7
4 US C32 3500 1000 1000 1000 500 4
etc...

My Output table (Tab2) has to be somewhat like this:
f1 f2 f3 f4 f5 f6 f7
1 CAN A12 1000 15 1 15
1 CAN A12 1000 15 2 15
1 CAN A12 1000 15 3 15
1 CAN A12 1000 15 4 15
1 CAN A12 1000 15 5 15
1 CAN A12 1000 15 6 15
1 CAN A12 1000 15 7 15
1 CAN A12 1000 15 8 15
1 CAN A12 1000 15 9 15
1 CAN A12 1000 15 10 15
1 CAN A12 1000 15 11 15
1 CAN A12 1000 15 12 15
1 CAN A12 1000 15 13 15
1 CAN A12 1000 15 14 15
1 CAN A12 1000 15 15 15
2 MEX B12 1000 5 1 5
2 MEX B12 1000 5 2 5
2 MEX B12 1000 5 3 5
2 MEX B12 1000 5 4 5
2 MEX B12 320 5 5 5
3 US C24 1000 7 1 7
3 US C24 1000 7 2 7
3 US C24 1000 7 3 7
3 US C24 1000 7 4 7
3 US C24 1000 7 5 7
3 US C24 1000 7 6 7
3 US C24 850 7 7 7
4 US C32 1000 4 1 4
4 US C32 1000 4 2 4
4 US C32 1000 4 3 4
4 US C32 500 4 4 4
etc...

Last 3 fields are only the number of transactions per account.

Here's an image of my 2 tables showing what I would like to accomplish:

Thanks, I would appreciate any help/guidance.

Gloria

 

[DSummZZZ]

You can add a record to the new table by testing to see if there is a value in the 'Q' fields. Give this a try:

SCATTER MEMVAR BLANK
SCAN
   STORE seqn    to m.seqn
   STORE package to m.package
   STORE id      to m.id
   STORE pgs     TO m.pgs
   FOR nDx = 1 to 15
      STORE 'Q' + ALLTRIM(TRANSFORM(nDx)) TO cTempField
      STORE 'm.Q' + ALLTRIM(TRANSFORM(nDx)) TO cTempVar
      STORE 0 TO (cTempVar)
      IF EVALUATE(cTempField) > 0
         STORE &cTempField TO &cTempVar
         INSERT INTO table2 FROM MEMVAR 
      ENDIF
   NEXT 
ENDSCAN

-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[anayanzi]

Thank you Dave for your reply.

I gave the code a try. Since I am not very experienced with VFP, my goal is not only to accomplish the task, but I wish to understand what the code is doing. I have to give the code a try and see (via the debugger) what exactly it is doing.

I understood most of the commands, SCATTER, STORE, SCAN-ENDSCAN, INSERT, TRANSFORM. I ran the code as is, and I got no output. The code is failing because of me, my lack of understanding.

The part that I am failing to grasp is these 3 lines, especially the 'Q' and how it translates into the different qty fields:

STORE 'Q' + ALLTRIM(TRANSFORM(nDx)) TO cTempField
STORE 'm.Q' + ALLTRIM(TRANSFORM(nDx)) TO cTempVar
STORE 0 TO (cTempVar)

I see that these STORE commands are inside a FOR..NEXT loop, but I cannot see where the 15 different Qty1-Qty15 fields are being 'READ'.

I tried putting tab1.qty1 instead of the 'Q', and some other 'guesses' but to no avail. Could you explain a bit more what the code is doing and how to make it read the different qty fields?

I appreciate it very much,

Thanks

Gloria

 

[DSummZZZ]

I was wondering if you were familiar with those methods or if I would confuse you. Sorry, but let me see if I can clarify.
During the FOR/NEXT loop, nDx of course is a number, 1-15. So I take that number, convert it to string, trim off the blanks, and put a "Q" in front of it to make it a substitute for the 'Q1' field:

STORE 'Q' + ALLTRIM(TRANSFORM(nDx)) TO cTempField

becomes 'Q1'

Then I do the same thing to create a memory variable called 'm.Q1'

STORE 'm.Q' + ALLTRIM(TRANSFORM(nDx)) TO cTempVar

and default its value to 0

STORE 0 TO (cTempVar)

EVALUATE() evaluates the value of the field cTempField (q1), to see if there is something > 0 in it:

IF EVALUATE(cTempField) > 0

If there is, then we use macro substitution (&cTempField) to take the value contained within the field q1 and store it to the memory variable m.q1 - again using macro substitution (&cTempVar). Then create a record the second table using INSERT INTO.

It is actually shorthand for something like:

IF q1 > 0 
   INSERT INTO ...
ENDIF

IF q2 > 0 
   INSERT INTO ...
ENDIF

And so on.
Hope that makes sense.


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[anayanzi]

Again thanks Dave for your help. I am understanding better with your explanation. It's kind of fun trying to work it out.

I still have not been able to make it work all the way. First, my fault. I forgot to mention that the fields Qty1-Qty15 were character fields. When I got the error 'operator/operand type mismatch' I changed all the qty field to numeric data type and changed the name from qty1-qty15 to q1-q15 in order to match it to the cTempField (q1). It worked...almost.

It populated all the fields in the output tab2 table perfectly with the exception of the qty n(5) field. That particular field in the output table stays blank. So, playing with the code, below the line

STORE pgs TO m.pgs

*I added

STORE q1 TO m.qty

and indeed it now populates the qty field in the output table, but it populates with the same number '1000'. Record 2 from the input table, in field q5, has 320, but in the output it appears as 1000.

I understand that the reason is because the code I added is only reading the first field Q1. So does it stand to reason the I need to do another FOR...NEXT loop?

I do apologize for continuing to ask for help. The only thing I am missing is the have the output table populated with the corresponding values.

Below is an image of the output table.

Thanks for the help and guidance.

Gloria

 

[Olaf Doschke]

I changed all the qty field ... names from qty1-qty15 to q1-q15 in order to match it to the cTempField (q1).

If you recognized this detail, why did you change 15 field names instead of the two lines of code?

STORE 'Q' + ALLTRIM(TRANSFORM(nDx)) TO cTempField
STORE 'm.Q' + ALLTRIM(TRANSFORM(nDx)) TO cTempVar

Both these lines generate the Q1..Q15 names and can much easier be changed to prefix Qty instead of only Q, even if you don't know what Transform does, you know the output is Q1 in cTempfield for nDx=1.

Another rule of thumb: Rather change what's new than what has been its way for a long time unless you really "refactor" things. (refactoring is a term describing rewriting code and restructuring data in a better way for how it has evolved has become less good, less maintainable or too slow). Since the table preexisted, you now might break old code working out the old schema of the table and working with it. Also it's more work to change 15 field names instead of the two lines above. It could even be simplified to

STORE 'Qty' + TRANSFORM(nDx) TO cTempField
STORE 'm.' + m.cTempField TO cTempVar

Besides the data structure looks like it could be redesigned, but that's beyond this quesstion.

What I'm saying here is you show good observations and understand things, you make progress. But you're not lazy enough yet .

Bye, Olaf.

 

[vgulielmus]

This is the third time when I saw a similar request in the past year.
Here is an alternate solution

CREATE CURSOR cc (f1 I,f2 C(3),f3 C(3), f4 I)
INSERT INTO cc VALUES (1 , 'CAN' , 'A12' , 15000)
INSERT INTO cc VALUES (2 , 'MEX' , 'B12' , 4320)
INSERT INTO cc VALUES (3 , 'US' , 'C24' , 6850)
INSERT INTO cc VALUES (4 , 'US' , 'C32' , 3500)
INSERT INTO cc VALUES (5 , 'US' , 'F32' , 9000)
INSERT INTO cc VALUES (6 , 'US' , 'M32' , 12600)
INSERT INTO cc VALUES (7 , 'US' , 'J40' , 3000)
INSERT INTO cc VALUES (8 , 'US' , 'R40' , 201)
INSERT INTO cc VALUES (9 , 'US' , 'R57' , 552)
INSERT INTO cc VALUES (10 , 'US' , 'R66' , 6575)

* Set the Qty
lnQty = 1000
* Calculate the number of pages and the Qty for the last page for each Seqn
SELECT f1, CEILING(f4 / m.lnQty) as Pgs, f4 % m.lnQty as LastPage FROM cc INTO CURSOR cTmp
* Calculate the maximum(number of pages)
SELECT MAX(Pgs) as PgsMax FROM cTmp INTO CURSOR cTmpMax

* Create the result
CREATE CURSOR cFinal (Seqn I,Package C(3), Id C(3), Qty I, Pgs I, Sheet1 I, Sheet2 I)
INDEX on BINTOC(Seqn) + BINTOC(Sheet1) TAG cFinal

FOR lnSheet1 = 1 TO cTmpMax.PgsMax
	INSERT INTO cFinal ;
		SELECT cc.f1 as Seqn,;
			f2 as Package,;
			f3 as Id,;
			IIF(m.lnSheet1 < Pgs OR m.lnSheet1 = Pgs AND LastPage = 0, m.lnQty , LastPage) as Qty,;
			Pgs,;
			m.lnSheet1 as Sheet1,;
			Pgs as Sheet2;
		FROM cc,cTmp WHERE cc.f1 = cTmp.f1 AND m.lnSheet1 <= Pgs
NEXT
BROWSE

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[TamarGranor]

Vilhelm-Ion--if you're seen the same question a bunch of times, it might make a good article for FoxRockX.

Tamar

 

[vgulielmus]

article for FoxRockX

How should I proceed ?

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[anayanzi]

My 2 cents worth. I personally think Tamar's idea is a good one Vilhelm.

Although it's probably been said hundreds of times, and not wanting to sound like Mary Poppins, but this community should VERY proud of the job being done here. You gentlemen and ladies, probably have no idea the tremendous help being done here. Just this thread alone helped me a lot, in respect to my job and my knowledge. And who knows how many other jobs have been saved, how many other jobs been advanced by help and advice being given here, and all for free!

I have gained a huge amount of respect for this community. I wish I could do more, but at least wanted my thanks to be said.

Dave, Olaf, and Vilhelm-Ion, Tamar, thanks for all the help.

Gloria

 

[TamarGranor]

Probably the best thing is to send an email to artikel@dfpug.de telling what you want to write about. Then, Rainer Becker, the editor, can confirm that he wants it and give you the Word template.

Tamar

 

[vgulielmus]

Thank you

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[DSummZZZ]

Gloria,

I have been out of town so I haven't seen tek-tips in a few days. But I want to say thanks for your kind words, and I'm glad I was able to help shed some light!
As for the "...and all for free!" part, monetarily, yes. All we ask is that YOU to come back and answer someone elses questions and see if you can help them out.


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm