assigning record field to variable [Wall of Text]

[cmjtf1re]

Greetings, I have a question that I am not sure of how to ask. This prevents me from accurately searching for a solution, therefore I must rely on good ole fashion verbiage...or in this case Typiage.

I have a database with 10 questions in it. Each question may contain 1 through 10 in the field.

I'm trying to write a program that will basically count for question 1 = 1, 2, 3, 4, and so on. then continue to count for question 2 = 1, 2, 3, 4, and so on. All the way to question 10. My code works perfectly fine for counting only one question's field, ie; count only for question 1 = 1, 2, 3, 4...

Also, the name of my question fields are ques1, ques2, all to ques10 subsequentially.

Here is my code:

do while inc < 11
go top
store "ques"+allt(str(inc)) to mques
* the single line above is where I'm trying to
* assign 'ques1' to mques, all the way to 'ques10'

do while not eof()
store mques to mqt
* the single line above is where I would
* normally type 'store ques1 to mqt'
* which would store whatever is in the ques1
* field to mqt. ie;if ques1 = 2, mqt now = 2

store 0 to mput
do while not eof() and mques = mqt
* the latter half of the single line above
* is where I would type 'ques1 = mqt'
* which if it worked the way I needed it to
* would mean if ques1 has '2' in the field,
* continue.

mput = mput + 1
? mques, mput
skip
enddo
select 2
appe blank
replace re1_10 with mques+mqt, actual with mput
* dont mind the indentation of the single
* line above, this is where I want re1_10
* to say 'ques1' + '1' thru '10', to
* 'ques 10' + '1' thru '10'
select 1
enddo
inc = inc+1
enddo

//end code

My problem is I'm trying to cut corners instead of writing 10 duplicate lines of code for 10 questions to do what could be done with 1 set of code.

What keeps happening is the line 'store ques1 to mqt' would normally store the value of that particular record to mqt so if ques1 was 2, mqt would then be 2. but because I use 'store "ques"+allt(str(inc)) to mques' mques goes through the cycle ten times, each time increasing inc: ques1, ques2, ques3 and so on, but the line 'store mques to mqt' doesn't store the value of ques1, or ques2 and up, instead it makes mqt actually equal the word 'ques1' instead of the value.

This is my issue and this is what I am hoping you can help me with.

ps I tried storing 0 to mqt before storing mques to mqt to make it register mqt as a numeric field, but still it does not work. I'm going to continue trying to figure it out but help would be greatly appreciated.

Thanks.

 

[cmjtf1re]

store 1 to inc
do while inc < 11
store "=ques"+allt(str(inc)) to mques
store 1 to inq
do while inq < 11
store 0 to mput
go top
do while not eof()
if mques = inq &&*********This line right here is where I keep getting my error.
mput = mput + 1
?mques,inq,mput
endif
skip
enddo
select 2
appe blank
replace re1_10 with mques+space(1)+inq, actual with mput
inq = inq+1
enddo
inc = inc+1
enddo


Please disregard my previous post, this new code is cleaner and more efficient. I've boiled it down to now my open problem being this error I keep receiving. "Operator/Operand type mismatch". If you look at the code I pasted I have a comment with some number of asterisks. I know why I'm getting the error, I'm trying to figure out a way around it. mques is supposed to equal ques1, ques2, ques3 all the way to ques10. if I type 'if ques1 = inq' I get absolutely no problems, because it checks the ques1 field value to see if it contains whatever number inq happens to be. When I type it the way I have typed it there, it doesn't check ques1 field. All it sees is me asking the if statement 'if [character var] = [numeric var]' which is why I get the error.

Is there anyway to call the value of a particular field? such as ' Val(mques) ' which the code would interpret as Val(ques1)' that checks ques1's field value to see if it contains whatever number inq happens to be? Example:

store 5 to inq
If ques1 = inq (This will check the field value of ques1 to see if it contains #inq#)
? "ques1 is 5"
endif

^- that would work. this wont:

store 5 to inq
store "ques"+"1" to mques
if mques = inq (Which should technically read ques1 = inq)
? "ques1 is 5"
endif

even though technically it is the same thing as the previous code, instead it reads 'if [char var] = [num var]' and crashes.

store 5 to inq
store "ques"+"1" to mques
if val(mques) = inq (val wont work, but for the sake of my example)
? "ques1 is 5"
endif

is there a command to do such? read the field value of 'mques' (Which happens to be ques1) instead of [char var] = [num var] ?


Ps, I know I type a lot. I try my best to fully elucidate my question and give as much help as possible.

 

[mmerlinn]

[&nbsp;]
Why are you using DO WHILE?

It is a lot easier to use a FOR loop and let the computer do the counting instead of you needing to remember to do it.

Change all of your DO WHILEs to FOR loops, like your first one would be: FOR inc = 1 TO 10

Your code will be much more readable and easier to maintain, not to say anything about shorter.

mmerlinn

http://mmerlinn.com

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Steven Raymond

 

[GriffMG]

I was going to answer this last night, I'm glad I waited.

Your first error is being caused because you are comparing mqes with inq and they are not of the same type; mqes is a character string and inq is a numeric.

You need to do some macro substitution to resolve the issue and get 'the value of what the string represents' rather than the contents of the string.

Look at the 'Evaluate()' line below.

Another problem is being caused by not selecting the appropriate table in a timely fashion - I've added two select statements to make it clearer below (only the first 'select 1' is critical).

store 1 to inc
do while inc < 11
    store "ques"+allt(str(inc)) to m.strQues
    mqes = Evaluate(m.strQes)

    store 1 to inq
    do while inq < 11
        store 0 to mput
        select 1
        go top
        do while not eof()
            if mques = inq &&*********This line right here is where I keep getting my error. 
                mput = mput + 1
                ?mques,inq,mput
            endif
            select 1
            skip
        enddo
        select 2
        appe blank
        replace re1_10 with m.strQues+space(1)+inq, actual with mput
        inq = inq+1
    enddo
    inc = inc+1
enddo

In general, mmerlinn is also right using 'for...next' loops for your outer iterations might be clearer (I stick to using 'for...next' for counters and 'do while...enddo' for scanning data tables). Some will suggest that the 'do while...enddo' loop should be replaced with a 'scan...endscan' - but in practise there is no measurable difference in speed.

For your own sanity, I would recommend that where you develop any kind of loop in future that you type in the control sequence BEFORE you complete the contents of the loop - and the same for 'if...endif' constructs... so I would suggest that you type something like this:

select 1
go top
do while .not. eof()
  

   select 1
   skip
enddo

BEFORE you type in the processing commands between the 'do while' and the 'select 1' - it reduces the chances of processing the wrong table by miles.

Good luck.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[craigber]

Wow.. all these suggestions about using FOR/NEXT over DO WHILE and other ideas to improve the code, both for readability and speed, yet some simple things are missed:

Use lots of white space spell out commands

mques = "ques" + ALLTRIM(STR(inc))

is much easier to read than

store "ques"+allt(str(inc)) to mques

Also, SCAN/ENDSCAN is faster and much easier to read than DO WHILE NOT EOF()/SKIP/ENDDO

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[GriffMG]

He he,

the old scan/endscan do while/enddo argument pops up again!

B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[mmerlinn]

[&nbsp;]
White space IS important. No doubt about that.

But if you take care of the white space before taking care of the looping, you will be doing lots of extra work for NOTHING.

For example if you take the lines inc = inc+1 and put in white space inc = inc + 1 --- What have you gained? --- Nothing --- Fixing the looping means removing these lines anyhow.

That is why I mentioned looping and NOT white space. I feel it is much more important to start there than anywhere else.

As for FOR versus SCAN, the code was hard to read, so I did not notice the replace statement within the code. Personally, I would use SCAN when tables are involved, but I think that is personal preference.

Some of the personal standards that I use are:

1) CAPITALIZE all FUNCTION, PROCEDURE, COMMANDS, etc to make them contrast to the variable names and data
2) SPELL OUT everything
3) use long descriptive names unless used only locally
4) use lots of white space within lines of code
5) use lots of linefeeds to separate groups of similar actions
6) indent all contents of loops and branching type structures
7) use SCAN/ENDSCAN on tables
8) use FOR/ENDFOR when count is known or can be calculated
9) use DO WHILE/ENDDO when the end cannot be known

This is a small sample of the standards I use. I find code a lot easier to read when I use these standards.

Not everyone will agree with those particular standards or use them, but if standards of some kind are not used, tail-chasing for ages becomes the rule when looking for bugs.

mmerlinn

http://mmerlinn.com

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Steven Raymond

 

[mmerlinn]

[&nbsp;]
I took the code in the second post and set it to my standards. Other than changing the variable assignments, looping, white space, and linefeeds, nothing else was done. Any/all bugs are still there. Now I find it easier to read and easier to locate the bugs in it.

FOR inc = 1 TO 10

mques = "=ques" + ALLTRIM(STR(inc))
FOR inq = 1 TO 10

mput = 0
GO TOP
SCAN

IF mques = inq && *********This line right here is where I keep getting my error.
mput = mput + 1
? mques, inq, mput
ENDIF

ENDSCAN

SELECT 2
APPEND blank
REPLACE re1_10 WITH mques + SPACE(1) + inq, actual WITH mput

ENDFOR

ENDFOR

First thing that pops up is noted in the RED and GREEN highlights - STRING versus NUMBER.

mmerlinn

http://mmerlinn.com

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Steven Raymond

 

[mmerlinn]

[&nbsp;]
You need to use EVAL(), &, or "()" to extract the value from the string. There are several threads in this forum discussing the merits of each.

******************

As a side note I just discovered that I can no longer post to this forum on this computer if I use NS. Now I am forced to use POS Microshaft IE. Grrrrr.

mmerlinn

http://mmerlinn.com

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Steven Raymond

 

[Imaginecorp]

Not really understanding your question, but if you are looking for field name substitution here is an idea on how to do it.

Select <tablename>
Scan
	For x = 1 To 10
		ques = ""
		ques = "<tablename>.ques"+Transform(x)

		* tablename is the table that contains
		*ques1,ques2...
		*ques now becomes ques1,ques2,ques3....

		cfielddata = Evaluate(ques)
		*cfielddata will jow hold what was
		*in the field ques1,ques2...

		replacefield = ""
		replacefield = "<tablename>.replacefield"+Transform(x)

		*tablename is the table whose fields
		*are being replaced 
		*these are the the fields being replaced
		*replacefield1,replacefield2,replacefield3....

		Replace (replacefield) With (ques)

		*other code
	Endfor
	Select <tablename>
Endscan

 

[TamarGranor]

If I understand your question correctly, you have a series of fields named ques1, ques2, etc. Each of those fields contains a value between 1 and 10. What you're looking for is, for each question, the number of records with each given answer. Correct?

Before I offer a solution, I'll point out that the reason this is hard is that your data isn't normalized. If you had two table, one identifying the questions with one record per question, and then another where each record represents a single answer and contains the identifier of the question to tell you what it applies to, this would be a very easy problem.

Moving on to the way you do have things, I'd do this with a loop of queries:

FOR nQuestion = 1 TO 10
cField = "Ques" + trans(m.nQuestion)
cResult = "csrQues" + trans(m.nQuestion)

SELECT &cField AS nAnswer, CNT(*) ;
FROM YourTable ;
GROUP BY 1 ;
INTO CURSOR (m.cResult)
ENDFOR

At the end of this, you have 10 cursors, each with 10 (or fewer, if some answers were never given) records, one per answer.

Before I make any suggestions as to what to do with the 10 cursors, I'd like to know what you need to do with the results.

Tamar

 

[cmjtf1re]

Thank you everyone for giving your two cents on how I did everything wrong. Constructive criticism is always a bonus. Thank you GriffMG for actually answering my question and later mmerlinn too after realizing what I was asking.

For future reference I now know I can use evaluate to substitute a field with a character string. However I scrapped this code as it goes through the database atleast 100 times. I was thinking more about getting it done than I was thinking about efficiency. Working with various databases which hold anywhere from 5000 to 400,000 records, going through that amount 100 times is quite time consuming.

I wrote a new program with do case statements which only goes through the database one time and does exactly what I need it to do.

If anyone wants to take a look at that and possibly give me some pointers as to how I can trim it I'd be more than happy to send it.

 

[TamarGranor]

Figured I'd give my comments about DO WHILE/FOR/SCAN separately from an answer on the original question.

There's no matter of opinion here. There are solid reasons why you should use FOR for counted loops and SCAN for loops through records. I've written about this at length in the past (including an article in FoxRockX and last year's Southwest Fox session notes. Here's the short form.

DO WHILE vs. FOR:

1) FOR is an order of magnitude (that is, more than 10 times faster).

2) The endpoint of a FOR is evaluated once on the way in, so you can screw yourself up by changing it inside the loop.

DO WHILE vs. SCAN

1) SCAN always starts at the beginning of the table (unless you use SCAN WHILE), so you don't need GO TOP.

2) SCAN automatically SKIPs, so you don't need the SKIP command.

3) SCAN automatically reselects the controlling workarea at the end of the loop, so even if you change workareas inside, you don't get messed.

4) In most cases, SCAN is faster, sometimes by nearly an order of magnitude. (The exception is an occasional case where the table is ordered.)

Bottom line: Except in rare cases, FOR or SCAN is always better than DO WHILE.

Tamar

 

[cmjtf1re]

Tamar. And everyone else who commented.

My original goal was to write this program in as little lines as possible, my mistake was not taking into account I didn't want to scan through a large database multiple times.

Thanks to input from all of you I've written my program that is both considerably shorter and scans through only one time. Not only that but it's easier to read and much easier to show someone if you're having a problem.

Tamar my goal is to simply count and be able to send the numbers out to each corresponding client. Opening a command window and typing each line " Count for ques1 = "1" " is just too much typing.

If anyone wants to take a look at my code below I am down to two minor issues.

SELECT 2
FOR nQuesCount = 1 TO 10
	APPE BLANK
	REPLACE q WITH nQuesCount, v1 WITH 0, v2 WITH 0, v3 WITH 0, v4 WITH 0, v5 WITH 0, v6 WITH 0, v7 WITH 0, v8 WITH 0, v9 WITH 0, v10 WITH 0, v11 WITH 0, v12 WITH 0, v13 WITH 0
ENDFOR
SET ORDER TO q
SELECT 1
SCAN
	FOR incQuesResp = 1 TO 10
		FOR nQCount = 1 TO 10
			STORE "ques"+ALLT(STR(nQCount)) TO m.strQues
    		mQues = EVAL(m.strQues)
			DO CASE
			   CASE VAL(mQues) = incQuesResp
			      SELECT 2
			      SEEK (nQCount)
			      DO CASE
				      CASE incQuesResp = 1
				      REPLACE v1 WITH v1+1
				      CASE incQuesResp = 2
				      REPLACE v2 WITH v2+1
				      CASE incQuesResp = 3
				      REPLACE v3 WITH v3+1
				      CASE incQuesResp = 4
				      REPLACE v4 WITH v4+1
				      CASE incQuesResp = 5
				      REPLACE v5 WITH v5+1
				      CASE incQuesResp = 6
				      REPLACE v6 WITH v6+1
				      CASE incQuesResp = 7
				      REPLACE v7 WITH v7+1
				      CASE incQuesResp = 8
				      REPLACE v8 WITH v8+1
				      CASE incQuesResp = 9
				      REPLACE v9 WITH v9+1
				      CASE incQuesResp = 10
				      REPLACE v10 WITH v10+1
				      CASE incQuesResp = 11
				      REPLACE v11 WITH v11+1
				      CASE incQuesResp = 12
				      REPLACE v12 WITH v12+1
				      CASE incQuesResp = 13
				      REPLACE v13 WITH v13+1
			      ENDCASE
			   	  SELECT 1        
			ENDCASE
		ENDFOR
	ENDFOR
ENDSCAN
SELECT 2
GO TOP
BROW &&title "pres ESC to exit - close browser to recount" nolgrid norgrid noedit nodelete norefresh noappend FONT 'Courier',12 	
CLEAR

That does everything I need it to do. One of the minor issues I'm having is the long line of code:

SELECT 2
FOR nQuesCount = 1 TO 10
	APPE BLANK
	REPLACE q WITH nQuesCount, v1 WITH 0, v2 WITH 0, v3 WITH 0, v4 WITH 0, v5 WITH 0, v6 WITH 0, v7 WITH 0, v8 WITH 0, v9 WITH 0, v10 WITH 0, v11 WITH 0, v12 WITH 0, v13 WITH 0
ENDFOR

the v's with a number following up to 13 are field names.

I want to replace that bit of code with:

FOR nQuesCount = 1 TO 10
	APPE BLANK
	REPLACE q WITH nQuesCount
	FOR nValCount = 1 to 13
		STORE "v" + ALLT(STR(nValCount)) TO m.numValue
		mValue = EVAL(m.numValue)
		REPLACE mValue WITH 0
	ENDFOR
ENDFOR

However it tells me on the line "REPLACE mValue WITH 0" that "Variable mValue is not found."

The v fields are numeric.

The other minor detail is how I would be able to browse only the fields that aren't 0. As of right now I can see all question's and how many of each value are in each. If there are 0 answers to a certain question, I don't want to see it. Again that is minute, if it can be done great.

 

[mmerlinn]

[&nbsp;]
I am not on a computer where I can test this out to see if this is even doable, but it seems to me that you can dispose of the DO CASE/ENDCASE and to something like this:

z = "v" + ALLTRIM(STR(incQuesResp))
y = EVAL(z) + 1
REPLACE (z) WITH y

or maybe

REPLACE &z WITH y

I may have this all mixed up, but something like this should work to eliminate all of those case statements.

mmerlinn

http://mmerlinn.com

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Steven Raymond

 

[cmjtf1re]

mmerlinn that is precisely what I had planned on doing once I figured out why I was getting the error 'Variable mValue is not found.' Where as mValue would be the equivilant to your 'z' var. I'm going to try wrapping paranthesis around mValue. If that doesn't work I'll try the ampersand. Thanks again.

 

[Imaginecorp]

Just enclose mvalue in Parenthesis , you can also use a &...

REPLACE (mValue) WITH 0 (Recommended)
or
REPLACE &mValue WITH 0

 

[mmerlinn]

[&nbsp;]

It looks to me that the following code cannot work ever:

STORE "v" + ALLT(STR(nValCount)) TO m.numValue
mValue = EVAL(m.numValue)
REPLACE mValue WITH 0

You are building a variable in the first line. Then in the second line you are trolling for a value of that variable. Then in the third line you are trying to use that value as a field name. No way that can work.

Try:

STORE "v" + ALLT(STR(nValCount)) TO m.numValue
REPLACE (m.numValue) WITH 0

mmerlinn

http://mmerlinn.com

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Steven Raymond

 

[mmerlinn]

[&nbsp;]

Figures. My logic in the last post is faulty. Even though my logic is faulty, the suggestion should still work (I hope).

mmerlinn

http://mmerlinn.com

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Steven Raymond

 

[TamarGranor]

Instead of a set of variable v1, v2, etc., why don't you just use an array? Then you could get rid of lots of code. That whole CASE statement would become:

v[incQuesResp] = v[incQuesResp] + 1

Then, after you've done all the counting, you can do a REPLACE in a loop:

FOR incQuesResp = 1 TO 10
REPLACE ("v" + transform(incQuesResp)) WITH v[incQuesResp]
ENDFOR

Tamar