Subject Re: Combobox field list using DISTINCT
From Mervyn Bick <invalid@invalid.invalid>
Date Wed, 27 Apr 2016 04:13:04 +0200
Newsgroups dbase.getting-started

On 26/04/2016 22:33, edward racht wrote:
> I have a report form with several comboboxes used to select items to print in a report.  The report provides information from a query for a 'NOT EMPTY CE' field.
 >
 > I recently discovered that I have been doing some not so good programming and being satisfied.  NOW, I am not satisfied.
 >
 > I am using a datamodule.
 > I have the canGETROW selecting 'NOT EMPTY CE'
 >
 > What I was using in the combobox was the following
 > d = new DATABASE()
 > d.databaseName := "LMR"
 > d.active := true
 > q = new query()
 > q.database := d
 > // Remove duplicate field values
 > q.sql := 'select distinct MD from "rs"'
 > q.active := true
 > r = q.rowset
 >
 > When the lightbulb finally turned on I realized I wasn't using the benefits of the datamodule in the combobox.
 >
 > The following is where I am at this moment.
 > It runs but I get all the 'MD' field listed which is extensive.
 >
 > FUNCTION CBMD_onOpen
 > private a
 > local r
 > r = form.datamodref1.ref.c1.rowset
 > // Define the Array
 > aSource = new Array()
 > // Add a blank value
 > aSource.add ( "" )
 > r.first()
 > DO WHILE NOT r.endOfSet  // Process the table
 > // Add field value to new row of the Array
 > aSource.add( new string( r.fields[ "MD" ].value ).rightTrim() )
 > // Move to next row
 > r.next()
 > ENDDO
 > aSource.sort()
 > // Add Array to datasource
 > this.datasource = " Array aSource "
 > RETURN
 >
 > I am befuddled trying to learn how to get a combobox to provide a list of a  field 'MD' only once and for the field to be available only for the 'not empty CE' field.

Follow exactly the same route you took for the CE field.  Create a query
in the datamodule that has 'select distinct MD from tablename' in its
sql property.  If necessary add a where clause to get rid of records
with empty fields.

As you want to add a blank entry to the list in the combobox you need to
use an array instead of assigning the field direct to the combonox's
dataSource property.  Use the new DISTINCT rowset to build the array
rather than the main rowset. You shouldn't need to sort the array as
DISTINCT should do this automatically.  There shouldn't be any need to
right trim the entries.  aSource.add(r.fields[ "MD" ].value) should be
all you need.

Mervyn.