One to Many Form

You can share your experience with HMG. Share with some screenshots/project details so that others will also be benefited.

Moderator: Rathinagiri

User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

One to Many Form

Post by sudip »

Hi,

I just created my FIRST One to Many Form with HMG.

It is a very basic sale invoice entry form, with 2 combo boxes (one for customer another for Item in grid). It can auto calculate sale invoice amount.

I shall incomporate AddNew, Delete, Save, Print etc later.

Please check and tell me how to improve. I need your advice very much to learn HMG :)

I couldn't write this program without your active support. :)

Code: Select all


#include "minigui.ch"

static aGrid := {}, aCustSource := {}, aCustDisp := {}, ;
   aItemSource := {}, aItemDisp := {}, aItemRate := {}, lChanged := .f.

Function Main
   local i, gb
   
   gb := {|| CalcAmt()}
   
   CreateTable()
   CreateLookup()

   DEFINE WINDOW Form_1 ;
      AT 0,0 ;
      WIDTH 640 ;
      HEIGHT 460 ;
      TITLE "One To Many Form" ;
      MAIN ;
      on init RefreshWin()

      @ 10, 10 label lblInvno value "Invoice No.:" 
      @ 10, 100 textbox txtInvno ;
         readonly 

      @ 10, 300 label lblInvdt value "Invoice Dt.:" 
      @ 10, 400 datepicker dpkInvdt



      @ 40, 10 label lblCustcd value "Customer:"
      @ 40, 100 COMBOBOX cboCustcd ;
         items aCustDisp ;
         width 200 
         
      @ 70,10 GRID grdSaledtl ;
      WIDTH 500 ;
      HEIGHT 210 ;
      HEADERS {'Item', 'Rate', 'Qty', 'Amt'} ;
      WIDTHS {200,100,100,100} ;
      COLUMNCONTROLS { {'COMBOBOX', aItemDisp}, ;
         {'TEXTBOX', 'NUMERIC', '999999.99'}, ;
         {'TEXTBOX', 'NUMERIC', '999999'}, ;
         {'TEXTBOX', 'NUMERIC', '99999999.99'}} ;
      edit ;
      on lostfocus calcamt()

      @ 330, 300 label lblTotamt value "Total:"
      @ 330, 400 textbox txtTotamt ;
         readonly ;
         numeric ;
         inputmask "9999999.99" 
         
      @ 360, 10 button cmdPrev caption "&Previous" ;
         action MovePrev()
         
      @ 360, 120 button cmdNext caption "&Next" ;
         action MoveNext()
      
   END WINDOW

   CENTER WINDOW Form_1

   ACTIVATE WINDOW Form_1

Return


FUNCTION CreateLookup()
   local i
   
   aItemSource := {}
   aItemDisp := {}
   aItemRate := {}
   aCustSource := {}
   aCustDisp := {}
   
   select item
   do while !eof()
      aadd(aItemSource, item->itemcd)
      aadd(aItemDisp, item->itemnm)
      aadd(aItemRate, item->rate)
      skip
   enddo
   
   select cust
   do while !eof()
      aadd(aCustSource, cust->custcd)
      aadd(aCustDisp, cust->custnm)
      skip
   enddo
   
   return nil
   
   
FUNCTION RefreshWin()
   form_1.txtInvno.value := sale->invno
   form_1.dpkInvdt.value := sale->invdt
   form_1.cboCustcd.value := ascan(aCustSource, {|x| x == sale->custcd})
   form_1.txtTotamt.value := sale->totamt
      
   aGrid := {}
   select saledtl
   locate for invno = sale->invno
   do while !eof()
      aadd(aGrid, {ascan(aItemSource, {|x| x == saledtl->itemcd}), ;
         saledtl->rate, saledtl->qty, saledtl->rate*saledtl->qty})
      continue
   enddo
   form_1.grdSaledtl.DeleteAllItems()
   for i = 1 to len(aGrid)
      form_1.grdSaledtl.additem(aGrid[i])
   next
   return nil
   
   
PROCEDURE CalcAmt()
   local i, nIctr, aTemp := {}, mTotAmt := 0.00
   // lChange := .t.
   i := form_1.grdSaledtl.value
   nIctr :=  form_1.grdSaledtl.cell(i, 1)
   aTemp := form_1.grdSaledtl.item(i)
   if i < 1
      return
   endif
   aTemp[2] := iif(nIctr>0 .and. nIctr<=len(aItemRate), aItemRate[nIctr], 0.00)
   aTemp[4] := aTemp[2]*aTemp[3]
   form_1.grdSaledtl.item(i) := aTemp

   for i = 1 to form_1.grdSaledtl.Itemcount
      mTotAmt += form_1.grdSaledtl.cell(i, 4)
   next
   form_1.txtTotamt.value := mTotAmt
   return


PROCEDURE MovePrev()
   select sale
   if !bof()
      skip -1
   endif
   if bof()
      go top
   endif
   RefreshWin()
   

PROCEDURE MoveNext()
   select sale
   if !eof()
      skip 
   endif
   if eof()
      go bottom
   endif
   RefreshWin()




FUNCTION CreateTable()
   local aDbf := {}
   
   if !file("item.dbf")
      aDbf := {}
      aadd(adbf,   {"itemcd",   "c",   10,   0})
      aadd(adbf,   {"itemnm",   "c",   20,   0})
      aadd(adbf,   {"rate",     "n",    8,   2})
      dbcreate("item", adbf)
      use item new
      
      select item
      
      append blank      
      replace itemcd with "COMP"
      replace itemnm with "Computer"
      replace rate with 20000.00
      
      append blank
      replace itemcd with "CD"
      replace itemnm with "Compact Disk"
      replace rate with 10.00
      
      append blank
      replace itemcd with "OPM"
      replace itemnm with "Optical Mouse"
      replace rate with 400.00
      
      use
   endif


   if !file("cust.dbf")
      aDbf := {}
      aadd(adbf,   {"custcd",    "c",   10,   0})
      aadd(adbf,   {"custnm",   "c",   20,   0})
      dbcreate("cust", adbf)
      use cust new
      
      select cust
      
      append blank
      cust->custcd := "CUST1"
      cust->custnm := "Customer One"
      
      append blank
      cust->custcd := "CUST2"
      cust->custnm := "Customer Two"

      append blank
      cust->custcd := "CUST3"
      cust->custnm := "Customer Three"

      use
   endif

   if !file("sale.dbf")
      aDbf := {}
      aadd(adbf,   {"invno",   "c",   10,   0})
      aadd(adbf,   {"invdt",   "d",    8,   0})
      aadd(adbf,   {"custcd",  "c",   10,   0})
      aadd(adbf,   {"totamt",  "n",   10,   2})
      dbcreate("sale", adbf)
      use sale new
      
      select sale
      
      append blank
      sale->invno := str(1, 10)
      sale->invdt := date()
      sale->custcd := "CUST1"
      sale->totamt := 40200.00
      
      append blank
      sale->invno := str(2, 10)
      sale->invdt := date()
      sale->custcd := "CUST2"
      sale->totamt := 420.00
      
      use
   endif

   if !file("saledtl.dbf")
      aDbf := {}
      aadd(adbf,   {"invno",    "c",   10,   0})
      aadd(adbf,   {"itemcd",   "c",   10,   0})
      aadd(adbf,   {"rate",     "n",    8,   2})
      aadd(adbf,   {"qty",      "n",    6,   0})
      dbcreate("saledtl", adbf)
      use saledtl new
      
      select saledtl
      
      append blank
      saledtl->invno := str(1, 10)
      saledtl->itemcd := "COMP"
      saledtl->rate := 20000.00
      saledtl->qty := 2

      append blank
      saledtl->invno := str(1, 10)
      saledtl->itemcd := "CD"
      saledtl->rate := 10.00
      saledtl->qty := 20

      append blank
      saledtl->invno := str(2, 10)
      saledtl->itemcd := "CD"
      saledtl->rate := 10.00
      saledtl->qty := 5

      append blank
      saledtl->invno := str(2, 10)
      saledtl->itemcd := "OPM"
      saledtl->rate := 400.00
      saledtl->qty := 1
      
      use
   endif
   close databases
   use item new
   use cust new
   use sale new
   use saledtl new

   return nil

How to compile:-
1. Please copy the codes into a .prg file (e.g., demo.prg) in a folder.
2. Go to command prompt and change directory to that folder.
3. Run C:\HMG\BATCH\COMPILE demo
This will create an exe file. Please run the exe file (it will automatically create required tables)

With best regards.

Sudip
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: One to Many Form

Post by Rathinagiri »

Thank you. I will check and come back soon.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: One to Many Form

Post by Rathinagiri »

Yes. It is a nice example. Hats off.

I had added small insert and delete sequence once you press insert key and delete key. I had justified the grid too.

Code: Select all


    #include "minigui.ch"

    static aGrid := {}, aCustSource := {}, aCustDisp := {}, ;
       aItemSource := {}, aItemDisp := {}, aItemRate := {}, lChanged := .f.

    Function Main
       local i, gb
       
       gb := {|| CalcAmt()}
       
       CreateTable()
       CreateLookup()

       DEFINE WINDOW Form_1 ;
          AT 0,0 ;
          WIDTH 640 ;
          HEIGHT 460 ;
          TITLE "One To Many Form" ;
          MAIN ;
          on init RefreshWin()

          @ 10, 10 label lblInvno value "Invoice No.:"
          @ 10, 100 textbox txtInvno ;
             readonly

          @ 10, 300 label lblInvdt value "Invoice Dt.:"
          @ 10, 400 datepicker dpkInvdt



          @ 40, 10 label lblCustcd value "Customer:"
          @ 40, 100 COMBOBOX cboCustcd ;
             items aCustDisp ;
             width 200
             
          @ 70,10 GRID grdSaledtl ;
          WIDTH 550 ;
          HEIGHT 210 ;
          HEADERS {'Item', 'Rate', 'Qty', 'Amt'} ;
          WIDTHS {200,100,100,100} ;
          COLUMNCONTROLS { {'COMBOBOX', aItemDisp}, ;
             {'TEXTBOX', 'NUMERIC', '999999.99'}, ;
             {'TEXTBOX', 'NUMERIC', '999999'}, ;
             {'TEXTBOX', 'NUMERIC', '99999999.99'}} ;
          edit ;
          justify {0,1,1,1};
          on lostfocus calcamt()

          @ 330, 300 label lblTotamt value "Total:"
          @ 330, 400 textbox txtTotamt ;
             readonly ;
             numeric ;
             inputmask "9999999.99"
             
          @ 360, 10 button cmdPrev caption "&Previous" ;
             action MovePrev()
             
          @ 360, 120 button cmdNext caption "&Next" ;
             action MoveNext()
         
       END WINDOW
       on key DELETE of form_1 action dodel()
       on key INSERT of form_1 action doins()

       CENTER WINDOW Form_1

       ACTIVATE WINDOW Form_1

    Return


    FUNCTION CreateLookup()
       local i
       
       aItemSource := {}
       aItemDisp := {}
       aItemRate := {}
       aCustSource := {}
       aCustDisp := {}
       
       select item
       do while !eof()
          aadd(aItemSource, item->itemcd)
          aadd(aItemDisp, item->itemnm)
          aadd(aItemRate, item->rate)
          skip
       enddo
       
       select cust
       do while !eof()
          aadd(aCustSource, cust->custcd)
          aadd(aCustDisp, cust->custnm)
          skip
       enddo
       
       return nil
       
       
    FUNCTION RefreshWin()
       form_1.txtInvno.value := sale->invno
       form_1.dpkInvdt.value := sale->invdt
       form_1.cboCustcd.value := ascan(aCustSource, {|x| x == sale->custcd})
       form_1.txtTotamt.value := sale->totamt
         
       aGrid := {}
       select saledtl
       locate for invno = sale->invno
       do while !eof()
          aadd(aGrid, {ascan(aItemSource, {|x| x == saledtl->itemcd}), ;
             saledtl->rate, saledtl->qty, saledtl->rate*saledtl->qty})
          continue
       enddo
       form_1.grdSaledtl.DeleteAllItems()
       for i = 1 to len(aGrid)
          form_1.grdSaledtl.additem(aGrid[i])
       next
       return nil
       
       
    PROCEDURE CalcAmt()
       local i, nIctr, aTemp := {}, mTotAmt := 0.00
       // lChange := .t.
       i := form_1.grdSaledtl.value
       nIctr :=  form_1.grdSaledtl.cell(i, 1)
       aTemp := form_1.grdSaledtl.item(i)
       if i < 1
          return
       endif
       aTemp[2] := iif(nIctr>0 .and. nIctr<=len(aItemRate), aItemRate[nIctr], 0.00)
       aTemp[4] := aTemp[2]*aTemp[3]
       form_1.grdSaledtl.item(i) := aTemp

       for i = 1 to form_1.grdSaledtl.Itemcount
          mTotAmt += form_1.grdSaledtl.cell(i, 4)
       next
       form_1.txtTotamt.value := mTotAmt 
       return


    PROCEDURE MovePrev()
       select sale
       if !bof()
          skip -1
       endif
       if bof()
          go top
       endif
       RefreshWin()
       

    PROCEDURE MoveNext()
       select sale
       if !eof()
          skip
       endif
       if eof()
          go bottom
       endif
       RefreshWin()




    FUNCTION CreateTable()
       local aDbf := {}
       
       if !file("item.dbf")
          aDbf := {}
          aadd(adbf,   {"itemcd",   "c",   10,   0})
          aadd(adbf,   {"itemnm",   "c",   20,   0})
          aadd(adbf,   {"rate",     "n",    8,   2})
          dbcreate("item", adbf)
          use item new
         
          select item
         
          append blank     
          replace itemcd with "COMP"
          replace itemnm with "Computer"
          replace rate with 20000.00
         
          append blank
          replace itemcd with "CD"
          replace itemnm with "Compact Disk"
          replace rate with 10.00
         
          append blank
          replace itemcd with "OPM"
          replace itemnm with "Optical Mouse"
          replace rate with 400.00
         
          use
       endif


       if !file("cust.dbf")
          aDbf := {}
          aadd(adbf,   {"custcd",    "c",   10,   0})
          aadd(adbf,   {"custnm",   "c",   20,   0})
          dbcreate("cust", adbf)
          use cust new
         
          select cust
         
          append blank
          cust->custcd := "CUST1"
          cust->custnm := "Customer One"
         
          append blank
          cust->custcd := "CUST2"
          cust->custnm := "Customer Two"

          append blank
          cust->custcd := "CUST3"
          cust->custnm := "Customer Three"

          use
       endif

       if !file("sale.dbf")
          aDbf := {}
          aadd(adbf,   {"invno",   "c",   10,   0})
          aadd(adbf,   {"invdt",   "d",    8,   0})
          aadd(adbf,   {"custcd",  "c",   10,   0})
          aadd(adbf,   {"totamt",  "n",   10,   2})
          dbcreate("sale", adbf)
          use sale new
         
          select sale
         
          append blank
          sale->invno := str(1, 10)
          sale->invdt := date()
          sale->custcd := "CUST1"
          sale->totamt := 40200.00
         
          append blank
          sale->invno := str(2, 10)
          sale->invdt := date()
          sale->custcd := "CUST2"
          sale->totamt := 420.00
         
          use
       endif

       if !file("saledtl.dbf")
          aDbf := {}
          aadd(adbf,   {"invno",    "c",   10,   0})
          aadd(adbf,   {"itemcd",   "c",   10,   0})
          aadd(adbf,   {"rate",     "n",    8,   2})
          aadd(adbf,   {"qty",      "n",    6,   0})
          dbcreate("saledtl", adbf)
          use saledtl new
         
          select saledtl
         
          append blank
          saledtl->invno := str(1, 10)
          saledtl->itemcd := "COMP"
          saledtl->rate := 20000.00
          saledtl->qty := 2

          append blank
          saledtl->invno := str(1, 10)
          saledtl->itemcd := "CD"
          saledtl->rate := 10.00
          saledtl->qty := 20

          append blank
          saledtl->invno := str(2, 10)
          saledtl->itemcd := "CD"
          saledtl->rate := 10.00
          saledtl->qty := 5

          append blank
          saledtl->invno := str(2, 10)
          saledtl->itemcd := "OPM"
          saledtl->rate := 400.00
          saledtl->qty := 1
         
          use
       endif
       close databases
       use item new
       use cust new
       use sale new
       use saledtl new

       return nil


      function dodel
      local lineno := form_1.grdsaledtl.value
      if  lineno > 0
         form_1.grdsaledtl.deleteitem(lineno)
         if lineno > 1
            form_1.grdsaledtl.value := lineno - 1
         else
            if form_1.grdsaledtl.itemcount > 0
               form_1.grdsaledtl.value := 1
            endif
         endif
      endif
      calcamt()
      return nil
      
      function doins
       if form_1.grdSaledtl.cell(form_1.grdsaledtl.itemcount, 4) > 0.0
          form_1.grdsaledtl.additem({1,aitemrate[1],0,0.00})
       endif        
       form_1.grdSaledtl.value := form_1.grdsaledtl.itemcount
      return nil
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
Alex Gustow
Posts: 290
Joined: Thu Dec 04, 2008 1:05 pm
Location: Yekaterinburg, Russia
Contact:

Re: One to Many Form

Post by Alex Gustow »

Hi Sudip!
Good work for first steps! (I'm seriously - I'm novice in HMGing too)

Only one advice: number fields in browses/grids looks better when they are justify right. Something like:

Code: Select all

     @ 70,10 GRID grdSaledtl ;
      WIDTH 500 ;
      HEIGHT 210 ;
      HEADERS {'Item', 'Rate', 'Qty', 'Amt'} ;
      WIDTHS {200,100,100,100} ;
      COLUMNCONTROLS { {'COMBOBOX', aItemDisp}, ;
         {'TEXTBOX', 'NUMERIC', '999999.99'}, ;
         {'TEXTBOX', 'NUMERIC', '999999'}, ;
         {'TEXTBOX', 'NUMERIC', '99999999.99'}} ;
      edit ;
      JUSTIFY { BROWSE_JTFY_LEFT, BROWSE_JTFY_RIGHT, ;
               BROWSE_JTFY_RIGHT, BROWSE_JTFY_RIGHT } ;
      on lostfocus calcamt()
Because we haven't different justifies for data in column and header in browses/grids (we have it in TBrowse) - add some spaces in headers, something like:

Code: Select all

      HEADERS {'     Item', 'Rate     ', 'Qty     ', 'Amt     '} ;
and your grid will look more accurately.
Good work!

P.S. Hey, rathinagiri! We wrote about one thing (justifying) at the same time! :)
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: One to Many Form

Post by Rathinagiri »

Yep Alex.

Wise men think alike. ;)
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: One to Many Form

Post by sudip »

Hello Rathinagiri,

Thanks a lot! I was just waiting for your reply :)

I copied your program and compiled. It works just excellent !!!

I learned many things:-
1) Justification of Columns.
2) How to delete a row without moving the row pointer and checking no rows in grid.
3) How to add a row in a grid when amount > 0.00.

(Hope I have learned from the changes/additions you made) :)
Thank you again!

With best regards.

Sudip
With best regards,
Sudip
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: One to Many Form

Post by sudip »

Hello Alex,

Thank you very much for taking interest and guiding me. :)
I saw your posts but never met you. Now, I am very happy to meet you. :)
I changed my codes as per Rathi and your advice.
Hi Sudip!
Good work for first steps! (I'm seriously - I'm novice in HMGing too)

Because we haven't different justifies for data in column and header in browses/grids (we have it in TBrowse) - add some spaces in headers, something like:

Code:
HEADERS {' Item', 'Rate ', 'Qty ', 'Amt '} ;

and your grid will look more accurately.
Good work!
Thank you! I need your advice very much.

With best regards.

Sudip
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: One to Many Form

Post by Rathinagiri »

Thank you Sudip. We all sail in the same HMG boat. :)
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
Alex Gustow
Posts: 290
Joined: Thu Dec 04, 2008 1:05 pm
Location: Yekaterinburg, Russia
Contact:

Re: One to Many Form

Post by Alex Gustow »

Hi Sudip & Rathi!

"We all write progs with
Well-known age-am-dzhee!
Best-known age-am-dzhee!
Perfect age-am-dzhee!"

("age-am-dzhee" - pronounce of "HMG") :))

(sing on melody of "Yellow Submarine" - let's compose a hymn ! hey, lyrics-writers! come on!)
:))
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: One to Many Form

Post by Rathinagiri »

Nice rhythmic song Alex. Thanks a lot.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
Post Reply