Mike,
Weren't we going through this already? I also see it's not used anymore as Joe adapted your advice to use EMPTY(State), it should better stay at SCAN FOR STATE==lcEmptyValue to make sense again. And just to be clear: It does not depend on an index on state to work, it will find empty state field without an index, too, and it won't matter much, unless you would have ten thousands or even millions of LM_STDNT records. I wouldn't write code that has a prerequisite without then telling how to get there. And last not least STATE==lcEmptyValue (or when == doesn't work STATE=lcEmptyValue) is also not slower then EMPTY without an index. EMPTY considers more than = comparison and so it actually is overused, mainly because it gives a little semantic meaning to the code, it's illustrative to look for some EMPTY field. But empty simply means the field contains just spaces, so we actually only need EMPTY() for when a) different data types could be checked for being empty, empty is not only working for strings. It's not the case here, and it's a bit lazy to use a more general function when you already know the specific case is about empty strings.
And if you don't have an index on state, then create one, Joe. That already propels the finding of rows with empty state.
I spot what you did completely wrong here: You LOCATE while you SELECT LM_STDNT, You locate the info of the same first LM_STATE record (which you never touch) from in LM_STDNT. It's no wonder you get no progress, you messed it up.
The code scans for students with no state. So you're fine with the position in the students' table and now want to find the record in TEMP, in LM_STATE as you later told us. Both mine and Mikes code SELECT TEMP in the scan loop body before the locate, that translates to SELECT LM_STATE, not SELECT LM_STDNT. You already have a student at hand with no state set and you want to locate and find the matching state => LM_STATE! You have to look in the right direction.
On top of that, but as a consequence of it you swapped around the matching conditions, which doesn't matter very much as a=b is equivalent to b=a, but VFPs matching differs a bit from that and makes matching depending. At this point, it would be best if field widths of the fields involved would be the same, not one email field c(40) and the other c(30), for example.
And last not least you didn't bracket the combined condition on last and first name. It's working because OR has precedence over AND, but I wouldn't rely on that. Whenever you would extend the conditions this should continue to work and thus it would just be good to bracket the partial conditions that belong as one, in this case the separate comparisons of first and last name always need to be combined into one boolean result of the fullname matching. The precendece of AND does that in this overall condition just ORing one more condition about the mail, but if it gets more complex this could have all kinds of side effects, if you miss out on bracketing then, so just bracket in advance, it also makes code better readable:
Code:
Procedure Ma_Test
lcMsg = "Update LM_STDNT.STATE FIELD"
=MSGBOX(lcMsg,"MA_TEST PROGRAM",gbOK+giInfo)
* activate window debug
* set step on
lcMsg = "Make sure LM_STATE table is correct!"
lcMBTitle = "Ready to Update LM_STDNT?"
If MSGBOX(lcMsg,lcMBTitle,gbYN+giQmark+gf2nd) = grYes
Use LM_STATE In 0
Use LM_STDNT In 0
Select LM_STDNT
lcEmptyValue = SPACE(LEN(LM_STDNT.STATE))
Scan For LM_STDNT.State==lcEmptyValue
* [highlight #EF2929]Select LM_STDNT[/highlight]
[highlight #FCE94F]Select LM_STATE[/highlight]
Locate For ([highlight #FCE94F]LM_STATE[/highlight].email = [highlight #FCE94F]LM_STDNT[/highlight].email) Or [highlight #FCE94F]([/highlight][highlight #FCE94F]LM_STATE[/highlight].f_name = [highlight #FCE94F]LM_STDNT[/highlight].f_name And [highlight #FCE94F]LM_STATE[/highlight].l_name = [highlight #FCE94F]LM_STDNT[/highlight].l_name[highlight #FCE94F])[/highlight]
If Found()
Replace State With LM_STATE.State [highlight #FCE94F]in LM_STDNT[/highlight]
Endif
Endscan
Close All
Endif
Return
The order of a=b vs b=a also is important for the optimization. Foxpro looks for indexes in the table located in anyway, but it's much more suggestive as the seek operation resulting when you think of finding the student email value (on the right hand side) in the state (temp) table email.
And after all that, since now LM_STATE is selected when you do the replace, it becomes important to use the IN clause of the REPLACE. And it's advisable to do so always, even when you know the workarea you replace in is selected. Specifying REPLACE LM_STDNT.State is actually guiding to the right workarea in simple cases, but the usage of REPLACE is not using fully qualified names, but split them in field name names only in the single [tt]field WITH sourcevalue[/tt] clauses and then finally specify IN workarea to tell of which workarea all the fields are on the left hand side of all the [tt]field WITH sourcevalue[/tt] clauses. The sourcevalues indeed are best fully qualified names, when the value isn't just a literal value but has a source, especially when its a field with same name, so there it is correct.
It makes it redundant to say REPLACE LM_STDNT.field WITH x IN LM_STDNT as the IN already addresses the LM_STDNT workarea. VFP will understand that you don't mean LM_STDNT.LM_STDNT.State.
It makes it dangerous to REPLACE LM_STDNT.field WITH x without an IN clause when another workarea is currently selected as there always is the implicit IN clasue IN "current workarea". That's a very stringent way Foxpro works in a current workarea. But if now LM_STATE is selected and you REPLACE LM_STDNT.State WITH LM_STATE.STATE, what should the implicit LM_STATE.LM_STDNT.State mean? You'll be lucky Foxpro then actually interprets that the way you intend it to mean LM_STDNT.State, but I wouldn't rely on it in more complex situations with more open tables.
You may also first manually SELECT LM_STDNT again, and do what the SCAN loop will do anyway, as the REPLACE Actually also should work in LM_STDNT. It's that table, that's missing the state. But again, that also means you can't hope to find the missing state it in the LM_STDNT dbf, can you. The locate for finding the state must be in LM_STATE, that should be as clear as it can be. So you have a scanloop that switches back and forth between two workareas, as the LOCATE has no IN clause and can't be made to Locate in another table than the currently selected one.
See how much I need to explain. People always complain SQL is complex and not easy to understand, but when you're not in the mindset about current workareas this is even harder to see then what a more or less complex SQL query does.
Finally, if you actually would need to locate for the state information in other LM_SDNT records, this would have an awful effect, do you spot it? it's totally possible the info could be in another LM_STDNT record. But then you iterate rows of LM_STDNT and then jump forth and back in that table. How should a loop on that ever find an exit? You're moving the loop pointer within the loop. This is the recipe for endless loops and that's what you did encounter. In the end you just mistranslated what was given to you. You had all the time to give us the real names in the first place and then wouldnÄt mistranslate this back to the real names.
Bye, Olaf.
Olaf Doschke Software Engineering
