filtering combo box according to entered variable

[Ehab Samir Aziz]

I am filtering a combo box of cities related to the country and filtering the govereners according to cities entered but there is a value must be refreshed to make that filtering active in the recent run . The filter is active in the next run of the module. My question what is missing in my code to make the filtering affected in active run .
Here is the code :

Code: Select all  Expand view

   cFilter :=""
   @ 45,2 SAY "&Country" OF oDlg PIXEL
   @ 45,50 SAY ":" OF oDlg PIXEL
   @ 45,200 COMBOBOX oCbx VAR v_co_cocmbo ITEMS aBase(1,"cust",256,"cu_coun",cfilter) size 110,80 ;
   OF oDlg PIXEL;
   ON CHANGE (oGet:VarPut(PADR(v_co_cocmbo,30)),oGet:Refresh())
   @ 45,60 GET oGet VAR cu_coun OF oDlg PIXEL

   cFilter :="alltrim(upper("+"cust->cu_coun"+" ))" + '=="' + alltrim(upper(cu_coun)) + '"'




   @ 60,2 SAY "&City" OF oDlg PIXEL
   @ 60,50 SAY ":" OF oDlg PIXEL
   @ 60,200 COMBOBOX oCbx2 VAR v_ci_cocmbo ITEMS aBase(1,"cust",256,"cu_city",cfilter) size 110,80 ;
   OF oDlg PIXEL;
   ON CHANGE (oGet2:VarPut(PADR(v_ci_cocmbo,30)),oGet2:Refresh())
   @ 60,60 GET oGet2 VAR cu_city OF oDlg PIXEL

   cFilter :="alltrim(upper("+"cust->cu_city"+"))" + '=="' + alltrim(upper(cu_city)) + '"'
   



   @ 75,2 SAY "&Governer" OF oDlg PIXEL
   @ 75,50 SAY ":" OF oDlg PIXEL
   @ 75,200 COMBOBOX oCbx3 VAR v_go_cocmbo ITEMS aBase(1,"cust",256,"cu_gove",cFilter) size 110,80 ;
   OF oDlg PIXEL;
   ON CHANGE (oGet3:VarPut(PADR(v_go_cocmbo,30)),oGet3:Refresh())
   @ 75,60 GET oGet3 VAR cu_gove OF oDlg PIXEL


FUNCTION aBase(workarea,dbf_na,arr_size,fld_na,cFilter)
//-------------------------------------
LOCAL arr:={},nAreaActiv:=Select()
LOCAL y:=0
local bFilter:=""

aAdd(arr,space(arr_size)) //Put First element empty


select (workarea)
use (dbf_na)
SELE (dbf_na)
DBGOTOP()

set filter to &(cFilter)
DBGOTOP()
go top


WHILE!Eof()


y:=ASCAN(arr,(dbf_na)->&fld_na)                     
if y==0
aAdd(arr,(dbf_na)->&fld_na)
dbSkip()
else
dbSkip()
endif
ENDDO

SELE (nAreaActiv)

RETURN(arr)


[Enrico Maria Giordano]

Try using oCbx:SetItem() method.

EMG

[Ehab Samir Aziz]

I tried very hard to filter a combo box based on a get value but with no result . Recently I fixed my values from dbf and also I tried to filter cities belongs to their countries as following but do no work .

Code: Select all  Expand view


select 5
use coun
index on upper(coun->coun_name) to coun
database oCoun

   @ 45,2 SAY "&Country" OF oDlg PIXEL
   @ 45,50 SAY ":" OF oDlg PIXEL
   @ 45,180 dbcombo oCbx var v_co_cocmbo;
      alias oCoun:cAlias;
      size 100,200 pixel;
      itemfield "COUN_CODE";
      listfield "COUN_NAME";
      of oDlg;
      UPDATE

select 6
use city
index on upper(city->city_name) to city
database oCity
set filter to upper(city->coun_name)==upper(v_co_cocmbo)
city->(dbgotop())

   @ 60,2 SAY "&City" OF oDlg PIXEL
   @ 60,50 SAY ":" OF oDlg PIXEL
   @ 60,180 dbcombo oCbx2 var v_ci_cocmbo;
      alias oCity:cAlias;
      size 100,200 pixel;
      itemfield "CITY_CODE";
      listfield "CITY_NAME";
      of oDlg;
      UPDATE


[Enrico Maria Giordano]

You have to set the filter in the ON CHANGE clause of the first DBCOMBO and then refresh (or refill?) the second DBCOMBO.

EMG

[Ehab Samir Aziz]

Still now I can not state the correct syntax for set filter with dbCombo ? Could you help me more ?

[James Bott]

First, I don't understand why you have a GET in addition to the combobox. The combobox already contains a GET which in turn contains the selection of the user. I can't see any use for the additional GET containing the same information.

After a selection is made, you need to set a filter and then reload the next combobox. So for the first combobox you need something like the code below. I would not use ON CHANGE because that is going to reload the next combobox each time the selection is changed (when the combobox is scrolled it will reload for each record). You only want it reloaded after the control is exited, so I would use the VALID clause.

cFilter :=""
@ 45,2 SAY "&Country" OF oDlg PIXEL
@ 45,50 SAY ":" OF oDlg PIXEL
@ 45,200 COMBOBOX oCbx VAR v_co_cocmbo ITEMS aBase(1,"cust",256,"cu_coun",cfilter);
SIZE 110,80 ;
OF oDlg PIXEL;
VALID (reset( v_co_cocmbo, oCbx2, "cust" ), .t.)


function reset( cItem, oCbx, cFile )
local aItems:={}
local nArea:= select()
select 0
use (cFile)
set filter to cItem == cu_coun
go top
do while ! eof()
add(aItems,cu_coun)
skip
enddo
use
select( nArea )
oCbx:setItems( aItems )
oCbx:refresh()
return nil

[Ehab Samir Aziz]

Sir I tried the code I could not find the error,it does not work . Sir Let my explain again:

I am not filtering the combobox of countires (just no redundancy) (chosen firstly).

I am filtering the combobox of Cities in the cust file according to their countries(chosen secondly).

I am filtering the combobox of governers in the cust file according to their cities(chosen thirdly).

The file cust has all those data that may be in redundancy .

Code: Select all  Expand view



@ 45,2 SAY "&Country" OF oDlg PIXEL
@ 45,50 SAY ":" OF oDlg PIXEL
@ 45,200 COMBOBOX oCbx VAR v_co_cocmbo ITEMS aBase(1,"cust",256,"cu_coun");
SIZE 110,80 ;
OF oDlg PIXEL


@ 60,2 SAY "&City" OF oDlg PIXEL
@ 60,50 SAY ":" OF oDlg PIXEL
@ 60,200 COMBOBOX oCbx2 VAR v_ci_cocmbo ITEMS aBase(1,"cust",256,"cu_city") size 110,80 ;
OF oDlg PIXEL;
VALID (reset( v_ci_cocmbo, oCbx2, "cust",1,"cu_city","cu_coun" ), .t.)

   @ 75,2 SAY "&Governer" OF oDlg PIXEL
   @ 75,50 SAY ":" OF oDlg PIXEL
   @ 75,200 COMBOBOX oCbx3 VAR v_go_cocmbo ITEMS aBase(1,"cust",256,"cu_gove")  size 110,80 ;
   OF oDlg PIXEL;
VALID (reset( v_go_cocmbo, oCbx3, "cust",1,"cu_gove","cu_city" ), .t.)


FUNCTION aBase(workarea,dbf_na,arr_size,fld_na)
//-------------------------------------
LOCAL arr:={},nAreaActiv:=Select()
LOCAL y:=0
local bFilter:=""

aAdd(arr,space(arr_size)) //Put First element empty


select (workarea)
use (dbf_na)
SELE (dbf_na)
DBGOTOP()


WHILE!Eof()


y:=ASCAN(arr,(dbf_na)->&fld_na)                     
if y==0
aAdd(arr,(dbf_na)->&fld_na)
dbSkip()
else
dbSkip()
endif
ENDDO

SELE (nAreaActiv)

RETURN(arr)

function reset( cItem, oCbx, cFile ,workarea,fld_na,filtered_fld)
//--------------------------------------------------------------
local aItems:={}
local nArea:= select()
local y:=0
select (workarea)
use (cFile)
set filter to cItem == (workarea)->filtered_fld
go top
do while ! eof()
y:=ASCAN(aItems,(cFile)->&fld_na)
if y==0
aAdd(aItems,(cFile)->&fld_na)
dbSkip()
else
dbSkip()
endif
enddo
use
select( nArea )
oCbx:setItems( aItems )
oCbx:refresh()
return nil

[James Bott]

Please explain what you mean when you say "it does not work." What, exactly is the problem?

I suggest a different approach than scanning the array for each record. If you index the file by the field being searched with the UNIQUE clause, then it will be much faster than scanning the array.

Ah, I just spotted a problem. You have the VALIDs on the wrong controls. You want the next control to be reset when the current control is exited (thus the VALID). So the first VALID resets the next control, and the second VALID resets the last control. They should be like this:

Code: Select all  Expand view

@ 45,2 SAY "&Country" OF oDlg PIXEL
@ 45,50 SAY ":" OF oDlg PIXEL
@ 45,200 COMBOBOX oCbx VAR v_co_cocmbo ITEMS aBase(1,"cust",256,"cu_coun");
SIZE 110,80 ;
OF oDlg PIXEL ;
VALID (reset( v_co_cocmbo, oCbx2, "cust",1,"cu_city","cu_coun" ), .t.)

@ 60,2 SAY "&City" OF oDlg PIXEL
@ 60,50 SAY ":" OF oDlg PIXEL
@ 60,200 COMBOBOX oCbx2 VAR v_ci_cocmbo ITEMS aBase(1,"cust",256,"cu_city") size 110,80 ;
OF oDlg PIXEL;
VALID (reset( v_ci_cocmbo, oCbx3, "cust",1,"cu_gove","cu_city" ), .t.)

   @ 75,2 SAY "&Governer" OF oDlg PIXEL
   @ 75,50 SAY ":" OF oDlg PIXEL
   @ 75,200 COMBOBOX oCbx3 VAR v_go_cocmbo ITEMS aBase(1,"cust",256,"cu_gove")  size 110,80 ;
   OF oDlg PIXEL

There is also another problem. What if the user does all three selections, then goes back to the first, or second, and makes a change. All the following controls need to be reset to blank. Also, what if the user makes the country selection then tries to make a governor selection? Is the last combobox empty?

Here is another issue. What if the database is incomplete? When there are no records in the file, then there is nothing to choose from. I think you would have to keep the choices separate from the customer database. How is the user going to enter something that is not yet in the database?

All things to think about...

James

[James Bott]

Also, there is no need to pass a workarea. Select(0) gets you the next available workarea. Hardcoding workareas is something to be avoided. It is possible--I never do it.

James

[Ehab Samir Aziz]

"It does not work " Oka the country combobox have no problem . the city appeared for seconds and disappeared also the governers disappeared immediately . select (0) got error message no workarea.
Here down the code I used :

Code: Select all  Expand view


@ 45,2 SAY "&Country" OF oDlg PIXEL
@ 45,50 SAY ":" OF oDlg PIXEL
@ 45,200 COMBOBOX oCbx VAR v_co_cocmbo ITEMS aBase(1,"cust",256,"cu_coun");
SIZE 110,80 ;
OF oDlg PIXEL ;
VALID (reset( v_co_cocmbo, oCbx2, "cust","cu_city","cu_coun" ), .t.)

@ 60,2 SAY "&City" OF oDlg PIXEL
@ 60,50 SAY ":" OF oDlg PIXEL
@ 60,200 COMBOBOX oCbx2 VAR v_ci_cocmbo ITEMS aBase(1,"cust",256,"cu_city") size 110,80 ;
OF oDlg PIXEL;
VALID (reset( v_ci_cocmbo, oCbx3, "cust","cu_gove","cu_city" ), .t.)

@ 75,2 SAY "&Governer" OF oDlg PIXEL
@ 75,50 SAY ":" OF oDlg PIXEL
@ 75,200 COMBOBOX oCbx3 VAR v_go_cocmbo ITEMS aBase(1,"cust",256,"cu_gove")  size 110,80 ;
   OF oDlg PIXEL


function reset( cItem,oCbx,cFile,fld_na,filter_name)
//---------------------------------------------------------

local aItems:={}
local nArea:= select()
local y:=0

*select (0)
use (cFile) new
set filter to cItem == filter_name
go top

do while ! eof()
y:=ASCAN(aItems,(cFile)->&fld_na)
if y==0
aAdd(aItems,(cFile)->&fld_na)
dbSkip()
else
dbSkip()
endif

ENDDO

use
select( nArea )
oCbx:setItems( aItems )
oCbx:refresh()
return nil