I have created a form for a set with the following calculated fields defined in the form:
OpValue = if(previous("Client_id","Claclper")=Client_Id.and.previous("Acl_id","Claclper")=Acl_Id,previous("Cl_value","Claclper"),0)
OpValAcc = if(previous("Client_id","Claclper")=Client_Id.and.previous("Acl_id","Claclper")=Acl_Id,previous("Clvalaccr","Claclper"),0)
BaseRate = lookupn("C",EndOfPeriodID(Period_Id),"rate","baserate","Startdatex")
BaseAdj = lookupn("C",EndOfPeriodID(Period_Id),"rate","baseadj","Startdatex")
TCTRPC = if(lookupl("F",Trans->Txntyp_Id,"tctr","txntype","Txntyp_Id"),lookupn("f",Trans->Period_Id,"tctr","period","Period_Id"),0)
Deals = IF(LOOKUPL("F",Trans->TXNTYP_ID,"deal","txntype","Txntyp_Id")=.F.,0,IF(LOOKUPL("F",Trans->TXNTYP_ID,"deal_pos","txntype","txntyp_id")=.T.,1,-1))*Trans->Tot_Amt
Income = IF(LOOKUPL("F",Trans->TXNTYP_ID,"deal","txntype","Txntyp_Id")=.T.,0,Trans->TOT_AMT)*(1+calc->TCTRPC/100)
Weighting = IF(Trans->SET_DATE=CTOD(" ").OR.LOOKUPL("F",Trans->TXNTYP_ID,"deal","txntype","Txntyp_Id")=.F.,0,(LOOKUPD("F",Trans->PERIOD_ID,"enddate","period","period_id")-Trans->SET_DATE)/DAYSINPERIODID(Trans->PERIOD_ID))
TotDeals = total(calc->Deals,GRP->Return)
TotInc = total(calc->Income,GRP->Return)
WtDeal = calc->Deals*calc->Weighting
TotCash = total(trans->Tot_Amt,GRP->Return)
DaysInPeriod = DaysInPeriodID(Period_Id)
CashReturn = (calc->Baserate+calc->Baseadj)*calc->Daysinperiod/365
TotWtDeals = total(calc->Wtdeal,GRP->Return)
IsCash = lookupl("F",Acl_Id,"Cashreturn","assetcl","Class_Id")
NonCashReturn = if(calc->OpValue+calc->Opvalacc+calc->Totwtdeals=0,0,(Cl_Value+Clvalaccr-calc->Opvalue-calc->Opvalacc-calc->Totdeals+calc->Totinc)/(calc->Opvalue+calc->Opvalacc+calc->Totwtdeals))*100
Return = if(calc->Iscash,calc->Cashreturn,calc->Noncashreturn)
There seems to be a problem with the last 3 fields: when the form is in design mode and I click on Form/Calculated fields.. then click on the "NonCashReturn" line, I get a "Not an open index" error message. The "Return" field gives the following error message: "error: lookupl(F,"02",CASHRETURN,assetcl,Class_id) not an open index : Function 'if'.".
When I open the expression builder for these calc fields, they evaluate correctly, without any error messages; they also display correctly on the form.
I have compacted the database, rebuilt the indexes for assetcl.dbf etc. but the error messages won't go away. Although the form shows the correct values, I am worried about installing the database on the client's computer, in case it causes problems later.
Does anyone know what is causing this problem?
I am using build 4035/3028.
Thanks,
Tony
OpValue = if(previous("Client_id","Claclper")=Client_Id.and.previous("Acl_id","Claclper")=Acl_Id,previous("Cl_value","Claclper"),0)
OpValAcc = if(previous("Client_id","Claclper")=Client_Id.and.previous("Acl_id","Claclper")=Acl_Id,previous("Clvalaccr","Claclper"),0)
BaseRate = lookupn("C",EndOfPeriodID(Period_Id),"rate","baserate","Startdatex")
BaseAdj = lookupn("C",EndOfPeriodID(Period_Id),"rate","baseadj","Startdatex")
TCTRPC = if(lookupl("F",Trans->Txntyp_Id,"tctr","txntype","Txntyp_Id"),lookupn("f",Trans->Period_Id,"tctr","period","Period_Id"),0)
Deals = IF(LOOKUPL("F",Trans->TXNTYP_ID,"deal","txntype","Txntyp_Id")=.F.,0,IF(LOOKUPL("F",Trans->TXNTYP_ID,"deal_pos","txntype","txntyp_id")=.T.,1,-1))*Trans->Tot_Amt
Income = IF(LOOKUPL("F",Trans->TXNTYP_ID,"deal","txntype","Txntyp_Id")=.T.,0,Trans->TOT_AMT)*(1+calc->TCTRPC/100)
Weighting = IF(Trans->SET_DATE=CTOD(" ").OR.LOOKUPL("F",Trans->TXNTYP_ID,"deal","txntype","Txntyp_Id")=.F.,0,(LOOKUPD("F",Trans->PERIOD_ID,"enddate","period","period_id")-Trans->SET_DATE)/DAYSINPERIODID(Trans->PERIOD_ID))
TotDeals = total(calc->Deals,GRP->Return)
TotInc = total(calc->Income,GRP->Return)
WtDeal = calc->Deals*calc->Weighting
TotCash = total(trans->Tot_Amt,GRP->Return)
DaysInPeriod = DaysInPeriodID(Period_Id)
CashReturn = (calc->Baserate+calc->Baseadj)*calc->Daysinperiod/365
TotWtDeals = total(calc->Wtdeal,GRP->Return)
IsCash = lookupl("F",Acl_Id,"Cashreturn","assetcl","Class_Id")
NonCashReturn = if(calc->OpValue+calc->Opvalacc+calc->Totwtdeals=0,0,(Cl_Value+Clvalaccr-calc->Opvalue-calc->Opvalacc-calc->Totdeals+calc->Totinc)/(calc->Opvalue+calc->Opvalacc+calc->Totwtdeals))*100
Return = if(calc->Iscash,calc->Cashreturn,calc->Noncashreturn)
There seems to be a problem with the last 3 fields: when the form is in design mode and I click on Form/Calculated fields.. then click on the "NonCashReturn" line, I get a "Not an open index" error message. The "Return" field gives the following error message: "error: lookupl(F,"02",CASHRETURN,assetcl,Class_id) not an open index : Function 'if'.".
When I open the expression builder for these calc fields, they evaluate correctly, without any error messages; they also display correctly on the form.
I have compacted the database, rebuilt the indexes for assetcl.dbf etc. but the error messages won't go away. Although the form shows the correct values, I am worried about installing the database on the client's computer, in case it causes problems later.
Does anyone know what is causing this problem?
I am using build 4035/3028.
Thanks,
Tony
Comment