Combo Box Programming Approach With Inactive Values

[Auguy]

Looking for some advice on how others handle the following situation. A combo box contains a list of items for the user to select. The client only wants to see the currently active items. Not a big deal to do in the select for the combo box data source. But what if the user is looking at an old record where a particular item is now inactive and is no longer available in the combo box. I could have separate controls, a text box to show the current value and a combo box to show the available items when in the "Edit" mode. But then I will have to change the combo box source to include the inactive items and some method to determine when I need to change the data source. I have used a right-click to toggle this sort of action in the past but I'm hoping someone can pass along some of their knowledge and recommendations.

Auguy
Northwest Ohio

 

[JRB-Bldr]

"Not a big deal to do in the select for the combo box data source"

Meaning that you are not using a LIVE table for your combo box source.

If, instead of using a SELECT SQL to build a cursor which then becomes the Combo box Source, you were to use the LIVE table itself with either a conditional Index or FILTER to eliminate the non-ACTIVE records, the Combo box's display would be more 'real time'.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

But what if the user is looking at an old record where a particular item is now inactive and is no longer available in the combo box.

Do you mean that the record was active when you populated the combo, but has become inactive by the time the user drops down the combo?

If that's right, you should do the SELECT in the combo's DropDown event. That way, you guarantee that the activity status is completely up-to-date whenever the user sees the list portion of the combo.

If I've misunderstood the problem, my apologies. (It won't be the first time.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Auguy]

JRB-Bldr, I usually use live tables as the source. I just meant to say that changing the source wasn't a big deal. Your suggestion is exactly what I used for my right-click method. The problem still exists about when to trigger the full list vs. just the active list. I guess I could search for the item in the combo's data source and turn on/off the inactive items as needed. If the items are in a grid I would have to check each item, but I don't really see a way around this.

Mike, I should have been clearer. This might be an order from a month ago where the item was active at that time but it is now inactive and would not be in the list of active items.


Auguy
Northwest Ohio

 

[JRB-Bldr]

I too interpreted your issue like Mike did (wrongly!).

I thought that you were having problems with multiple users having the form on-screen and each of them having a 'snap shot' of your table being used as the Combo box source.

Then if one user changed the Active state of a record, the others would not see the change and still have that record as part of their Combo box choices.

If, as you say, this Active/Not-Active status is being set days/months ago, then I don't understand why your SQL Select is still bringing up the records which are Not-Active.

I would have guessed that each time the user enters the Form, they get a new query of the associated table and use the Active/Not-Active parameters as one of the Query parameters to exclude Not-Active records.

Regardless, in the Form's Load method, you were to
USE MyTable IN 0 && The 'Live' table
SELECT MyTable
SET ORDER TO Display
&& Where Index Expr: INDEX ON <Something> TAG Display FOR Active

Then if your Combo box source was set such that it used the alias MyTable as its source it would only show ACTIVE records.
The active Index would eliminate the display of Not-Active records in 'real time'. No right/left clicks necessary.

However if you should want to toggle between ALL Records and ACTIVE Records you could always use a button or click method to change the active Index and then Refresh the Combo box.

Good Luck,
JRB-Bldr

 

[Auguy]

I guess I'm not explaining myself very well. Let's say I have an order entry form with the usual header information and detail line items in a grid. For each detail line I only want to see the active items when entering a new order. This should be fairly straight forward using either a filter on a live table or using a select statement. I'm not really worried about an item's active/inactive status being changed during the order entry. When I save the order I have an order entered with only active items. Tomorrow one or more of the items on this order is marked as inactive. The next day I edit the order. If I only have the active items in the combo box, they will not match the items that are now inactive. So for either all of the combo boxes or at least the inactive items on the order, I have to include the inactive items in the combo, otherwise they will show a blank but still contain the inactive item. I don't see any way around this without comparing each line item against the active list and adjusting the combo data source accordingly. I hope this is a better explanation.

Auguy
Northwest Ohio

 

[Koen Piller]

Hi,

Auguy I am a little bit puzzled what your problem is. You want only some items in your combobox which have the active status? And you say it is no problem to use a select statement. So what is wrong with JRB's solution? If that does not work, maybe you post the select statement you are using now to populate your combo.

Regards,

Jockey(2)

 

[Auguy]

The problem is not in selecting/filtering the active/inactive items. The problem is how to determine a change in the list is needed. For a new order I will always only let them select active items so this isn't a problem. But for old orders some of the items could be inactive and the item combo would have to adjust itself to contain the inactive item or the combo will be blank. Maybe I'll go back to my original method of showing only the active records for the new orders and all of the records for any edits.

Auguy
Northwest Ohio

 

[alan92rttt]

I get and have dealt with your issue.

The solution I've used it to have my dropdown based on a cursor.

Select just the active items into the readwrite cursor and then check to see if the current value is in the cursor. If it is not insert a record for that one item. This will get the user a dropdown with the active items plus the one saved with the data row they are editing.

basically

Select * from myTable where active=.T. into cursor curMyTable

locate for myEditTable.keyField = curMyTable.key
select myTable
locate for myEditTable.keyField = myTable.key
scatter memvar
insert into myTable from memvar

 

[Auguy]

Thanks to all for your help.
alan92rttt, I guess that's where I was headed and your advice will help solidify my approach.

Auguy
Northwest Ohio

 

[alan92rttt]

I just had another idea that may work.

Save the key from the row that is being edited to a public variable and then include that in your table.


public pcEditKey

pcEditKey=[101]

Select myTable
set filter to active=.T. or key = pcEditKey

 

[TamarGranor]

That's basically a good idea except that there's no reason to use a public variable (and lots of reasons not to). Use a form property to track the key for the current item.

Tamar

 

[alan92rttt]

I understand why you say to use a form property. I personally don't do it. Putting a form property for me has lead to numerous scope issues. Normally I'm setting the filter variable in one form and needing it outside of that forum scope or after that form has been released. If my app has an application object I'd put it there.

I figured Public was just an easier way to describe the concept.