Update records from another table in foxpro9

[Niki_S]

I have a table in foxpro like this.

[b]prodFty    WHCode     Qty      Fcty[/b]
SFC        1234D      456      
VTM        654F       12    
SGL        652Y       87    
FMF        1654I      120
FMA        875P       45

And I have another like this.

[b]Fcty[/b]
SGL

I need my table 1 like this,

prodFty    WHCode     Qty      Fcty
SFC        1234D      456      SGL  
VTM        654F       12       SGL
SGL        652Y       87       SGL
FMF        1654I      120      FMF
FMA        875P       45       FMA

Here I need to update Fcty field when the prodFty is SFC/VTM/SGL. Others I don't need to change anything and I need to update Fcty as the prodFty.
Can anyone please answer for this?
Thank you.

 

[mm0000]

locate
scan 
 if allt(prodfty) $ 'SFC/VTM/SGL'
    replace fcty with 'SGL'
 else 
    replace fcty with prodfty
 endif
endscan

If you want to update values from the fcty table, you will need to add the prodfty field to the second table to be able to link and update the fcty field in the first table with desired values.

 

[Niki_S]

scan 
 if (SGG_Final.cProdFty)= 'SFC/VTM/SGL'
    replace SGG_Final.cFtyCD with 'SGL'
 else 
    replace SGG_Final.cFtyCD with SGG_Final.cProdFty
 endif

I did it as above. but the records are not updated.
I can't add prodfty field to second table. Simple I need to update first table fcty field ,
If the cProdFty = SGL/SFC/VTM I need to update fcty field as SGL. If not I need to replace the same record in prodfty field

scan 
SELECT SGG_Final 
 if (prodfty)= 'SFC/VTM/SGL'
    UPDATE SGG_Final  Set prodfty = 'SGL'
 else 
     UPDATE SGG_Final  Set prodfty = fcty 
 endif
endscan

I tried this also , but not working.

 

[mm0000]

replace (SGG_Final.cProdFty)= 'SFC/VTM/SGL' with allt(prodfty) $ 'SFC/VTM/SGL' as shown in my example. Notice that i used $ and not = .

 

[Niki_S]

scan 
SELECT SGG_Final 
 IF allt(cProdFty) $ 'SFC/VTM/SGL'
  replace cFactory with 'SGL'
 else 
    replace cFactory with cProdFty
 endif
endscan

This is the code that I used, but not working.

CREATE TABLE C:\TEMPFILES\SGG_Final (cSggNo C(20),cInvNo c(15),cProdFty C(3),cGMTName C(75),cHTSCode C(25),YearMonth Integer,Months C(15),nInvQty N(10,2),;
dXFactory d(8),cGrnNumber C(12),cIntimNo C(20),FactPrice Integer,cIntimNum C(20),GName C(75),TotGross integer,cFtyCD C(3),WHCode C(50), cFactory C(3))

And this is my table structure. The cFactory filed is not updating when I run the code.

 

[AndrewMozley]

I may not have understood the requirement fully, but if you want to to process all the records in your table, you might want to have code which says something like :
. . . .

SELECT SGG_Final
SCAN
   IF ALLT(cProdFty) $ ‘SFC/VTM/SGL’
      REPLACE cFactory WITH ‘SGL’
     ELSE
      REPLACE cFactory WITH cProdFty
      ENDIF
   ENDSCAN

That is, the SCAN command processes the records in a table you have already selected. As originally written, I believe your code will only be applied to whichever record in SGG_Final was the current record when you started executing this block of code.

Hope this helps - Andrew

 

[Niki_S]

I have already used this. But the code is not working.
I have a code like this,

prodFty    WHCode     Qty      Fcty
SFC        1234D      456      
VTM        654F       12    
SGL        652Y       87    
FMF        1654I      120
FMA        875P       45

And I want it to be like this.

prodFty    WHCode     Qty      Fcty
SFC        1234D      456      SGL  
VTM        654F       12       SGL
SGL        652Y       87       SGL
FMF        1654I      120      FMF
FMA        875P       45       FMA

 

[Niki_S]

This is also not working Mike Yearwood. I can't think how to do this!!!!
Is there have any other way to do this please?

 

[AndrewMozley]

Hello Niki - sorry that this is not working on your machine.

I take it that the names of the fields in your table (file) SGG_Final are now prodFTy, WHCode, Qty and Fcty.

Have created this test program; it uses a cursor rather than a file on disk, but the operation of the program is similar to what you need :

CREATE CURSOR SGG_Final (prodFty C(6), WHCode C(6), Qty N(4), Fcty C(6))
INSERT INTO SGG_Final VALUES ('SFC', '1234D', 456,'')
INSERT INTO SGG_Final VALUES ('VTM', '654F',  12, '')
INSERT INTO SGG_Final VALUES ('SGL', '652Y',  87, '')
INSERT INTO SGG_Final VALUES ('FMF', '1654I', 120,'')
INSERT INTO SGG_Final VALUES ('FMA', '875P',  45, '')
BROWSE

SELECT SGG_Final
SCAN
   IF ALLTRIM(prodFty) $ 'SFC/VTM/SGL'
      REPLACE Fcty WITH 'SGL'
     ELSE
      REPLACE Fcty WITH ProdFty
      ENDIF
   ENDSCAN
BROWSE

If you run this program you should see this

I take your point Mike - but was just trying to get Niki's original code to work!

 

[Niki_S]

Thank you Andrew... It's working now.
Can you please tell me This?
I used this code to replace records to my table. And my table is SGG_Final.

SELECT SGG_Final
SCAN
   IF ALLTRIM(prodFty) $ 'SFC/VTM/SGL'
      REPLACE Fcty WITH 'SGL'
     ELSE
      REPLACE Fcty WITH ProdFty
      ENDIF
   ENDSCAN

I used this to replace records to my table. But it is not working.
But I tried it by making a cursor. At that time this is working.
Can you please tell me how to replace records to table? Ia there have error in my code when I'm selecting the table?
Thank you

 

[AndrewMozley]

Hello again Niki

I think the problem may be with the SELECT statement. This is being treated as the Visual Foxpro SELECT, rather than the SELECT-SQL with which I believe you are more familiar.

I am sure that other people can explain better than me !

The VFP SELECT command assumes that you have already opened a file (probably with the
USE <filename> [AS <ALIAS>]command.

The <ALIAS> clause is optional. If it is not included you refer to the table later (e.g with a SELECT statement) using the filename you specified. But the original filename might have been a long pathname - for example :

USE C:\MYPROJECT\DATA\SGG _FINAL

.. . and in that case you could then have said

USE C:\MYPROJECT\DATA\SGG _FINAL AS Stock

And later

SELECT Stock
SCAN . . etc.

The reason that it there are two separate commands is that most applications will have several tables (files) open, and will refer to them with their AlISASes as the occasion demands.

Perhaps another member of this forum could provide a link to explaining the use of the VFP SELECT command. . . .

In the present case you would have had to have written

USE SGG_FINAL
SELECT SGG_FINAL

 

[Niki_S]

I did as you said.

USE C:\MYPROJECT\DATA\SGG _FINAL AS Stock

When I used this they said that this is an unrecognized phrase.
Why is that?

 

[AndrewMozley]

My mistake - I had left a space in the filename, It should have been (as an example)

USE C:\MYPROJECT\DATA\SGG_FINAL AS Stock

But this was only an example; you will have to specify the path name where your data file is - I don't suppose that you have a folder C:\MYPROJECT\DATA - that was just an example ! If your data file is in the current folder, then the command USE SGG_FINAL would have worked.

But if your data file is in some other format, not in a standard Foxpro .dbf file, this will not work.

Can anyone else help Niki? Is the data perhaps not being held in a standard VFP data file?

 

[Niki_S]

Now it is okay Andrew.....
I did it the way that you previously told me. Thank you and others for the support.

I have another one two ask....
If you can please tell me how to combine variables like this,

IF............
variable1+variable2+variable3
etc.

Thank you

 

[AndrewMozley]

The short answer is that you write an expression like e.g.

X = (variable1 + variable2) * 3 + 12.345

Or

cString2 = cString1 + “xyz”

Or – if you are putting the resultant value into a field of the current data file (or cursor) you are working with :

REPLACE cField WITH (cString1 + 2)

However I see that some of the code in your examples is not VFP code, although it does have similarity. Maybe you would like to learn how to use Visual Foxpro, starting from scratch? I take it that you do have Visual Foxpro installed on your computer.

Perhaps other members of this site (Mike Lewis? Tamar?) could suggest a user’s guide to help you with the basics. I take it that at this stage you are not wanting to get involved with other important features of the language – like the creation of forms on the screen, building a class library &c; at present you perhaps just wish to process data files, maybe update them and perhaps produce reports.

Best wishes. Andrew

 

[Niki_S]

I'm a student and I'm new with foxpro. Still I'm learning this.
Anyhow Thank you for the supports.

SELECT SGG
SCAN 
		if (nInvToFob)=0 then
	(_Fabric + _TrimNPD + _TrimPD + _ProfCent + _CAM)
	else
	IF isnull(cSggNo) then
	
	 replace nValue with _FOBPrice  

		ELSE
		   replace nValue with _FactPrice 
		ENDIF 
	ENDSCAN

According to the above code my variables are _Fabric,_TrimNPD,_TrimPD,_ProfCent,_CAM,_FOBPrice ,_FactPrice . Here I need to add _Fabric,_TrimNPD,_TrimPD,_ProfCent,_CAM in my 1st row. When I'm using this way they said that this is an Unrecognized command verb. Can you please tell me how to do this?
Thnk you.

 

[AndrewMozley]

Hello Niki

If the code you provided is a VFP program (.prg) and you ask VFP to compile it. It will indeed fail, highlighting the 4th line of your program - the one beginning (_Fabric, with this message :

Unrecognised command verb

This is because this line is not a valid VFP instruction. It is an expression – either adding these 5 expressions if they are all numeric, or concatenating them (placing them one after the other in a single string) if they are character variables. But if you want to make it into a valid instruction it must be part of a a statement – perhaps :

cNewvariable = 	(_Fabric + _TrimNPD + _TrimPD + _ProfCent + _CAM)

What is this line of your program intended to do?

Also a few points.

1. When you run this program, unless it is part of a larger program, or you have already opened a data file called SCG (or with ALIAS SCG), it will fail, because Foxpro will not know how to SELECT that work-area.

2. Are you certain that you wish to use the ISNULL() statement? As far as I know the simplest way to give a variable a null value is with the statement Variable = .NULL. You are unlikely to have retrieved such a value from a field in one of your data files.

3. Are you trying to process each record in your SCG file (I think that is the ALIAS of your SGG_Final.dbf file) - to put a value (the sum of 5 other fields in that record) into one of its fields? And is nValue a field within that data file - because your REPLACE command implies that it is.

 

[Niki_S]

cNewvariable = 	(_Fabric + _TrimNPD + _TrimPD + _ProfCent + _CAM)

In this line I need to update my record field as the sum of these variables.

I have a cursor named SGG and already there have too many records. What I want is, I want to update my new cursor [SGG_grn.nValue] field by using SGG cursor fields. For that I have already created variables. And SGG_grn have a colmn named nValue. In this area I need to update nValue.

SELECT SGG
 SCAN
 
		if (nInvToFob)=0 then
	
	_Value = (_Fabric + _TrimNPD + _TrimPD + _ProfCent + _CAM)
	 replace SGG_grn.nValue with _Value 
	IF isnull(cSggNo) then
	
	 replace SGG_grn.nValue with _FOBPrice  

		ELSE
		   replace SGG_grn.nValue with _FactPrice 
		ENDIF 
		ENDIF
		
	ENDSCAN

I did it as below. But the records are not updating.

 

[Chris Miller]

You scan in SGG, but replace in SGG_grm. You only replace in one record of SGG_grn, as you don't move in that table/cursor.

Besides, yuo could format your code better readable. Rightclick on the editor window, then pick "Beatify".

Chriss

 

[Niki_S]

Yeah I got the points you said. I tried this in two ways. But the records are not still updating.

select SGG_grn
REPLACE SGG_grn.nValue with _Fabric + _TrimNPD + _TrimPD + _ProfCent + _CAM FOR SGG_grn.nInvToFob=0
REPLACE SGG_grn.nValue with _FobPrice for isnull(SGG_grn.cSggNo)
REPLACE SGG_grn.nValue with _FactPrice for  !isnull(SGG_grn.cSggNo)

SELECT SGG_grn
SCAN

	IF (nInvToFob)=0 THEN
		SELECT SGG_grn
	 		replace nValue with _Value
	  
	IF isnull(cSggNo) THEN 
		SELECT SGG_grn	
	 		replace nValue with _FOBPrice  

	ELSE
		SELECT SGG_grn
		   replace nValue with _FactPrice 
	ENDIF 
	ENDIF
		
ENDSCAN

Can anyone please tell me what are the errors and how should I fix them.
Thank you