| Subject | Re: data modules - calculated fields |
| From | Gaetano De Luisi <gaetanodd@hotmail.com> |
| Date | Mon, 06 Jul 2020 18:05:31 -0400 |
| Newsgroups | dbase.getting-started |
Thank you Mervyn, multiplying by 12 is not to have the hourly energy production but the average power production for each 5-minute interval. Each row in the table is a 5-minute interval reading of energy produced/used/exported. Thanks for the tip about DATE field names. I don't have a date field name but I do have a TIMESTAMP field name. Is that going to cause a similar issue? Is it best to call it something like ReadingTimeStamp? Since I may want to do some more sophisticated morphing with those fields at times, e.e. to add a flag based on a function rather than a simple calculation, would this structure be adequate for that? class energyDMDataModule of DATAMODULE this.ENERGYDATA1 = new QUERY(this) with (this.ENERGYDATA1) onOpen = class::ENERGYDATA1_ONOPEN left = 125.0 top = 45.0 width = 114.0 height = 133.0 sql = 'select *,12*energyProducedWh as hourlyWhEquivalent from "energydata.dbf" active = true endwith with (this.ENERGYDATA1.rowset) with (fields["<someFlagName>"]) beforeGetValue = {<some more sophisticated code or function>} endwith endwith function ENERGYDATA1_onOpen() f=new Field() f.fieldName:="someFlagName" f.readOnly:=true this.rowset.fields.add(f) return endclass Many thanks for your help! Gaetano. Mervyn Bick Wrote: > 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 > "energydata.dbf"' > active = true > endwith > > // this.rowset = this.energydata1.rowset //not needed. > // this.rowset.fields.add(f) > > > endclass > > > 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(). > > > Mervyn. > > > > > | |