Combobox populated with data from a table 1

[SitesMasstec]

Hello colleagues!

In my form (bellow) I give the user the option to select a name from a table (names are showed in alphabetical order).

The user can filter the result of the report by a chosen name, or he/she can leave it blank. I achieved this by appending a blank record to the table.



For the text box that showns the names, these are some properties:

RowSource: curclientes.CNOME
RowSourceType: 6-Fields

Is there a way to let the Combobox to show the first item blank (without the need of appending a blank record to the table) ?

 

[Mike Lewis]

Try setting the combo's ListIndex to 0. I think that will do what you want.

Having said that, I would take a different approach. I would add a checkbox to the form, to let the user choose between filtering on a name or leaving the filter blank. The caption of the checkbox could be something like "Select names". If the user elects not to specify a filter, then disable the combo box.

I think this would be more intuitive than your approach, but that's just my personal opinion. You know your users better than I aod.

Mike

 

[Paul Gibson]

When it comes to Comboboxes/Drop-down lists so there are so many different ways to set them up and use them. It mostly comes down to preference of how you want your UI to look and operate.

Just setting them to directly use the "lookup table" or "data table" or whatever you are basing the filter on is by far one of the easiest ways but with that ease comes very limited flexibility. The VFP control doesn't natively provide a simple way to just set one or two properties on the control to have an item in the list that is obviously, to the user, the equivalent of a "No filter" option but it does provide options for you to add one yourself.

If I'm building a filter drop-down like you've described and the number of options isn't going to be in the multiple hundreds or thousands then one of my preferences is to use RowSourceType 3 - SQL Statement OR 2 - Alias and write my own query to pull out the items, in the format that I want to, and this provides easy ways of adding any kind of custom "empty" or "no filter" item into that solution.

For example, let say I'm using the Northwind Employees table as the source for my drop-down filter. I can run a query such as:

Select Cast("<All Employees>" AS C(40)) AS FullName ,Cast(0 As I) As EmployeeId From Employees Where EmployeeId = 1 Union Select Cast(Alltrim(LastName) + ", " + Alltrim(FirstName) AS C(40)) As Fullname ,EmployeeId From Employees Order By 1 Into Cursor ddEmployee

...and this will give me a list of all the employees, but with a <All Employees> item at the top of the list. The default item will have an ID of zero and all the rest of the items will have their correct Employee ID. This will allow me to code the logic on the UI to say if the value of the drop-down is greater than zero, apply the filter, otherwise ignore the filter.

NOTE: One less than ideal aspect of that particular query is that for the <All Employees> item in that SQL query, I had to code WHERE EmployeeId = 1, to ensure that I did get 1 and only 1 item. I did that because I'd looked at the Employees table and I knew it had a row where EmployeeId = 1 so it worked for my example but in an empty database or one where ID #1 doesn't exist for another reason, like it has been deleted due it the table not being a static lookup, that WHERE clause would not work to give you the desired item. To make that part always work all of the time you could pull using a table where you can guarantee a particular row exists, in most of my applications we do always have one "company" record or "instance" record that stores some configuration for the installation so we can use that but you can also use a concept that some people do in their databases and that is to have a simple numbers table or cursor. This can take a number of forms but in it's simplest terms it could just be a cursor with maybe just ten records in it where the IDs for each row are the numbers 1 to 10 and have it so your application can always call upon this table if it ever needs to. You can use this table/cursor for lots of useful things and one is for this type of solution:

Select Cast("<All Employees>" AS C(40)) AS FullName ,Cast(0 As I) As EmployeeId From MyNumber Where Id = 1 Union Select Cast(Alltrim(LastName) + ", " + Alltrim(FirstName) AS C(40)) As Fullname ,EmployeeId From Employees Order By 1 Into Cursor ddEmployee

That will create the <All Employees> item using the number cursor and union in the employees from the employees table.

Another option is to specifically use SELECT TOP 1 in that part that is creating the <All Employees> item but you'd still have to be sure the table you have in your FROM has at least 1 row.

In full SQL Server these hacks for that item are not required, in SQL Server these days (and for a long time) you can just do the equivalent of:

Select Cast("<All Employees>" AS C(40)) AS FullName ,Cast(0 As I) As EmployeeId Union Select Cast(Alltrim(LastName) + ", " + Alltrim(FirstName) AS C(40)) As Fullname ,EmployeeId From Employees Order By 1 Into Cursor ddEmployee

...ignoring the fact that some of that is VFP syntax, it's a bit of a mish mash, I'm just demoing that in other database engines you can remove the FROM and WHERE from the first part and it'll just create one row with the specified values. VFP requires a FROM and the FROM dictates how many rows in conjunction with WHERE and JOINs etc.

This means another potential solution is to pre-emptively create your own separate cursor/alias with the contents that you want in the drop down. For example you could write code in the LOAD event of your form to create a cursor, insert the "default" row with whatever values you want and then append in the rows for the filters. Still continuing with the Northwind Employees example:

Create Cursor MyFilter (FullName C(40), Id I)
Insert Into MyFilter (FullName, Id) VALUES ("<All Employees>", 0)
Insert Into MyFilter (FullName, Id) SELECT Alltrim(LastName) + ", " + Alltrim(FirstName) As Fullname, EmployeeId From Employees Order By 1

With the cursor created with the exact contents that you want, you can use RowSourceType 2 - Alias or 6 - Fields and the appropriate RowSource

Some of these solutions will leave cursors/aliases sat in the background so be mindful of that and, if necessary, tidy them up by closing them in the UNLOAD method of your form.

These are just a few suggestions, when it comes to combos and drop-downs there are no right answers just many alternative answers.

 

[igorsandler]

When it comes to Comboboxes/Drop-down lists so there are so many different ways to set them up and use them. It mostly comes down to preference of how you want your UI to look and operate.

Just setting them to directly use the "lookup table" or "data table" or whatever you are basing the filter on is by far one of the easiest ways but with that ease comes very limited flexibility. The VFP control doesn't natively provide a simple way to just set one or two properties on the control to have an item in the list that is obviously, to the user, the equivalent of a "No filter" option but it does provide options for you to add one yourself.

If I'm building a filter drop-down like you've described and the number of options isn't going to be in the multiple hundreds or thousands then one of my preferences is to use RowSourceType 3 - SQL Statement OR 2 - Alias and write my own query to pull out the items, in the format that I want to, and this provides easy ways of adding any kind of custom "empty" or "no filter" item into that solution.

For example, let say I'm using the Northwind Employees table as the source for my drop-down filter. I can run a query such as:

Select Cast("<All Employees>" AS C(40)) AS FullName ,Cast(0 As I) As EmployeeId From Employees Where EmployeeId = 1 Union Select Cast(Alltrim(LastName) + ", " + Alltrim(FirstName) AS C(40)) As Fullname ,EmployeeId From Employees Order By 1 Into Cursor ddEmployee

...and this will give me a list of all the employees, but with a <All Employees> item at the top of the list. The default item will have an ID of zero and all the rest of the items will have their correct Employee ID. This will allow me to code the logic on the UI to say if the value of the drop-down is greater than zero, apply the filter, otherwise ignore the filter.

NOTE: One less than ideal aspect of that particular query is that for the <All Employees> item in that SQL query, I had to code WHERE EmployeeId = 1, to ensure that I did get 1 and only 1 item. I did that because I'd looked at the Employees table and I knew it had a row where EmployeeId = 1 so it worked for my example but in an empty database or one where ID #1 doesn't exist for another reason, like it has been deleted due it the table not being a static lookup, that WHERE clause would not work to give you the desired item. To make that part always work all of the time you could pull using a table where you can guarantee a particular row exists, in most of my applications we do always have one "company" record or "instance" record that stores some configuration for the installation so we can use that but you can also use a concept that some people do in their databases and that is to have a simple numbers table or cursor. This can take a number of forms but in it's simplest terms it could just be a cursor with maybe just ten records in it where the IDs for each row are the numbers 1 to 10 and have it so your application can always call upon this table if it ever needs to. You can use this table/cursor for lots of useful things and one is for this type of solution:

Select Cast("<All Employees>" AS C(40)) AS FullName ,Cast(0 As I) As EmployeeId From MyNumber Where Id = 1 Union Select Cast(Alltrim(LastName) + ", " + Alltrim(FirstName) AS C(40)) As Fullname ,EmployeeId From Employees Order By 1 Into Cursor ddEmployee

That will create the <All Employees> item using the number cursor and union in the employees from the employees table.

Another option is to specifically use SELECT TOP 1 in that part that is creating the <All Employees> item but you'd still have to be sure the table you have in your FROM has at least 1 row.

In full SQL Server these hacks for that item are not required, in SQL Server these days (and for a long time) you can just do the equivalent of:

Select Cast("<All Employees>" AS C(40)) AS FullName ,Cast(0 As I) As EmployeeId Union Select Cast(Alltrim(LastName) + ", " + Alltrim(FirstName) AS C(40)) As Fullname ,EmployeeId From Employees Order By 1 Into Cursor ddEmployee

...ignoring the fact that some of that is VFP syntax, it's a bit of a mish mash, I'm just demoing that in other database engines you can remove the FROM and WHERE from the first part and it'll just create one row with the specified values. VFP requires a FROM and the FROM dictates how many rows in conjunction with WHERE and JOINs etc.

This means another potential solution is to pre-emptively create your own separate cursor/alias with the contents that you want in the drop down. For example you could write code in the LOAD event of your form to create a cursor, insert the "default" row with whatever values you want and then append in the rows for the filters. Still continuing with the Northwind Employees example:

Create Cursor MyFilter (FullName C(40), Id I)
Insert Into MyFilter (FullName, Id) VALUES ("<All Employees>", 0)
Insert Into MyFilter (FullName, Id) SELECT Alltrim(LastName) + ", " + Alltrim(FirstName) As Fullname, EmployeeId From Employees Order By 1

With the cursor created with the exact contents that you want, you can use RowSourceType 2 - Alias or 6 - Fields and the appropriate RowSource

Some of these solutions will leave cursors/aliases sat in the background so be mindful of that and, if necessary, tidy them up by closing them in the UNLOAD method of your form.

These are just a few suggestions, when it comes to combos and drop-downs there are no right answers just many alternative answers.

Yes, this is he way we do it as well: using views mostly but sometimes cursors to drive a combobox

 

[SitesMasstec]

Mike:
Setting ListIndex=0 for the Combobox did not work for the highlighted in blue, above.
In the area highlighted in red, I already use a clean button to deselect eventual selecting if the user click the down arrow of the Combobox/es and want to give up selection/s.

Mike and Paul:
In the Combobox highlighted in blue, as the source table have more than 2 thousand records, I just add a blank record in that table and the problem was solved, the first option in the Combobox is a blank one and if the user leaves it in this condition, all records are selected.

 

[igorsandler]

You c an design your forms any way you want but, in my opinion:
1. I would never use tables as a source for anything, only views. They can be open exactly the same way as tables can but it much safer than to use tables directly.
2. 2000 records in the combobox???? Have you try to pick a record yourself? From 2000+ records? Combobox shouldn't be used for so many records to pick because it's extremely hard to find the record you want. We have a ton of similar forms and we use PageFrames with first tab having a grid with all search options you need and the second page with detail records selected from the grid.

Again, this is the way we do, you can do any way you like

 

[SitesMasstec]

Hello, Igor!

About your 2) note: The 2.000 records (just field of names) in the combobox appears in alphabetical order and the user can type J and the combobox shows the first names starting with J. The user can continue typing, for example, Jo, and the combobox shows names starting with Jo.

About your 1) note: Yes, I agree with you, I am afraid to use table directly in the combobox, I would like to use something like a mirror of the table. As you said, it seems, View is the solution. I have never used a View in my programs, but I will try to understand how to use this option.

 

[mjcmkrsr]

Hi,

You may of course have a look at the local or remote view and how it is created. But in this case it's overkill - you simply need a (filtered) cursor e.g.

Select ... from ... where ... into cursor csrTemp

hth

MarK

 

[igorsandler]

Hello, Igor!

About your 2) note: The 2.000 records (just field of names) in the combobox appears in alphabetical order and the user can type J and the combobox shows the first names starting with J. The user can continue typing, for example, Jo, and the combobox shows names starting with Jo.

About your 1) note: Yes, I agree with you, I am afraid to use table directly in the combobox, I would like to use something like a mirror of the table. As you said, it seems, View is the solution. I have never used a View in my programs, but I will try to understand how to use this option.

Hello,
like I said, it's your app, you can design it any way you want. I was just saying that normally you don't use combobox, if the user need to chose from more than 20-30 records.
As far as views, I write my own SQL views statements, meaning I do not use FoxPro view wizard. We still use FoxPro 7 and view wizard will not allow more than 3 JOINS so more than 20 years ago I've been though to write my own SQL Views statements, a lot of them pretty complex.
Anyway, good luck with all that