Subject Re: Filter on 2 fields with lookup
From Mervyn Bick <invalid@invalid.invalid>
Date Thu, 28 Mar 2024 12:35:39 +0200
Newsgroups dbase.getting-started

On 2024/03/27 19:31, Dirk C wrote:

> Mervin,
> once selected a country, combow : sorteer and segemt are enabled = true
> and the grids appear for bedrijfstak,merken en producten and topics as
> date grid also
> segment combox filter o, kind of segment, for now is filtering by
> segment, i managed to filter by segment and country :
> the issui is when a segment in the segment combox doesn't appear in the
> Data in vraagbak.stammdat, filter stops to work
> i tried already to set Filter is NuLL or " "
> to bring rowset first()
>  ¬†when rowset first i controll all the data over all the countries
> i understand, if you, as i also read, more are involved with server based.
> thanks
> Dirk

I now have a better idea of what the program does.

When you assign a SELECT statement to a field's lookupSQL property you
don't need to add an ORDER BY clause.

   with (this.VRAAGBAK_STAMMDAT.rowset)
       with (fields["Province"])
          lookupSQL = "select areacode,area from euroregions"
       with (fields["Country"])
          lookupSQL = "select landid,land from  country"
       with (fields["soort"])
          lookupSQL = "select * from segm"
       with (fields["outside"])
          lookupSQL = "select * from employee"
       autoEdit = false

Make the value selected in the KEUZEDOOZ_LAND combobox available to the
onChange event handler of the KEUZEDOOZ_SOORTEN combobox by saving the
value in a user-defined property of the form whenever the value in
KEUZEDOOS_LAND changes.  Do the same for the value selected in the
KEUZEDOOS_SOORTEN combobox.  The filter based on the two fields can now
be activated from either onChange event handler when the value in either
combobox is changed.

The following code is untested so it may require further work.  Watch
for line-wrap.  I assume the country in KEUZENDOOZ_LAND is always
selected before the value in KEUZENDOOZ_SOORTEN.  If this is not the
case you may need to add tests in he KEUZENDOOS_SOORTEN onChange event
handler to deal with this.  I also assume that there is a record in the
table segm containing the value "(selecteren)" which the user can select.

Function keuzeDOOS_LAND_onChange
    local cLand
// = 2 // This is already in the constructor code
    Class :: Veldenaanpassen()
    cLand = form.vraagbak_stammdat.rowset.fields['country'].lookuprowset
    cLand.applyLocate("land = '" +this.value+"'")
    //Make landCode value available elsewhere in form
    form.cLandcode = (cland.fields[1].value)
    if form.KEUZEDOOS_SOORTEN.value = '(selecteren)' //No value selected
      form.rowset.Filter = [country =']+form.cLandcode+[']
      form.rowset.Filter = [country =']+form.cLandcode+[' and soort

Function KEUZEDOOS_SOORTEN_onChange()
    local cSegm, cMomentFilter
// = 2 //This is already in the constructor code
    cSegm = form.vraagbak_stammdat.rowset.fields['soort'].lookuprowset
    cSegm.applyLocate([description = '] +this.value+[']) // description
    //Make descriptionCode value available elsewhere in form
    form.cS = (cSegm.fields[1].value)
    if form.KEUZEDOOS_SOORTEN.value = '(selecteren)'
      form.rowset.filter = form.rowset.Filter = [country =
      form.rowset.Filter :=[soort = ']+form.cS+[' and country = ']