For loop table fields 1

[robnowa]

Hello!
I'm quite new to Visual Foxpro and would like some help with a for loop.
I have the fields klubb1-12 in my table and lets say all even numbers are "checked" (klubb1="1", klubb2="2" etc) and I would like to loop through some rows and move everything +1. So if 2 is checked, then unckeck 2 and make 3 checked.
I thought this would work but I get "Command contains unrecognized phrase/keyword. on the second line.

FOR lnRaknare = 1 TO 12
	IF Kunder.klubb&lnRaknare = ALLTRIM(STR(lnRaknare))
		IF lnRaknare = 12
			Kunder.klubb12 = ""
			Kunder.klubb1 = "1"
		ELSE
			Kunder.klubb&lnRaknare = ""
			Kunder.klubb&lnRaknare+1 = ALLTRIM(STR(lnRaknare+1))
		ENDIF
	ENDIF
ENDFOR

Any suggestions or improvements? Thanks!

 

[JRB-Bldr]

Welcome to VFP and the forum.

First off, as the error message indicates, you have a syntax error on line #2:
IF Kunder.klubb&lnRaknare = ALLTRIM(STR(lnRaknare))

You appear to want the first part to represent the incrementing field name of the table.

cFldName = "Kunder.klubb" + ALLTRIM(STR(lnRaknare))
Then:
IF EVAL(cFldName) = ALLTRIM(STR(lnRaknare))

Secondly in order to change the value of a table's field, you need to use the VFP REPLACE command or a SQL UPDATE command, not merely an equals sign.

Since you are new to VFP I'll give you my 'standard' advice - you might want to spend some time looking over the free on-line VFP tutorial videos at:

http://www.garfieldhudson.com/FreeVideos.aspx

Good Luck,
JRB-Bldr

 

[robnowa]

Ah yes the REPLACE I'm aware of, didn't get so far in the code just yet

Thanks for your answer, I will try it out!

 

[JRB-Bldr]

Since you also want to change other field values depending on your internal IF/ENDIF conditions, you might want to do something like:

FOR lnRaknare = 1 TO 12
   cChkFldName = "Kunder.klubb" + ALLTRIM(STR(lnRaknare))
   cChngFldName = "Kunder.klubb" + ALLTRIM(STR(lnRaknare))
   cChngFld2Name = "Kunder.klubb" + ALLTRIM(STR(lnRaknare + 1))

   IF EVAL(cChkFldName) = ALLTRIM(STR(lnRaknare)) 
      IF lnRaknare = 12
         REPLACE Kunder.klubb12 WITH <whatever>,;
                 Kunder.klubb1 WITH <whatever>
      ELSE
        REPLACE &cChngFldName WITH <whatever>,;
           &cChngFld2Name WITH <whatever>
      ENDIF
   ENDIF
ENDFOR

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Welcome, Rob!

JRB-Bldr makes a good suggestion by first putting together the full field name as cFldName = "Kunder.klubb" + ALLTRIM(STR(lnRaknare))

Kunder.klubb&lnRaknare itself would work via Macrosubstitution, but lnRaknare would then need to be a string, the macro substitution operator & only works on string variables. VFP only substitutes string values into the static rest of the code. So remember that about macro substitution: Only strings. The strings just may be digits, but they need to be strings.

Lasst not least a table with fields klubb1 to klubb12 is bad table design. That's not because it's DBF or VFP accesses the data, this is a genral rule about data nromalization. It's not different in VFP, even though you can do macro substitution here, which helps a bit to loop such fields. The main problem here is extensibility. Even if 1 to 12 stand for months here, and a 13th month is not to be expected, similar data should always be in N records, not in N fields. A row is not a place for arrays of information. If you want to go that route, rather use a memo with N lines or store XML in there for reducing database schema complexity. But that also only pays well on a database with a XML field type including query capabilities on such fields in their SQL dialect. Or for very unspecific data not the same in each record.

Bye, Olaf.

 

[robnowa]

Thanks for the info and good point there Olaf!
Yes, we're going to change that 12 months fields to something better in the near future.