VBA/Xapi Macro problem 1

[NEORAT06]

Hello,
We have a Macro that pulled some info from one of the optional fields and writes it to an Excel spreadsheet. We recently upgraded to Sage ERP Accpac 5.4 and Pervasive 9 only to discover they move the location of our optional fields. The macro use to use the following:

Set Loc = OpenView("IC0210","IC")
Set LocFields = Loc.Fields
sqftEA = LocFields.Item("OPTFLD4").Value

But that field no longer exists. The field now resides under the ICITEMO table as follows:

OPTFIELD = “SQUAREFT”
VALUE = “XX.XXX”

Does anyone know what view I must open to find the optional field and can you give me an example on how to pull data from it?

 

[jayconverse]

IC0313.

.Browse "ITEMNO = " & sItem & " AND OPTFIELD = SQUAREFT", True

Jay Converse
IT Director
Systemlink, Inc.

 

[NEORAT06]

Thanks, it works great now.

Any suggestions about hot to pull this info in Crystal Reports 7? I have made my reports "work" using subreports but, as I understand it, in order to pass information to the main report you have to use whileprintingrecords statement and sharing variables. This makes summarizing impossible using the normal command and using more variables is difficult.

I’d like to use SQL Expression Fields but can’t figure out the syntax. In the Pervasive Control Panel this works:

select ICITEMO.VALUE from "ICITEMO" where "ITEMNO" = 'C9908' and "OPTFIELD" = 'SQUAREFT'

but this

select ICITEMO.VALUE from "ICITEMO" where ICITEMO."ITEMNO" = ICITEM."ITEMNO" and ICITEMO."OPTFIELD" = 'SQUAREFT'

will not work in Crystal Reports 7’s SQL expression field maker. Do you know the correct syntax for this expression?

 

[jayconverse]

I never use Crystal's expression field maker. Just join the tables and put OPTFIELD="SQUAREFT" in the record selection formula.

Jay Converse
IT Director
Systemlink, Inc.