×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Fox 2.6 Append From
2

Fox 2.6 Append From

Fox 2.6 Append From

(OP)
Hi Experts,

I have a table "CUSTOMER" that contains a bunch of data fields such as FIRST, LAST, EMAIL, and STATE. However I noticed that my STATE field has no data. I have another table "TEMP" that contains that STATE information and I would like to update the CUSTOMER table. The STATE table contains FIRST, LAST, EMAIL, STATE for validation purposes.

So essentially I want to take the data in field temp.state and append it to customer.state as long as one of these conditions exist
customer.email = temp.email
customer.first+" "+customer.last = temp.first+" "+temp.last

I'd like to execute this as a small program so I can do this process regularly and quickly while I figure out how to fix the problem of STATE being blank in the first place...

Thanks,
Joe

RE: Fox 2.6 Append From

Well, aside from APPEND being term for adding new rows, while you want to update rows missing a set, I understand your demand.

Obviously the APPEND command won't do that job, so you need a nested scanning of the two dbfs and replace state with tmep.stat in customer.

Overall, if it's not much data missing, I'd mainly scan the CUSTOMERS dbf for empty states in the first place and then locte them in temp:

CODE

Local lcEmptyValue
* lcEmptyValue = SPACE(LEN(STATE))
USE TEMP in 0
USE CUSTOMERS in 0
SELECT CUSTOMERS
lcEmptyValue = SPACE(LEN(CUSTOMERS.STATE))
SCAN FOR STATE==lcEmptyValue
   * try to locate state in temp for match by email or name
   SELECT TEMP
   LOCATE FOR (customer.email = temp.email) OR customer.first+" "+customer.last = temp.first+" "+temp.last
   IF FOUND()
      REPLACE STATE WITH TEMP.SATE IN CUSTOMER
   ENDIF
ENDSCAN 
Edit: I fixed when to determine value of an empty state. Obviously you can only determine len(state) when the customers table is openend.

This does about the same as an SQL-Update does:

CODE

UPDATE CUSTOMERS FROM CUSTOMERS LEFT JOIN TEMP ON (customer.email = temp.email) OR customer.first+" "+customer.last = temp.first+" "+temp.last WHERE CUSTOMERS.STATE==lcEmptyValue 
And if your table is 2.6 but you have VFP9 you can use that SQL on the legacy dbf, VFP knows how to teat legacy dbf files and still can apply its SQL engine on them.

If you do this within FP 2.6, you might not have SCAN..ENDACAN, only DO WHILE NOT EOF(), then you need to modify the loop code a bit not forget to SELECT CUSTOMER again and SKIP 1. Do you need help with that? I'm sure you fin samples of such loops in existing code.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

If you were doing this in Visual Foxpro, you could easily do it with a SQL UPDATE command, but since you are using 2.6, you would need something like the following:

CODE -->

SELECT Customer
SCAN
  SELECT Temp
  LOCATE FOR Cutomer.Email = Temp.Email AND ;
    customer.first+" "+customer.last = temp.first+" "+temp.last
    
  IF FOUND()
     SELECT Customer
     REPLACE State WITH Temp.State
  ENDIF 
ENDSCAN 


I haven't tested this, and can't be sure that it works correctly, but it should give you a start.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

Ha, Olaf been me to it by four minutes. But good to see that we are thinking along the same lines.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

One other point to note is this:

customer.first+" "+customer.last = temp.first+" "+temp.last

This might not give you exactly what you expect. Keep in mind that these names will be padded with spaces, so customer.first+" "+customer.last might evaluate to something line
"Abraham    Jones     " 
.

Better to simply do this:

customer.first = temp.first AND customer.last = temp.last

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

With the slight difference I only update CUSTOMER rows when their state is empty, but yes, generally the same idea.

I wonder about LOOKUP(), never made much use of that function, but I am also pretty sure it wasn't a legacy Foxpro thing. Maybe it actually was and existed even before SET RELATION.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

Mike, good point about the name concatenation. It still can suffice if both tables have the same structure and field length.

Single field comparisons could always make use of indexes on these single fields without the need for a special index on an expression, but then there might be an index on that expression.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

No, I don't think LOOKUP() was available in 2.6. I think SCAN / ENDSCAN was available, but I'm not certain.

What 2.6 did have is the JOIN command. So you could do something like this:

CODE -->

SELECT Customer 
JOIN WITH Temp TO NewTable ;
  FOR Cutomer.Email = Temp.Email OR ;
    customer.first+" "+customer.last = temp.first+" "+temp.last 

The difficulty is that this will create a new table rather than update the existing one. But you could then perhaps rename Customer to something else, and then rename the new table to Customer. Not a very elegant solution.

There is also the old form of the UPDATE command (not the SQL version). That should work, but, if I remember right, it requires a relationship on a single field rather than an expression.

I wish I had kept my old 2.6 manuals.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

Do you have the VFPX version of the helpfile? It has the full description of the old UPDATE command in it, too.

It's describes starting with

CODE

UPDATE ON field FROM file 
Where field must be a common field of the current workarea you want to update whd the file you update from. And only a single field, as you say, not an expression.

There's also a strange condition when to end this command on a keyword/option "RANDOM" when the workarea is not sorted in an index order. But before I reproduce the whole help topic, it's available within the VFPX help version. When you search in it and add the search term 2.6 you get the legacy help topic texts with a VFPX disclaimer of these texts not being in the original VFP9 helpfile and stating that "it may still be valid".

You never know what happens to code only kept for downward compatibility in the context of changing foundational classes, but I believe in the first portion of the usual only sentence in the modern help: "Included for backward compatibility. Use ... instead."

I'm quite sure they stopped running the old functions through unit tests, but who knows, maybe they even did keep such parts of the overall C++ project sources of VFP, it would not be an effort, it would indeed be the "lazy" solution of backward compatibility to only extend and not actually deprecate and remove features.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

If I search for lookup 2.6 nothing comes up. But that's the problem with things still in the core language of even VFP9, these topics get updated and don't have a history in them or an attribute "added in VFP version X", that would sometimes be helpful to know.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

Olaf, no I don't have VFPX help file in front of me. I'll grab a copy when I have a moment. But down in the cellar I just came across an original Foxpro 2.0 package, about the size of a small desktop computer. I can see the docs for the UPDATE command, including the RANDOM keyword. I'm not sure how useful Joe will find this, nor indeed the JOIN command. He had better stay with the code we gave him.

On your point about whether the backward-compatibility commands still work correctly in the recent versions, it would be an interesting little project to test that.

By the way, I see that SCAN / ENDSCAN was available in the 2.0 (and therefore 2.6), so no need for the clumsy DO WHILE NOT EOF() / SKIP / ENDDO construct.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

If temp has all the records in it that are in your original database, perhaps you could zap the original and just append from

Regards

Griff
Keep Smileing

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

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: Fox 2.6 Append From

(OP)
wow Mike & Olaf, thanks. I will sort through the feedback and give it a go OR be back with another question before hitting enter.

(I do not have VFP so will need to use do while... I will have examples of these. Fingers crossed I patch together all the notes properly :)

Hi Griff, the temp database only has the few fields and was created to help get the customer.dbf updated.

RE: Fox 2.6 Append From

I actually got a few 3.5" floppy discs with original FPW2.6 as a gift once, but I never used that. It was a nice idea and I was once in a while considering to give it a go to see what can be done within it's limitations, but in the end I was already glad I only once needed to go back into VFP6. You only see how far things have changed when you go back. I found the IDE itself less stable, for example, and multiple times failed on commands not available in VFP6.

Not to contradict myself - regarding forms and controls there still is mainly the docking and anchoring mechanisms that added properties to forms and controls, but no new control, not even a splitter control. Obviously you can plan to put some sections in containers und use anchoring now, but the ease of putting a splitter in a form is not available. Really useful would have been a treeview. Thankfully VFP can use ActiveX controls, but you can't just rely on ActiveX=working fine in VFP, so you mainly use controls from the handful of vendors that cared for them working in the VFP context.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

Glad we were able to help, Joe. Let us know how you get on.

By the way, you said:

Quote:

I do not have VFP so will need to use do while

As I mentioned in my previous post, SCAN / ENDSCAN is available in 2.6. so you don't have to use the less convenient DO WHILE / ENDDO.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

(OP)
Ok guys... not to complicate things, but here's the final code

A few notes...
1. the aforementioned customer = LM_STDNT
2. the aforementioned temp = LM_STATE
3. I went with Mike's concatenation advise as the tables are not structurally aligned
4. You may recall from prior posts that my dad wrote all my programs. This Ma_test is a way for me to run small programs within the application...
5. I received a compile error on line 9 Local lcEmptyValue (Unrecognized command verb.) It's * for now
6. Can you also verify the REPLACE Statement (Could it be REPLACE LM_STDNT.STATE WITH LM_STATE.STATE)

THANK YOU VERY MUCH


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
* Local lcEmptyValue
* lcEmptyValue = SPACE(LEN(STATE))
USE LM_STATE in 0
USE LM_STDNT in 0
SELECT LM_STDNT
lcEmptyValue = SPACE(LEN(LM_STDNT.STATE))
SCAN FOR STATE==lcEmptyValue
* try to locate state in temp for match by email or name
SELECT LM_STDNT
LOCATE FOR (LM_STDNT.email = LM_STATE.email) OR LM_STDNT.f_name = LM_STATE.f_name AND LM_STDNT.l_name = LM_STATE.l_name
IF FOUND()
REPLACE STATE WITH LM_STATE.STATE IN LM_STDNT
ENDIF
ENDSCAN
CLOSE ALL
ENDIF
RETURN

RE: Fox 2.6 Append From

I'm not sure why you got an error message for the LOCAL statement. It looks OK to me. But in any case, you can avoid the error and simplify the whole thing slightly by changing your SCAN statement to:

SCAN FOR EMPTY(State)

The REPLACE looks OK to me as well. I don't think you need the IN clause, as LM_STDNT is the selected table. But, really, you are in a better position to judge the correctness of your code than we are, as you can test it and check the result.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

I would be astonished if legacy Foxpro doesn't know the scope of local variables, it wouldn't matter for a short program to create the variable at first assignment, so indeed not so important, you might indicate lcEmptyvalue as a private variable with PRIVATE lcEmptyvalue, but that will be the default scope anyway and within a single PRG there even is no difference.

Strictly speaking, you'd then use PRIVATE pcEmptyvalue or perhaps the naming convention even was without prefix and such ideas only came in later, indeed you can name things quite as you like, often enough even with keywords, as long as the commands used are not becoming ambiguous by such names.

But Mike, the whole reason for the variable in my code is to avoid the EMPTY() function and have the chance of Rushmore optimization of the condition State==lcEmptyvalue, if just a regular index on state exists, and this is even very probable.

You would need an index on EMPTY(state) to optimize FOR EMPTY(state), and you rarely will have such an index.

It might not matter much depending on the volume of data, but indeed a scan for state==lcEmptyvalue with just the standard empty value a char field has, all spaces in the length of the field, will find them much faster, especially when it's just 10 among thousands with a non-empty state.

While Empty() is nicer to understand and index usage for performance is sometimes just a luxury, it can also become essential.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

Silly of me. I forgot for the moment that we are dealing with 2.6. Of course, LOCAL didn't exist then. In fact, for a small stand-alone program like this, I woould just leave it out.

And, Olaf, I take your point about Rushmore optimisation of the SCAN.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

No problem, Mike, we'd still both fail on NULL values, which as I find exist, but are not as easy to reeset to as in visual Foxpro, it seems, and also need the ISBLANK() function to be detected.
Q99095: How to Reset a Database Field to a Null Value in FoxPro

I assume though, that a state char field would simply contain space(len(state)), all spaces, as its still usual for char fields.

Bye, Olaf

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

Or, what about this command to set the field to spaces:

BLANK FIELDS State

But was that available in 2.6?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

Avvordng to Q99095, yes.

And this also tells thats the initial state after APPEND BLANK and state wasn't set yet.
And ISBLANK(field) for null values in 2.6 fields, which must differ from today's NULL storage. As today ISBLANK() is .f. for Null fields.

I don't know if the knowledgebase is not precise or VFP nowadays doesn't behave as fp2.6 for 2.6 dbfs.
At least creating a fox2x dbf and appending blank

CODE

Create cursor crstest (state c(20) null)
Copy To nulltest type fox2x

Use nulltest.dbf
Append Blank 
? 'empty, isnull, isblank, Space(Len(state))'
? Empty(state), IsNull(state), IsBlank(state) , state==Space(Len(state)) 

What VFP9 creates and populates now behaves equal to spaces, also when you add DEFAULT null or DEFAULT .NULL., which fp 2.6 wouldn't know.
But VFP can define a cursor with default .NULL., of course, then save as fox2x and then append blank does not create .null. within the field.

I actually remember back in VFP6 or 7 EMPTY() and ISBLANK() could differ depending on whether the field was never set or not, but in VFP9 I don't see a case where isblank differs from empty().

It's clear empty() also judges tabs as whitespace, and CR and LF, but none of those would be a default, you'd explicitly need to set a field to contain such whitespace, so a string of spaces in the width of a field is a good replacement for EMPTY() in case of index usage, checking EMPTY() has still good cases when you need on detect other whitespace, too, ie for parsing code.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

(OP)
Hi Guys,

I seem to be caught in some kind of loop. Below is the final code.,
I got rid of the original line 9 Local lcEmptyValue
When line12 is not commented out, the system hangs (the lovely never ending hour glass)
When commented out, it's a definite loop. In the status bar i see a series of changing numbers/TOTAL Record Number (493)
LINE 12 lcEmptyValue = SPACE(LEN(LM_STDNT.STATE))

Extra Info... I do not have any indexes on the LM_STATE table. do I need to index on email, f_name and l_name?
Set Relation? Sorry, my knowledge is minimal...

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 EMPTY(State)
SELECT LM_STDNT
LOCATE FOR (LM_STDNT.email = LM_STATE.email) OR LM_STDNT.f_name = LM_STATE.f_name AND LM_STDNT.l_name = LM_STATE.l_name
IF FOUND()
REPLACE LM_STDNT.STATE WITH LM_STATE.STATE
ENDIF
ENDSCAN
CLOSE ALL
ENDIF
RETURN

RE: Fox 2.6 Append From

Joe,

Before we go any further, may I make a suggestion - and a request.

When you post long a piece of code in a forum message, please format it with [code][/code] tags. You can do that either by typing the tags manually or by highlighting the code and the clicking on the Code button in the toolbar (immediately above the messaging area).

The reason to do that is to preserve the indentation in the code, which makes the logic easier to follow. It also has the benefit of applying a mono-spaced font, which is generally preferable for computer code.

If you look back at other posts in this thread, you will see several cases where this has been done.

In addition, it is a good idea to insert blank lines, so as to break the code up into logical chunks. And you should also break any particularly long lines into two or more shorter lines, using a semi-colon as a continuation character (such as in your LOCATE command). This avoids the reader having to horizontally scroll the window.

I have taken the liberty of applying the above suggestions to the code in your most recent post. This is the result:

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 EMPTY(State)
        SELECT LM_STDNT
        LOCATE FOR (LM_STDNT.email = LM_STATE.email) OR LM_STDNT.f_name = LM_STATE.f_name AND ;
            LM_STDNT.l_name = LM_STATE.l_name
        IF FOUND()
            REPLACE LM_STDNT.State WITH LM_STATE.State
        ENDIF
    ENDSCAN

    CLOSE ALL
ENDIF

RETURN 

I hope you will agree that this is much clearer and easier to follow than your version of the same code.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

Regarding the line:

lcEmptyValue = SPACE(LEN(LM_STDNT.STATE))

As far as I can see, that line will make no difference to your code, regardless of whether or not it is commented out. What you are doing here is creating a variable (lcEmptyValue) and storing a string of spaces in it. The variable is not used anywhere else in your code, and so the line has no effect on the result.

That doesn't explain why the system appears to be hanging. I can't off hand see any reason for that.

I see you have coded this as a procedure. Do you have some more code somewhere that is calling that procedure? Or are you running it directly from the command window?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

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
         * Select LM_STDNT
         Select LM_STATE
         Locate For (LM_STATE.email = LM_STDNT.email) Or (LM_STATE.f_name = LM_STDNT.f_name And LM_STATE.l_name = LM_STDNT.l_name)
         If Found()
            Replace State With LM_STATE.State in LM_STDNT
         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 field WITH sourcevalue clauses and then finally specify IN workarea to tell of which workarea all the fields are on the left hand side of all the field WITH sourcevalue 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
https://www.doschke.name

RE: Fox 2.6 Append From

Just by the way, Mikes amended code still has this most blatant error f locating in LM_STDNT.

You are already at a record in LM_STDNT and want to find the LM_STATE to get the State.

Anyway, it reminds me of the fact Griff had this suggestion/question:

Quote (Griff)

If temp has all the records in it that are in your original database, perhaps you could zap the original and just append from

You already reacted to this saying

Quote (jlg13)

the temp database only has the few fields and was created to help get the customer.dbf updated.

So we know a bit more about temp just being a subset of fields. It's still unclear, what records are in there. If you only have the few students in LM_STATE, that need to be updated in LM_STDNT then it also would make sense to SCAN through them and LOCATE in LM_STDNT. Especially if this is the few corrective rows, then you want to process all of them. Mike and I concentrated on the other point of view, on the fact you want to correct LM_STDNT with empty state info. You might not even have all missing info in LM_STATE.

It would actually be good to tell such things, as we can only make assumptions about the number of rows and which rows matter the most. With the information as given both Mike and me concluded to first search for LM_STDNT with empty state is important to possibly mend those it was the safest way to handle this, we could be sure this is working. When you have all corrective records you could get in LM_STATE then this drastically changes the best strategy. Because it actually removes the effort to first find a LM_STDNT missing its state information, we already know every row of LM_STATE has corrective information that needs to be sorted to the right LM_STDNT record.

See? Giving out the problem statement in the most relevant way also is important. Because I can easily see how you mistranslated our solutions if you have that in front of you and in your mind. But you didn't tell us. Then it makes almost no sense to scan through all, perhaps thousands of LM_STDNT when all you have is a few LM_STATE data corrections.

What is known from the outset of the fields existing in LM_STATE about LM_STDNT mail and name, it's clear there is a 1:1 relationship between them no matter what fields more each table has, so you can tackle it from both sides. Actually, as you are not specific about that it could also be LM_STATE has multiple records for an email, but far less likely.

It makes sense to tackle it from the side that has fewer rows in it. But then, when you turn it around, also watch out whether you really want to update an LM_STDNT with LM_STATE.state, when LM_STDNT already has a state. Another reason both Mike and me got the idea independently to tackle this from the LM_STDNT side and first find rows there missing information.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

(OP)
Hi Olaf,

Thanks so much for the detail, the explanation is very educational for me. I admit, I will have to read it a few times to let it sink in.. :)

I just want to make sure to remind you we are dealing with Fox 2.6 here. I took the code above and received a compile error.

Replace State With LM_STATE.State in LM_STDNT
Error in line 18: Unrecognized phrase/keyword in command.

Did I miss something that I was supposed to adjust from the code displayed?

Thank you,
Joe

RE: Fox 2.6 Append From

Joe, I'm not sure which is line 18, but if it is the REPLACE, it might be because the IN clause wasn't supported in 2.6. But I have now way of checking that. (Don't you have some documentation for 2.6? If so, when you get this type of error, you should always check the docs to see the correct syntax.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

(OP)
Hi Mike, you are correct, it's the Replace line. Apologies. I edited my last post to include the code.
RE: Documentation: It's all at my dad's house however, I suspect I could find that on online. Next would be understanding it :) (Novice)

RE: Fox 2.6 Append From

In the absence of an IN clause, you would have to explicitly select the student table before doing the REPLACE, and then select the state table again afterwards:

CODE -->

Select LM_STATE
Locate For (LM_STATE.email = LM_STDNT.email) Or (LM_STATE.f_name = LM_STDNT.f_name And LM_STATE.l_name = LM_STDNT.l_name)
If Found()
    Select LM_STDNT
    Replace State With LM_STATE.State 
    Select LM_STATE
Endif 

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

Sorry about tha, Joe, but it should also be clear were working on never before encountered territory here (from my side) or long left.
That the VFPX help has 2.6 help topics helps a bit, but for example it does not have the historic description of REPLACE, as REPLACE still is a current command, and has more features now, it seems.

That's again something I would never have expected, but then again LOCATE doesn't support an IN clause even today. Also really an annoyance.
Yes, Mike sorted that out, then you have to explicitly SELECT LM_STDNT first before doing the REPLACE without the IN clause.

The rest of it would be to have some indexes but before we need to guess what works in 2.6 and what not just use that now and see whether it's sufficiently fast already.
I don't think yoú have to reselect LM_STATE after the replace, as Mike does, because next up is the ENDSCAN and that does that itself. Unless Fox2.6 differs from that again.
So, it actually can't hurt, but it would be optional, if you can simply check what this tells you about automatically selecting the scanned workarea:

CODE

CREATE CURSOR crsTest (f1 int)
APPEND BLANK
CREATE CURSOR crsLoop (f2 int)
APPEND BLANK
APPEND BLANK
SCAN
   ? ALIAS()
   SELECT crsTest
ENDSCAN 

And I don't know if ALIAS() is available.
If SCAN/ENDSCAN automatically reselects the crsloop that was selected at first entry of the scan..endscan loop, the alias displayed will always be crsLoop. If not, the alias will be crsTest for the second iteration.

Edit: If ENDSCAN would SKIP 1 in whatever is the current workarea and exit at EOF() there, you would expect just one output of "crsLoop" and no second iteration. If ENDSCAN does SKIP in crsLoop but doesn't select it you would still get two iterations and a switch of the alias name. So such a test code would reveal that behavior as you also don't have the docs at hand.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

I did a few more tests of REPLACE behavior and am puzzled a bit about the last REPLACE here.

CODE

On Error ? Lineno(), Message()
Clear

Create Cursor crsOne (iInt Int, iInt1 Int)
Insert Into crsOne Values (10,11)
Create Cursor crsTwo (iInt Int, iInt2 Int)
Insert Into crsTwo Values (20,22)

? crsOne.iInt, crsOne.iInt1, crsTwo.iInt, crsTwo.iInt2 && 10,11,20,22

Select crsOne
* Skip 1
* ambiguous name, but unambiguos in the current workarea
Replace iInt With 30
? crsOne.iInt, crsOne.iInt1, crsTwo.iInt, crsTwo.iInt2 && 30,11,20,22

* unambigous name in the other workarea
Replace iInt2 With 40 && fails with "Variable 'iint2' is not found"
? crsOne.iInt, crsOne.iInt1, crsTwo.iInt, crsTwo.iInt2 && values as before

* fully qualified name addressing
Replace crsTwo.iInt2 With 50
? crsOne.iInt, crsOne.iInt1, crsTwo.iInt, crsTwo.iInt2 && 30, 11, 20, 50 && stays 22 after SKIP 1, though

* explicit workarea addressing with IN (only in modern FoxPro)
Replace iInt2 With 60 in crsTwo
? crsOne.iInt, crsOne.iInt1, crsTwo.iInt, crsTwo.iInt2 && 30, 11, 20, 60 

I intentionally create two fields of same name in the two cursors and one differing to see what REPLACE addresses. As expected it works in the current workarea. That's why I also expected the error about the third replace of iInt2, because all the time crsOne is selected current workarea and has no iInt2 field. But when you actually address it with fully qualified name it is set.

I thought I know REPLACE only works by the rules
1. Act on fields of the current workarea or workarea given by IN clause (not available in 2.6, obviously)
2. Act on other workareas when a relation is set between current and other workareas

It seems to be enough to be in any current not on end-of-file position (which is third rule) and you can act on the current record of any open workarea by the fully qualified name. On one side that opens up a lot of possibilities, but also a lot of possible misbehavior. At least your replace can't bleed into other workareas unintentional just by the same field names and you can only address other workareas intentional by the full name. But indeed you can. At least in VFP9.

So you could even keep LM_STATE the current workarea and REPLACE LM_STDNT.State WITH LM_STATE.State

With the IN clause available I would rewrite that as REPLACE State WITH LM_STATE.State IN LM_STDNT, maybe just because I'm more used to that addressing. Partly as a defense of the case that could break all code depending on the current workarea to be correct: When a timer event switches to another workarea without resetting that. Partly as it's verbose and documents better what's done, as the last SELECT workarea may be many lines up in the code. REPLACE LM_STDNT.State WITH LM_STATE.State would also cover all aspects of being verbose and is self-documenting explicitly, but the difference is about which EOF is important. You'll not be able to replace something at EOF anyway, but see what happens when you uncomment the SKIP 1. As you're now on EOF('crsOne') no replace does anything, despite the erroneous still causing an error. A replace hitting EOF in crsOne doesn't replace there - because there is no data to replace in a nonexisting row. It's okay, but slips through silently. The replace of the fully qualified field name crsTwo.iInt2 also is not happening, because EOF('crsOne') is hindering that, we're not one EOF('crsTwo'), though, and thus a replace IN crsTwo is possible and works with an IN clasue available.

That's a very delicate detail, and you have no timer working in parallel, especially no such rogue timer doing unforgivable things. So you don't depend that much on the IN clause anyway. You're also not at EOF in both workareas by definition, the one you scan is not at EOF, the other in which you located AND found something is also not at EOF - Found is nothing but checking NOT EOF, actually. But that this works is new to me. And it should also be clear why I prefer IN workarea to have full control which EOF() is of importance.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

All very interesting, Olaf. To be honest, I've never felt sure about the use of alias.fieldname as the target for the replace, as in:

CODE -->

SELECT csrOne
REPLACE csrTwo.Int2 WITH 50 

I've seen the same behaviour that you described where the replacement appears to have worked until you skip. I've generally avoided using an alias.fieldname in this way, and tend to always explicity use the IN clause.

The rest of your code appears to work exactly as I would expect.

Regarding your point re using a timer. That seems to be a strong argument for saying that a timer event should never do things like change the work area or the index order; it should only ever do things that can have no effect anywhere else, such as displaying a message.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

I agree with you on the derived rules for timer, but then for example I do work on tables in VFPTransactions and the timer I use triggers a logque to do something. Most of the objects involve will work in different datasessions, but for reasons of participating in a transaction, I also work in the current datasession, effectively. What I never touch itself is the table triggering the dbc triggers, I only make a copy of its record with SCATTER and then leave it to other (inherited) trigger code to establish referential integrity or other things like cascading actions. I also cascade this trigger, but only to log tables and delay actions by queueing them.

But with referential integrity stored procedures you have another category of code that acts triggered by changes in workareas and that can be triggered simply ba the scan loop leaving off a record with a row buffered value, so it also has no usual call and is an event happening.

It's obviously important to set things back in timers. How sure can you be about third party code or an application you work on for a job? Indeed such things should be avoided at all costs. I still see no reason to relax the conditions on the other side, outside of timers. Just because I set up strict rules for timers, there are always unpredictable things, even other developers changing your code. If you do both you will have the least possibility problematic situations ever occur. Wearing a seatbelt and driving below speed limits as a driver and looking before crossing a street as a pedestrian. Why should you disregard one thing?

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

(OP)
Gents,

THANK YOU...

Just wanted to paste the final code below that worked great. Thank you both for all this information and effort. Not only did you help solve the problem but the discussion was very educational for me. I know how to figure out how to get State to update along with other data so I don't have to run this program weekly to keep syncing... :)

I was wondering if either of you could share information on how difficult the task would be to migrate from 2.6 to VFP 9.0. I have 2 platforms that run on fox 2.6 and one of them is VERY basic and would probably not require much work at all... literally one PRG supported by a couple of startup prgs and a series of screens. My dad worked on that for a few months, a few days per week as needs arose. The other platform is a series of 30+ prgs and screens and operates a "Billing" company. Just curious of your thoughts...


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
      Select LM_STATE
      Locate For (LM_STATE.email = LM_STDNT.email);
       Or (LM_STATE.f_name = LM_STDNT.f_name And LM_STATE.l_name = LM_STDNT.l_name)
      If Found()
        Select LM_STDNT
        Replace State With LM_STATE.State 
        Select LM_STATE
      Endif 
      Endscan
      Close All
   Endif
   Return 




P.S. MIKE.... Sorry I did not follow your instruction above about pasting code. I will review and learn for next time. Thank you again

RE: Fox 2.6 Append From

First, it's not hard to try.

When you open a project copy in VFP9 you get prompted for doing a visual or functional conversion. I think if I understood someone correctly you better not go through that, but simply add your SPR (screens) etc. as is to a new VFP0 PJX and thus stay with screens. You'll need to then do without the screen designer and work on the SPR code itself.

I never had to deal with a real conversion. So perhaps rather wait for Mikes and others advice. What I can point out is that along the way VFP changed a few things cause screens to look much different in VFP,
one thing to do is to turn off theme support of VFP by setting _SCREEN.Themes = .F.

With _SCREEN.Themes = .T. one bug with screens is that the interface may disappear and only appear once you move the mouse over inputs, etc. You might have reports and they surely will not use new features so SET REPOTBEHAVIOR 80 or keep it at that, unless you develop new reports if there are any at all now.

Then, there is also SET ENGINEBEHAAVIOR about SQL, but I think we don't need to talk about that.

-----------

About posting code, you can still change your post, there is an edit link at the bottom right of your post. When you're in the editing page you'll first see the post as it shows on site then the "source code" of it in the textarea with toolbar as known. Just select your code in the area and click on the code icon. It's the <> brackets on a sheet of paper icon, between the person with a speech bubble (quote) and the yellow gift wrapped with a red ribbon (spoiler) You can also use that spoiler icon. Just kidding, it'd put your code in a box that hides its content unless you select it.

These icons add the necessary tags around your selection, which also is valid for the simpler ones like writing bold or italic or underlined text, it always acts on the selection, with the usual irregular things like clicking on the image icon starts a dialog/process to upload a picture and that's put where the cursor is.

When I post I let the code icon create the open/close tags and then copy my code in between. Or I simply type them.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

It's a very long time since I had to convert a program from 2.x to Visual, so what I am about to say subject to my ageing memory.

Converting PRGs is relatively easy. In fact, most of the time, you won't have to change anything at all. The code should work almost exactly the same. There a few cases where backward comp ability has been broken, but they probably won't affect you. The biggest danger is that your 2.6 code might use certain names for variables or fields that have since become reserved words. These will become apparent as you run your code.

Converting screens is more problematical. In short, you won't be able to use your old SCX files, as these have an entirely different format. However, your generated SPRs should continue to run. These are essentially program files incorporating @/SAY and @/GET commands, and these work the same as before.

If you try to open an old SCX in VFP's form designer, it will give you the option of a functional or a visual conversion. Neither is satisfactory. I won't go into the reasons (unless you really want me to). I will just repeat what I said above. Discard your old SCXs (and SCTs) and just run the SPR.

All of the above will provide a route to your conversion. But a much better approach would be to re-design your application from scratch - or at least the visual parts of it. Learn how to use the VFP form designer and the many visual controls that come with VFP (textboxes, buttons, pageframes, combo boxes, etc/). Then create a form for each of your old screens. You will be able to copy and paste much of the snippets from your old screens into the relevant methods and events of your new forms, but it really does make sense to design the forms themselves from the ground up.

A similar approach is suitable for reports. Get to know the VFP report designer, and create a new set of reports.

Good luck with this. It's not a particularly easy process, but it is do-able. Just jump in and make a start. Be sure to keep a separate copy of your old programs so that you can continue to use these during the course of the conversion.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

I agree with Mike and Olaf (as so often) but I recall doing a visual conversion ca 25 years ago. It "kind of" worked initially, but maintaining those "forms" is pretty tough.
That is mainly because the converter for some reason puts all forms (called pages) into a formset (called pageframes). The result of this is that you can't just click on a textbox (or anything) and get that selected. You have press Ctrl and click to get through.

RE: Fox 2.6 Append From

I think you're right, Dan. That was one of the issues that I remember seeing. Also, in the visual conversion, all the code snippets from the screen were placed in a PRG file, and it was a hassle trying to match that up with the relevant events and methods of the form. I'm definitely glad I chose not to go down that route.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

(OP)
HI Mike and Olaf,

For practice... i edited the prior post as Olaf suggested to correct my code posting. Hope I did it correctly!!

Thanks again.

P.S. I am not qualified to migrate to VFP but I am at least thinking that this may be a step I should investigate. Imagine it's easier to run on more modern equipment and software. I'll start with the small application and go from there, In the mean time, expect to here from me as I continue to work in 2.6 and create problems :)

RE: Fox 2.6 Append From

Yes, the code section works now, also puts back the indentation.

Not qualified, as in not eligible to make the decision? Or do you feel this will be a too steep step just up and not forward? As Mike recommended you should keep the old IDE, and you can install in parallel. So you also have the full 2.6 and 9 help in parallel. And since most of your code is, well, just code, PRGS and SPRs, you will rather only use the text editor, that's no steep learning curve at all, but you get intellisense, autocompletion and inline help of parameters, for example.

You obviously will just have one file association with PRG, to VFP9, for example, but that doesn't matter much, from within the IDE you of course get at all project files. SPR might even stay a FP2.6 association. Ah, and while we're at dependencies, all clients will need vfp9 runtimes of course, but when you swap an exe that's just a bunch of more files. One file needs registering, which could be a hurdle, but it's only the exe associated with CHM help support. You don't need to start with that as a new feature and can indeed just copy over a few other DLLs with a new EXE for the upgrade.

As Mike also said there could be some incompatibilities. Well, the compiler will point out the lines and this may point out several occurrences of always the same problematic name, for example. VFP9 has a project spanning search and replace tool, so there's help you will find very productive.

And then it's your only chance to get forward with more modern code, even though development in something completely different of course also is an option.

Bye, Olaf.


Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

I haven't read the whole discussion. I just want to address Olaf's original issues regarding REPLACE because it's important stuff that everyone needs to understand and it's easy to write code that doesn't do what you expect it to.

REPLACE always operates in the current workarea UNLESS you include IN. If there's an IN, it operates in that workarea. You can always alias a field and change it, but unless it's in the workarea of the REPLACE (current workarea or the one specified by IN), you're operating on the current record in that table.

If those were all the rules, it would be easy. But here's the kicker. If you are at EOF() in the workarea of the REPLACE, nothing happens in any other workarea.

I've written about this a lot. Here's one example: http://www.tomorrowssolutionsllc.com/Articles/Work....

Bottom line: Always use IN with REPLACE and never replace fields other than in the workarea of the REPLACE.

Tamar

RE: Fox 2.6 Append From

On the subject of converting forms, I'm working on a large project with hundreds of forms to be converted. We've written a custom form conversion tool that starts by running the old form through functional conversion and then involves a whole lot more so we end up with a real VFP form that's about 70-80% converted. We do the rest by hand, but the grunt work of placing all the controls and setting ControlSources and so forth is done for us.

This is working only because the original developers were incredibly consistent, so we can automate a ton of work.

Tamar

RE: Fox 2.6 Append From

Thanks Tamar,

the point about EOF is understood and I also pointed it out. What I didn't know was that you actually can even address any workarea (and it's current row), even though no SET RELEATION connects to it. I thought I know you need to extend REPLACES scope by relating records and it would ignore anything not "joined" that way, as I also think of relations as Foxpros SQL replacement and SQL should of course also only act on the tables involved in a query.

It's not hard to understand how that works anyway, as all workareas, of course, have a current record, so it would be an intentional restriction to limit the scope the way I thought it is limited.

The corner case that EOF of the current workarea (or the one specified with IN) must not be at EOF cannot be stressed out often enough, so no problem, that you repeated it. It was the other reason I thought it would only work in the extended scope of other workareas as far as RELATIONS go, because in the end when no rows are found by relation it's a bit like SQL finding no outer join, and thus also not updating a row, perhaps. So again my thought was that was a way to have about the same scoping and limiting as you would have with SQL. So the way it is you will usually concentrate on the current workarea or one you specify, but you can actually also say "oh, and by the way, also replace that field there" in something not really involved. You may also just interpret it as being able to specify multiple IN workareas. Just that either the current or the specific IN workarea have their special influence by EOF, so you may pick them to be the indicator of finding something via a relation or not and only replacing under the FOUND() condition, which - as I said in this lengthy discussion - actually just means !EOF().

I'm clearly thinking too complicated. Just to finish the thought, when you'd use this EOF feature, which likely feels like a bug to many, you actually have more control in the cases we didn't discuss yet, when you also use the NEXT N or ALL scopes. I can't show an example spontaneously, but I think you can manage two workareas with a relation and especially in the case IN points to the non-current workarea and you make the EOF of that important, can do something like a correlated update depending on a match condition without SQL but using the ALL clause and the relation itself is your WHERE or INNER JOIN filter.

I guess most uses are for replacing one field of the current record anyway. It's easier to understand code that actually moves to the rows it wants to manipulate then using trickery with relations and the all scope.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

FWIW, I make a point of not aliasing fields in a REPLACE to make it extra clear that it's the IN clause that's determining what table we're talking to.

Tamar

RE: Fox 2.6 Append From

As I said.

Quote (myself)

With the IN clause available I would rewrite that as REPLACE State WITH LM_STATE.State IN LM_STDNT
(not REPLACE LM_STDNT.State WITH LM_STATE.State). As far as the error jlg13 got from a replace, Foxpro 2.6 does not support the IN clause,. But in his case the solution is to first select the LM_STDNT and then replace in there, as in the solution code:

CODE

Select LM_STDNT
Replace State With LM_STATE.State 

I'm still a bit surprised, that REPLACE didn't always have the IN clause, but it explains once more, why working in the current workarea is so in the blood of classic Foxpro developers.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Fox 2.6 Append From

Olaf, you say you were surprised that REPLACE didn't always have an IN clause. That is because REPLACE was one of the original Foxbase / dBASE commands, whereas IN didn't appear in the language until later (possibly in 2.x, but I'm not sure about that).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Fox 2.6 Append From

IIRC, none of the language except USE had the IN clause until they added it widely, which I think was in VFP 3. I was surprised recently to discover that it wasn't in FP2.6. (Working on an FPW->VFP conversion, where a lot of code will have to run in both versions for some time, so really being reminded what came in when.)

Tamar

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close