Subject Re: dataq modules - calculated fields
From Mervyn Bick <invalid@invalid.invalid>
Date Mon, 6 Jul 2020 12:07:02 +0200
Newsgroups dbase.getting-started

On 2020-07-06 10:17, Gaetano De Luisi wrote:
> Hey guys, it's been years since i wrote any code in dBase and I am getting stuck on some very basic stuff :(
> Not sure if it matters, but I use dBase Plus 11.
> I have a table that include energy outputs of my solar panels and I want to convert this to power values. essentially, with 5-minute interval energy readings, I just need to multiply the field value by 12 to convert that to a power reading.

> The part that sets the value of the calculated field has disappeared from the source  code editor but is still in the actual file. What am I doing wrong?
> Also, if the field I want to multiply by 12 is named "energyProducedWh", how would I reference it to replace the value "test" with [12*energyProducedWh]?

I don't know what is happening with your DMD but there is an easier way
of doing this.

Edit the .dmd file as follows

** END HEADER -- do not remove this line
// Generated on 06/07/2020
class energyDMDataModule of DATAMODULE
    this.ENERGYDATA1 = new QUERY(this)
    with (this.ENERGYDATA1)
       left = 125.0
       top = 45.0
       width = 114.0
       height = 173.0
       sql = 'select *,12*energyProducedWh as hourlyWhEquivalent from
       active = true

//   this.rowset = this.energydata1.rowset  //not needed.
//        this.rowset.fields.add(f)


The designer has hard coded the path to the .dbf file.  This is never a
good idea.  I remove it as a matter of course.  Hopefully, in the
fullness of time, dBASE will get round to omitting the path.

Multiplying the 5minute value by 12 will only give you an approximation
of the actual energy produced in an hour.  If you want the actual energy
produced you will need to sum the 5minute values.  Due to shortcomings
in localSQL as used in dBASE you will need to add separate fields for
year, month and day.

alter table energydata add yy int,add mm int, add dd int

To make sure these fields are populated execute the following in your
program each time before running the SQL statement below in a query.

If you have used DATE as a field name in your table this is going to
cause problems as DATE is a reserved word.  I suggest that you change it
to, say, ddate.

update energydata set yy = extract(year from yourdate)
update energydata set mm = extract(month from yourdate)
update energydata set dd = extract(day from yourdate)

sql = 'select yy,mm,dd '
sql += sum(energyProducedWh) as Wh '
sql += 'from energydata '
sql += 'group by yy,mm,dd'

If you want the output in kWh

sql += sum(energyProducedWh)/1000 as kWh '

This will give you a rowset containing the energy produced for each day.
  If you have missing records for some days use AVG(hourlyWhEquivalent)
instead of sum(energyProducedWh).  It won't be exact where records are
missing but it should be better than using sum().