try
   open database dbasetutorial
catch(exception e)
endtry
alter table :dbasetutorial:invoice add cName char(40) ,add cOrder char(10)
update :dbasetutorial:invoice set cName = invoice.'card name', cOrder = cast(invoice.'order date' as char(10)) 

//Cast will present the date as characters 'mm/dd/yyyy' irrespective of date format.
//The lines below will present the date as characters in the same format as the date.
//I use yyyy/mm/dd as the date format at so I would actually prefer to use dtos() instead of dtoc() for 
//the search field as this presents the character date as yyyymmdd without separators.

//Uncomment the lines below if you want a different format in the search field. This will overwite
//the existing mm/dd/yyyy values inserted by the update command.

//use :dbasetutorial:invoice
//replace all cOrder with dtoc(:order date:) 
// //note how to deal with spaces in fieldnames when using XDML 
//use
** END HEADER -- do not remove this line
//
// Generated on 2022-05-25
//
parameter bModal
local f
f = new like_search1Form()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class like_search1Form of FORM
   with (this)
      onOpen = class::FORM_ONOPEN
      onClose = class::FORM_ONCLOSE
      height = 17.5909
      left = 1.4286
      top = 0.4091
      width = 148.2857
      text = ""
   endwith

   this.DBASETUTORIAL1 = new DATABASE(this)
   with (this.DBASETUTORIAL1)
      left = 12.0
      width = 11.0
      height = 1.0
      databaseName = "DBASETUTORIAL"
      active = true
   endwith

   this.INVOICE1 = new QUERY(this)
   with (this.INVOICE1)
      left = 26.0
      width = 6.0
      height = 1.0
      database = form.dbasetutorial1
      sql = "select * from INVOICE.DBF where lower(cName) like :search and cOrder like :dsearch "
      params["search"] = "%"
      params["dsearch"] = "%"
   endwith

   this.GRID1 = new GRID(this)
   with (this.GRID1)
      dataLink = form.invoice1.rowset
      height = 8.6364
      left = 5.1429
      top = 3.0909
      width = 139.8571
   endwith

   this.RADIOBUTTON1 = new RADIOBUTTON(this)
   with (this.RADIOBUTTON1)
      height = 1.0909
      left = 11.4286
      top = 13.0
      width = 15.7143
      text = "Contains"
      group = true
      value = true
   endwith

   this.RADIOBUTTON2 = new RADIOBUTTON(this)
   with (this.RADIOBUTTON2)
      height = 1.0909
      left = 10.7143
      top = 15.4091
      width = 15.7143
      text = "Begins with"
   endwith

   this.ENTRYFIELD1 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD1)
      onGotFocus = class::ENTRYFIELD1_ONGOTFOCUS
      onKey = class::ENTRYFIELD1_ONKEY
      height = 1.0
      left = 38.5714
      top = 14.1818
      width = 17.4286
      value = " "
   endwith

   this.TEXTLABEL1 = new TEXTLABEL(this)
   with (this.TEXTLABEL1)
      height = 1.0
      left = 35.5714
      top = 13.0
      width = 23.5714
      text = "Search for (case insensitive)"
   endwith

   this.ENTRYFIELD2 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD2)
      onGotFocus = class::ENTRYFIELD2_ONGOTFOCUS
      onKey = class::ENTRYFIELD2_ONKEY
      height = 1.0
      left = 69.0
      top = 14.1818
      width = 19.0
      value = ""
   endwith

   this.TEXTLABEL2 = new TEXTLABEL(this)
   with (this.TEXTLABEL2)
      height = 1.0
      left = 69.0
      top = 13.0
      width = 21.0
      text = "Search for Order Date"
   endwith


   function ENTRYFIELD1_onGotFocus()
      form.entryfield2.value = ''
      return

   function ENTRYFIELD1_onKey(nChar, nPosition,bShift,bControl)
      ***if nChar = 13  //uncomment to prevent requery until Enter is pressed
         if form.radiobutton1.value = true
            form.INVOICE1.params['search'] = '%'+trim(lower(form.entryfield1.value))+'%'
				form.invoice1.params['dsearch'] = '%'
         else
            form.INVOICE1.params['search'] = trim(lower(form.entryfield1.value))+'%'
				form.invoice1.params['dsearch']  = '%' 
         endif
         if empty(form.entryfield1.value)
            form.INVOICE1.params['search'] = '%'
         endif
			 if empty(form.entryfield2.value)
            form.INVOICE1.params['dsearch'] = '%'
         endif
         form.INVOICE1.requery()
      ***endif
      return

   function ENTRYFIELD2_onGotFocus()
      form.entryfield1.value = ''
      return

   function ENTRYFIELD2_onKey(nChar, nPosition,bShift,bControl)
      ***if nChar = 13  //uncomment to prevent requery until Enter is pressed
         if form.radiobutton1.value = true
            form.INVOICE1.params['dsearch'] = '%'+trim(form.entryfield2.value)+'%'
				form.invoice1.params['search'] = '%'
         else
            form.INVOICE1.params['dsearch'] = trim(form.entryfield1.value)+'%'
				form.invoice1.params['search']  = '%' 
         endif
         if empty(form.entryfield1.value)
            form.INVOICE1.params['search'] = '%'
         endif
			 if empty(form.entryfield2.value)
            form.INVOICE1.params['dsearch'] = '%'
         endif
         form.INVOICE1.requery()
      ***endif      
      return

   function form_onClose()
      form.invoice1.active = false
      cmd = 'alter table invoice drop cName, drop cOrder'
      form.dbasetutorial1.executeSQL(cmd)
      return

   function form_onOpen()
	    form.invoice1.active = true  // making the query active here prevents error if query is active when form opens
       form.entryfield1.setfocus()
      return

endclass