Subject Re: Filter on 2 fields with lookup
From Mervyn Bick <invalid@invalid.invalid>
Date Tue, 26 Mar 2024 17:46:49 +0200
Newsgroups dbase.getting-started

On 2024/03/26 00:05, Dirk C wrote:
> Hello,
> want to filter on 2 fields wirh a lookup field
> i am trying to put the first filter as a variable and using for the
> second filter
>   function KEUZEDOOS_SOORTEN_onChange()
>     local cSegm, cS, cMomentFilter
> = 2
>      cSegm = form.vraagbak_stammdat.rowset.fields['soort'].lookuprowset
>    //soort
>       cSegm.applyLocate("description = '" +this.value+"'")   // description
>        cS = (cSegm.fields[1].value)
>         form.rowset.Filter :=[soort =']+cS+[']
>         cMomentFilter= form.rowset.filter
>            cLand =
> form.vraagbak_stammdat.rowset.fields['country'].lookuprowset
>           cLand.applyLocate("land = '" +this.value+"'")
>            A = (cland.fields[1].value)
>           form.rowset.Filter := cMomentFilter + "country ='"+A+"'"
>    return

I'm afraid you will need to give a bit more detail as it's not clear
what you want to do.

If you assign a lookupRowset to the lookupRowset property of
form.vraagbak_stammdat.rowset.fields['soort'], its first field must
contain the same sort of content as the field "soort" usually a code or
abbreviation.  The name of the first field is not important.  If the
second field in the lookupRowset is named, say "description", dBASE will
replace the field "soort" in form.vraagbak_stammdat.rowset with the
field "description" and its contents.

As you navigate in form.vraagbak_stammdat.rowset dBASE will
automatically navigate the lookupRowset and you can interrogate the
fields from the 3rd field onward.  Fetching cSegm.fields[1].value gives
you the value that would have been in the field "soort".

Each field in form.vraagbak_stammdat.rowset has a lookupSQL and a
lookupRowset property.

I assume "soort" and "land" are both fields in
form.vraagbak_stammdat.rowset and contain codes or abbreviations.

Perhaps the following.

form.vraagbak_stammdat.rowset.fields['soort'].lookupSQL = 'select
desc_code,description from descriptions.dbf'

form.vraagbak_stammdat.rowset.fields['land'].lookupSQL = 'select
land_code,country from countries.dbf'

function KEUZEDOOS_SOORTEN_onChange()
    form.vraagbak_stammdat.rowset.applyLocate('description = '"
    //This will move the rowpointer to the first matching description.
    //It does not filter the rowset.
    //You now need code to retrieve a country name into a variable,
    //say cLand.  You can't use this combobox as it displays descriptions.
    //Now you can set the filter to show al records with the selected
description and country.
    form.rowset.Filter :=[description =']+this.value+[' and country =