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

 

[Olaf Doschke]

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:

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:

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

 

[Mike Lewis]

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:

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

 

[Mike Lewis]

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

 

[Mike Lewis]

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 [tt]customer.first+" "+customer.last[/tt] might evaluate to something line [pre]"Abraham Jones "[/pre].

Better to simply do this:

[tt]customer.first = temp.first AND customer.last = temp.las[/tt]t

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

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

 

[Olaf Doschke]

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

 

[Mike Lewis]

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:

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

 

[Olaf Doschke]

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

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

 

[Olaf Doschke]

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

 

[Mike Lewis]

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

 

[GriffMG]

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 [Smile]ing

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. ​

 

[jlg13]

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.

 

[Olaf Doschke]

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

 

[Mike Lewis]

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

By the way, you said:

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

 

[jlg13]

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

 

[Mike Lewis]

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:

[tt]SCAN FOR EMPTY(State)[/tt]

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

 

[Olaf Doschke]

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

 

[Mike Lewis]

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

 

[Olaf Doschke]

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