Creating variables and using IF condition Inside SCAN in foxpro99

[Niki_S]

I have a cursor in my foxpro named 'SGG_GRN' . Based on that I need to add these below conditions.

if {vInvFinal.nInvToFob}=0 then
{@Fabric}+{@TrimNPD}+{@TrimPD}+{@ProfCent}+{@CAM}
else
IF isnull({vInvFinal.cSggNo}) then
   {vInvFinal.nInvQty}*({vInvFinal.nFOBPrice}/12)
ELSE
   {vInvFinal.nInvQty}*({vInvFinal.nFactFobPrice}/12)


Fabric :
if {vInvFinal.dInvDate}<date('05/01/2010') then
round({vInvFinal.nInvQty}*({vInvFinal.nFabValDoz2}/12),2)
else
round({vInvFinaln.nInvQty}*({vInvFinal.nFabValDoz}/12),2)


TrimNPD :
round({vInvFinal.nInvQty}*({vInvFinal.nTrimNPD}/12),2)


TrimPD :
round({vInvFinal.nInvQty}*({vInvFinal.nTrimPD}/12),2)


ProfCent :
round({vInvFinal.nInvQty}*(({vInvFinal.nEmbCost}+{vInvFinal.nWshCost}+{vInvFinal.nOthOpCost})/12),2)


CAM :
IF {vInvFinal.cInSub}="SUB" THEN
    round(((round(({vInvFinal.nTotSMV}*12*{vInvFinal.nSubAdm})+({vInvFinal.nMintAdm}*12),2))/12)*{vInvFinal.nInvQty},2)
Else
    round((round({vInvFinal.nTotSMV}*{vInvFinal.nBillRate}*12,2)/12)*{vInvFinal.nInvQty},2)

How can I insert these into my code?
Thank you

 

[Steve Meyerson]

I'll start:

1. You don't need the "then" words.

2. You DO need an ENDIF for each IF.

3. The expressions need to be statements, e.g. fabric=round({vInvFinal.nInvQty}*({vInvFinal.nFabValDoz2}/12),2)
instead of the header Fabric :

4. After rewrite, suggest you run the code, log the errors & come back with specifics. Ok?

Steve

 

[Niki_S]

SELECT SGG_GRN
_TrimNPD =  round((vInvFinal.nInvQty)*((vInvFinal.nTrimNPD)/12),2)


SELECT SGG_GRN
_TrimPD = round((vInvFinal.nInvQty)*((vInvFinal.nTrimPD)/12),2)

SELECT SGG_GRN
_ProfCent = round((vInvFinal.nInvQty)*(((vInvFinal.nEmbCost)+(vInvFinal.nWshCost)+(vInvFinal.nOthOpCost))/12),2)

[b]SELECT SGG_GRN
_CAM = IIF((vInvFinal.cInSub)="SUB", round(((round(((vInvFinal.nTotSMV)*12*(vInvFinal.nSubAdm))+((vInvFinal.nMintAdm)*12),2))/12)*(vInvFinal.nInvQty),2), round((round((vInvFinal.nTotSMV)*(vInvFinal.nBillRate)*12,2)/12)*(vInvFinal.nInvQty),2))
[/b]
[b]SELECT SGG_GRN
_Fabric = IIF ((vInvFinal.dInvDate)<date('05/01/2010'),round((vInvFinal.nInvQty)*((vInvFinal.nFabValDoz2)/12),2), round((vInvFinaln.nInvQty)*((vInvFinal.nFabValDoz)/12),2))
[/b]

[b]if {vInvFinal.nInvToFob}=0 
(_Fabric)+(_TrimNPD)+(_TrimPD)+(_ProfCent)+(_CAM)
else
IF isnull((vInvFinal.cSggNo)) 
   (vInvFinal.nInvQty)*((vInvFinal.nFOBPrice)/12)
ELSE
   (vInvFinal.nInvQty)*((vInvFinal.nFactFobPrice)/12)
ENDIF 
ENDIF[/b]

I did it as below. And I don't understand how to get highlighted parts as variables.Can you please tell me are those correct or wrong?
And when I am taking my record into foxpro from sql I renamed some fields,

SUM (vInvFinal.nFabValDoz2) nFabValD2

sum (vInvFinal.nFabValDoz) as nFabValDoz

sum (vInvFinal.nTrimPD) as nTrimPD

like this. I want to know do I need to add these renamed fields when I am creating a variable?

And also

if {vInvFinal.nInvToFob}=0 
(_Fabric)+(_TrimNPD)+(_TrimPD)+(_ProfCent)+(_CAM)
else
IF isnull((vInvFinal.cSggNo)) 
   (vInvFinal.nInvQty)*((vInvFinal.nFOBPrice)/12)
ELSE
   (vInvFinal.nInvQty)*((vInvFinal.nFactFobPrice)/12)
ENDIF 
ENDIF

Here in this part I need to add this value to my table in foxpro. So how can I insert the value to my Value column in foxpro using above code?
Thank you.

 

[Chris Miller]

do I need to add these renamed fields when I am creating a variable?

Miki, just get the separation of MSSQL and VFP, VFP retrieves data coming from SQL Server into a VFP cursor with the names you gave them, if you use AS. How should VFP then know they once had different names in MSSQL? It's not coming over as additional information, especially not when the fields are naming an exrepssion like SUM(something). There is no original field having the sum of many records in it, so when you don't give it a name it would be called Expr_1, Epxpr_2 and so on. But any formular, even simpler ones like field1+field2, has to get a new name.
And even when you just rename a single field and SELECT [sql field] as vfpfield, for example to avoid unallowed chracters in VFP field names, you finally deal with what VFP has, the VFP cursor.

If you browse it you see the names you have, and VFP code postprocessing that data, well, will need to use these names. What else?

Chriss

 

[Niki_S]

Thank you Chriss. I got what you say.
I changed my code as below. And now I want to make some mistakes in my codes.

CAM :
IF {vInvFinal.cInSub}="SUB" THEN
    round(((round(({vInvFinal.nTotSMV}*12*{vInvFinal.nSubAdm})+({vInvFinal.nMintAdm}*12),2))/12)*{vInvFinal.nInvQty},2)
Else
    round((round({vInvFinal.nTotSMV}*{vInvFinal.nBillRate}*12,2)/12)*{vInvFinal.nInvQty},2)

I converted below code using above code.

SELECT SGG_GRN
_CAM = IIF((cInSub)="SUB", round(((round(((nTotSMV)*12*(nSubAdm))+((nMintAdm)*12),2))/12)*(nInvQty),2), round((round((nTotSMV)*(nBillRate)*12,2)/12)*(nInvQty),2))


Fabric :
if {vInvFinal.dInvDate}<date('05/01/2010') then
round({vInvFinal.nInvQty}*({vInvFinal.nFabValDoz2}/12),2)
else
round({vInvFinaln.nInvQty}*({vInvFinal.nFabValDoz}/12),2)

I converted this using above table,

SELECT SGG_GRN
_Fabric = IIF ((dInvDate)<date('05/01/2010'),round((nInvQty)*((nFabValD2)/12),2), round((nInvQty)*((nFabValDoz)/12),2))

I need to know are these correct.

And the second one is , I have a table in fopro with column named Value. Now I need to update my value column using below code.

SCAN
SELECT SGG_GRN
	if (nInvToFob)=0 then
[b](_Fabric)+(_TrimNPD)+(_TrimPD)+(_ProfCent)+(_CAM)[/b] ****here I need to know how to combine variables*****
	else
	IF isnull((cSggNo)) then
	   (nInvQty)*((nFOBPrice)/12)
	ELSE
	   (nInvQty)*((FactPrice)/12)
	ENDIF 
	ENDIF 
ENDSCAN

Can you please fix me this?
Thank you