Fox 2.6 Append From 2

[jlg13]

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

 

[jlg13]

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

 

[Olaf Doschke]

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

 

[TamarGranor]

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/Working with Work Areas.pdf.

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

Tamar

 

[TamarGranor]

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

 

[Olaf Doschke]

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

 

[TamarGranor]

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

 

[Olaf Doschke]

As I said.

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:

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

 

[Mike Lewis]

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

 

[TamarGranor]

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