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.

 

[AndrewMozley]

Hello again Niki.

So that we can help you, please could you give us a list of the fields which are present in your data file SGG which you are processing in the second example of code you supplied. Mike Yearwood has made a similar request!

And please could you tell us what instruction you are using to open (USE) the data file SGG which you are SELECTing in the first line of this code.

And what is the value of the variable nInvtoFob at the beginning of this section of code. If this variable has not been defined (and hence has no value) you will get a program error when you try to run this program. And if this variable ninvtoFob is non-zero, none of the assignmnents (REPLACE nvalue . . .) will be executed - for any record in your SGG data file.

 

[Chris Miller]

Your IFs are nested, beatify makes this of your code:

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

Now it should be easy to see what Andrew said, all your REPLACE will only happen when nInvToFob is 0. Also, depending on cSggNo being NULL or not, what ends up in nValue is either _FOBPrice or _FactPrice. Your initial REPLACE with _Value is always overwritten.

Also, your code is now SELECTing SGG_grn at a lot of places. When you don't SELECT another workarea in any not posted code lines, you only need to select it once. Each ENDSCAN will also automatically skip in SGG_grn to the next record, if you didn't reach the end, so even when you would SELECT some other workarea within SCAN..ENDSCAN directly after SCAN at the begin of the scanloop code body, the scanned workarea is activated.

Chriss

 

[Niki_S]

Thank You all.... I resolved it.
I have another one to ask... Really sorry for the inconvenience.
Some of my variables there haven;t values that I want. It shows as 0.

SELECT SGG_grn 
_TrimNPD =  round((nInvQty)*((nTrimNPD)/12),2)


SELECT SGG_grn 
_TrimPD = round((nInvQty)*((nTrimPD)/12),2)

SELECT SGG_grn 
_lnvdate = Date(2010,5,1)

SELECT SGG_grn 
If dInvDate<_lnvdate 
   _Fabric = round((nInvQty)*((nFabValD2)/12),2)
Else
   _Fabric = round((nInvQty)*((nFabValDoz)/12),2)
ENDIF

For these 3 variables I can't get values. Why is that?

 

[Chris Miller]

Do you locate or seek the record you want to read?
All numeric fields are 0 when you do this when SGG:grn is at it's end (EOF() is true).

In any other recor, well, nInvQty could be 0 or so low, that it rounds to zero. I'd look into the details of what data you actually get when you single step through your code, ie SUSPEND and use the debugger and the data session window to see what data you have in the fields you read.

Chriss

 

[Niki_S]

Thank you Chriss, I did it when SGG_grn is at the end. Now it is okay.
Thank you for helping me to do this.

 

[Chris Miller]

We've all been here, and I didn't have internet way back then.

Chriss

 

[Doug Lindauer]

Niki, Sounds like you have worked things out and you got a lot of good help but there are a couple of things that might be helpful for you as a student.

At one point someone posted that you do something like:
USE C:\MYPROJECT\DATA\SGG _FINAL AS Stock

That is incorrect and will generate an error. It should be:
USE C:\MYPROJECT\DATA\SGG _FINAL alias Stock

In a dBase (i.e. FoxPro) "use" statement, if you want to refer to the table by a different name, then you use the keyword "alias".
You might do this for several reasons, such as opening the table a second time. Then you would use an alias.
For example if you wanted to open a table named MYTABLE twice you would have to use an alias and use the "again" keyword. So you might say

use MYTABLE
select 0 && selects the next available work area
use MYTABLE again alias MYTABLE2

If you don't use the word "alias" the system assigns the table name as the alias. So the alias of the first instance is simply MYTABLE
The VFP (i.e. dBase) "select" word tells the system which work area is the default work area. This way you don't have to prefix a field name
with its alias when you're in that table's work area.

The "as" word is good in an SQL select statement (although you really don't need it there either.)
For example in an SQL statement involving 2 tables, MYTABLE and YOURTABLE, both having key fields named PRIMEKEY, and both having fields named FIELD1 and FIELD2 you could say

select m.FIELD1, m.FIELD2, y.FIELD1 as F1, y.FIELD2 as F2 from MYTABLE as M, YOURTABLE as Y where m.PRIMEKEY = y.PRIMEKEY     && this is an inner join using "where"
OR you can omit the "as" keyword like this
select m.FIELD1, m.FIELD2, y.FIELD1 F1, y.FIELD2 F2 from MYTABLE M, YOURTABLE Y where m.PRIMEKEY = y.PRIMEKEY

The result would be a query with these columns:
FIELD1    FIELD2     F1     F2
.....     ......    .....   .....

But you can't use the "as" keyword in a foxpro "use" statement.

One other thing I noticed was the use of the "then" keyword in an "if else endif" construct.
VFP does permit it but it's not a normal dBase language thing and VFP is a dBase language.
So shoot me, but I think one should avoid using unnecessary keywords. It just clutters up the source code.

Along those lines, some people are in the habit of using keyword abbreviations. dBase has always permitted that
but this is one of things where just because you CAN do it, doesn't mean that you SHOULD do it. In the early days of
8 bit computers, 64K of memory and 360K floppy disks that seemed important but it's far more important that your code
should be neat, clear, and with a consistent style standard and indentation practice. So I would recommend never to
abbreviate keywords. Clarity is paramount in source code, IMO.

 

[AndrewMozley]

You are quite correct Doug. My mistake. I should indeed have suggested USE . . . ALIAS . . .
(There are a lot of other options)

Thank you for pointing that out. Andrew

 

[Chris Miller]

That's worth its own question thread, isn't it? Don't clutter one question with new questions, especially when they are unrelated. You might need this for the same task, but techincally it's a completely different issue. You don't reach people who don't follow this thread as they can't or don'T want to contribute to your initial question, but they might have an interest and an answer, if you give your new thread a good title.

Chriss

 

[Niki_S]

Okay Chriss