Subject Re: dataq modules - calculated fields
From Gaetano De Luisi <gaetanodd@hotmail.com>
Date Mon, 06 Jul 2020 05:49:02 -0400
Newsgroups dbase.getting-started

Hi Akshat,

Thank you for your reply. Good tip about adding the calculated field directly in the SQL statement since this is a fixed field, this coding is shorter. I'll give it a try. Meanwhile I seem to have found a code that works with the designer.

Take care,
Gaetano.

Akshat Kapoor Wrote:

> Good Afternoon Gaetano,
> I have rarely used the datamodule designer so cannot assisst you on that
> front. I am in a habit of editing the datamodule in editor. I never open
> them in designer.
> I have attached a datamodule that I was using some time ago.
> How was it designed I do not remember.
> > So i threw the table in a DMD designer, went to the source editor and added the following code (the part about F= New Field() is what I added manually). this is opened and copied from Notepad to make sure I was looking at the actual file contents:I would request you to go through Ken's books. There is a description
> there of how to add fields using designer.
> Designer will delete everything that it does not recognise as its output.
>
> If you want the rowset to be editable then opt in for this route
>
> If a readonly rowset will serve your purpose then you can add field in
> the sql itself
>
> 'select *,12*energyProducedWh as power from
> "D:\Documents\dbase\SolarOctopus2\energydata.dbf"'
>
> This alteration will stick and designer will not delete it.
>
> Regards
> Akshat
>
>
> ** END HEADER -- do not remove this line
> //
> // Generated on 25.02.2017
> //
> class inveDATAMODULE of DATAMODULE
>    this.VATRATE = new QUERY(this)
>    with (this.VATRATE)
>       left = 138.0
>       top = 19.0
>                 database = mcompany.db
>       sql = 'select recno,tax,vat,sat from "vatrate.dbf"'
>                 requestlive = true
>       active = true
>    endwith
>
>    with (this.VATRATE.rowset)
>       indexName = "TAX"
>       autoNullFields = false
>    endwith
>
>    /*this.INV = new QUERY(this)
>    with (this.INV)
>       left = 446.0
>       top = 23.0
>       sql = 'Select * from "'+mcompany.direct+'\inv'+mcompany.fyear+'.dbf"'
>                 requestlive = true
>       active = true
>    endwith
>
>    with (this.INV.rowset)
>       indexName = "RECNO"
>       autoNullFields = false
>    endwith*/
>
>    this.INVE = new QUERY(this)
>    with (this.INVE)
>       onOpen = class::INVE_ONOPEN
>       left = 291.0
>       top = 22.0
>                 database = mcompany.db
>       sql = 'Select * from "inve'+mcompany.fyear+'.dbf"'
>                 //requestlive = false
>       active = true
>    endwith
>
>    with (this.INVE.rowset)
>       indexName = "RECNO"
>       with (fields["tax"])
>          lookupRowset = form.vatrate.rowset
>       endwith
>       with (fields["full"])
>          beforeGetValue = {||iif(this.parent["company"].value#null ,ltrim(this.parent["company"].value),"1") ;
>                         +iif(this.parent["item"].value#null , ltrim(this.parent["item"].value), "1" ) ;
>                         +iif(this.parent["descrip"].value#null , ltrim(this.parent["descrip"].value), "1" );
>                         +iif(this.parent["bcode"].value#null , ltrim(this.parent["bcode"].value), "1" )}
>       endwith
>                 with (fields["nett"])
>                         beforegetvalue = {||iif(this.parent["offerp"].value >0,this.parent["offerp"].value ,round(this.parent["mrp"].value*(1-this.parent["disc"].value/100),0))}
>                 endwith
>       autoNullFields = false
>    endwith
>
>    this.rapid = new QUERY(this)
>    with (this.rapid)
>       left = 251.0
>       top = 22.0
>                 database = mcompany.db
>       sql = 'SELECT rapid.key_st, rapid.company, rapid.item , rapid.descrip , inve.recno FROM "rapid" INNER JOIN "inve'+mcompany.fyear+'" inve ON (rapid.company=inve.Company AND rapid.item=inve.item AND rapid.descrip=inve.descrip)'
>                 requestlive = false
>                 //?sql
>       active = true
>    endwith
>
>    with (this.rapid.rowset)
>       autoNullFields = false
>    endwith
>
>         function inve_onOpen()
>                 g = new field()
>                 g.fieldname = "full"
>                 this.rowset.fields.add(g)
>                 g.beforegetvalue = {||iif(this.parent["company"].value#null ,ltrim(this.parent["company"].value),"1") ;
>                         +iif(this.parent["item"].value#null , ltrim(this.parent["item"].value), "1" ) ;
>                         +iif(this.parent["descrip"].value#null , ltrim(this.parent["descrip"].value), "1" );
>                         +iif(this.parent["bcode"].value#null , ltrim(this.parent["bcode"].value), "1" )}
>                 h = new field()
>                 h.fieldname = "nett"
>                 this.rowset.fields.add(h)
>                 h.beforegetvalue = {||iif(this.parent["offerp"].value >0,this.parent["offerp"].value ,round(this.parent["mrp"].value*(1-this.parent["disc"].value/100),0))}                        
>       return
>
> endclass
>