application.setValueListItems issue

I have a form that is based on my Order_Details table with a combobox that displays items from my Products table via a value list. However, when I use application.setValueListItems my value list does not change and shows all my products. What I want to display are the products that are marked as “Current” (current_product = ‘1’).

Outline of issue:

I have 1 value list (Products) attached to my combobox (Product_ID) from table order_details
I have 1 method that is attached to onShow of the order_dialog form (on_load_new_record)
I have 1 layout based on the Order_Details table (order_dialog)

Valuelist: Products
Database Values:
product_id (Return in dataprovider)
name (Show in field/list)

Method: on_load_new_record

controller.newRecord()
order_id = globals.gOrder_id;
elements.Product_ID.requestFocus(true)


var query = "SELECT product_ID FROM products WHERE current_product = '1'";
var maxReturnedRows = 1000;
var name = elements.Product_ID.getValueListName();
var d = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
application.setValueListItems( name, d);

Layout is called as a showFormInDialog via this method:

	application.showFormInDialog(forms.Order_Dialog,-1,-1,492,222,'Add Item',false,false,true)

After reading through the boards I found out that you need to have the combobox as non-editable, which i have done however this made no difference.

Any ideas?

Thank you,
David

Some remarks:

  1. If you want to fill a value list by a custom query, you should set the value list to “custom” and not define anything (the value list will be filled with what you queried instead of using the definition that you described)
  2. Your query only returns the product_id, but not the name that your user probably wants to use. Change the query to something like “SELECT product, product_id …”
  3. You could also create a relation from a global variable with value 1 to your products table, column “current_product”. The relation will “see” all current products. In the value list definition you can the use that relation.

Hope this helps.

Patrick

Thanks Patrick!

Number 3 is a great idea
"3. You could also create a relation from a global variable with value 1 to your products table, column “current_product”. The relation will “see” all current products. In the value list definition you can the use that relation. "

That solved the issue with no coding!