Subject Re: how to calculate 2 records of the same column of a grid?
From Mervyn Bick <invalid@invalid.invalid>
Date Wed, 13 Jan 2021 08:25:17 +0200
Newsgroups dbase.getting-started
Attachment(s) dirkSample1.wfm

On 2021/01/13 01:06, Leslie Shewchuk wrote:
> Hello dirk.
 >
> Please try the attached and see if this does what you are looking for.
 >

I've slightly modified your example to show an alternative way to
display daily totals.

Mervyn.



** END HEADER -- do not remove this line
//
// Generated on 2021-01-13
//
parameter bModal
local f
f = new DirkSample1Form()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class DirkSample1Form of FORM
   with (this)
      onOpen = class::FORM_ONOPEN
      height = 18.2273
      left = -0.7143
      top = 1.0
      width = 92.2857
      text = ""
   endwith

   this.SAMPLE3 = new QUERY(this)
   with (this.SAMPLE3)
      left = 44.0
      top = 2.0
      width = 5.0
      height = 1.0
      sql = "select trandate,sum(amount)  from Sample group by trandate"
      active = true
   endwith

   this.SAMPLE1 = new QUERY(this)
   with (this.SAMPLE1)
      left = 40.0
      width = 5.0
      height = 1.0
      sql = 'select * from "Sample.dbf"'
      active = true
   endwith

   with (this.SAMPLE1.rowset)
      onNavigate = class::ROWSET_ONNAVIGATE
      onSave = class::ROWSET_ONSAVE
   endwith

   this.SAMPLE2 = new QUERY(this)
   with (this.SAMPLE2)
      left = 47.0
      width = 5.0
      height = 1.0
      sql = 'select * from "Sample.dbf"'
      active = true
   endwith

   this.CONTAINER1 = new CONTAINER(this)
   with (this.CONTAINER1)
      left = 0.0
      top = 13.1364
      width = 92.0
      height = 5.0
      anchor = 1        // Bottom
   endwith

   this.CONTAINER1.TEXT1 = new TEXT(this.CONTAINER1)
   with (this.CONTAINER1.TEXT1)
      height = 1.0
      left = 2.5714
      top = 0.7273
      width = 15.0
      border = true
      alignVertical = 1        // Middle
      text = "Text1"
      borderStyle = 4        // Single
      pageno = 0
   endwith

   this.CONTAINER1.TEXT2 = new TEXT(this.CONTAINER1)
   with (this.CONTAINER1.TEXT2)
      height = 1.0
      left = 51.1429
      top = 0.5455
      width = 15.0
      border = true
      alignVertical = 1        // Middle
      text = "Total"
      borderStyle = 4        // Single
      pageno = 0
   endwith

   this.CONTAINER1.ENTRYFIELD1 = new ENTRYFIELD(this.CONTAINER1)
   with (this.CONTAINER1.ENTRYFIELD1)
      height = 1.0
      left = 18.5714
      top = 0.7273
      width = 15.0
      value = "Entryfield1"
      pageno = 0
   endwith

   this.CONTAINER1.ENTRYFIELD2 = new ENTRYFIELD(this.CONTAINER1)
   with (this.CONTAINER1.ENTRYFIELD2)
      enabled = false
      height = 1.0
      left = 69.4286
      top = 0.5
      width = 15.0
      value = "Entryfield2"
      pageno = 0
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.sample1.rowset
      columns["COLUMN1"] = new GRIDCOLUMN(form.GRID1)
      with (columns["COLUMN1"])
         dataLink = form.sample1.rowset.fields["trandate"]
         editorType = 3        // SpinBox
         width = 17.1429
      endwith
      columns["COLUMN2"] = new GRIDCOLUMN(form.GRID1)
      with (columns["COLUMN2"])
         dataLink = form.sample1.rowset.fields["amount"]
         editorType = 3        // SpinBox
         width = 17.1429
      endwith
      with (columns["COLUMN1"].editorControl)
         rangeMax = {    -  -  }
         rangeMin = {    -  -  }
      endwith

      with (columns["COLUMN1"].headingControl)
         value = "TranDate"
      endwith

      with (columns["COLUMN2"].editorControl)
         rangeMax = 100
         rangeMin = 1
      endwith

      with (columns["COLUMN2"].headingControl)
         value = "Amount"
      endwith

      height = 12.5
      left = 0.0
      top = 0.0
      width = 42.0
   endwith

   this.GRID2 = new GRID(this)
   with (this.GRID2)
      dataLink = form.sample3.rowset
      columns["COLUMN1"] = new GRIDCOLUMN(form.GRID2)
      with (columns["COLUMN1"])
         dataLink = form.sample3.rowset.fields["trandate"]
         editorType = 3        // SpinBox
         width = 17.1429
      endwith
      columns["COLUMN2"] = new GRIDCOLUMN(form.GRID2)
      with (columns["COLUMN2"])
         dataLink = form.sample3.rowset.fields["sum of amount"]
         editorType = 3        // SpinBox
         width = 31.4286
      endwith
      with (columns["COLUMN1"].editorControl)
         rangeMax = {2021-04-11}
         rangeMin = {2021-01-01}
      endwith

      with (columns["COLUMN1"].headingControl)
         value = "trandate"
      endwith

      with (columns["COLUMN2"].editorControl)
         rangeMax = 100
         rangeMin = 1
      endwith

      with (columns["COLUMN2"].headingControl)
         value = "Daily total"
      endwith

      height = 12.5
      left = 46.2857
      top = 0.1364
      width = 41.4286
   endwith

   this.rowset = this.sample1.rowset

   function CalcTotal(frm)
      DayTotal = 0
                AllTotal = 0
                frm.sample2.rowset.refresh()
                if frm.sample2.rowset.first()
                        do
                                AllTotal += frm.sample2.rowset.fields["Amount"].value
                                if frm.sample2.rowset.fields["Trandate"].value = frm.sample1.rowset.fields["Trandate"].value
                                        Daytotal += frm.sample2.rowset.fields["Amount"].value
                                endif
                        Until .not.frm.sample2.rowset.next()
                endif
                
                frm.container1.text1.text = DtoC(form.sample1.rowset.fields["TranDate"].value)
                frm.container1.Entryfield1.value := daytotal
                Frm.container1.Entryfield2.value := alltotal
        return

   function form_onOpen()
                        form.calctotal(form)
   return

   function rowset_onNavigate(type, nRows)
                        this.parent.parent.calctotal(this.parent.parent)
   return

   function rowset_onSave()
                        this.parent.parent.calctotal(this.parent.parent)
   return

endclass