# Filtering using global variable/relation -show all records?

**URL:** <https://forum.servoy.com/t/filtering-using-global-variable-relation-show-all-records/13067>\
**Category:** Classic Servoy\
**Created:** [October 19, 2010, 7:32pm UTC](https://forum.servoy.com/t/filtering-using-global-variable-relation-show-all-records/13067 "2010-10-19T19:32:12Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![schoby](https://avatars.discourse-cdn.com/v4/letter/s/dc4da7/32.png) [@schoby](https://forum.servoy.com/u/schoby)\
**Post date:** [October 19, 2010, 7:32pm UTC](https://forum.servoy.com/t/filtering-using-global-variable-relation-show-all-records/13067/1 "2010-10-19T19:32:12Z")

</div>

Hello,

more or less I am a newbie at servoy and found a lot of information at this forum, but for the following problem I didn’t found a solution:

I have two tables:

- products (product\_id, product\_name,product\_category)
- product\_categorie (product\_categorie\_id,categorie\_name)

Then I added a global variable filterProducts and made a global relation globals.filterProducts= products.product\_category.  
I created a form without any datasource and added one combobox containing the global variable filterProducts and a portal with the relation above.  
Finally I added a valuelist with all product\_categories to the filterProducts field.  
When I now change the Categorie in the combobox the portal only shows products from this category.

And now my question - how can I show all topics/products ? My idea is that if nothing in the combobox is selected all products are shown and if a categorie is selected only products from this categorie are shown.

I atttached a small sample solution demonstrating the problem

Regards

Steffen

[sample\_project.servoy](https://forum.servoy.com/uploads/short-url/3QR9nCUYIwBx0Uh768ksEdOqcl9.servoy) (4.77 KB)

---

<div class="post-metadata">

**Author:** ![ngervasi](https://avatars.discourse-cdn.com/v4/letter/n/f9ae1b/32.png) [@ngervasi](https://forum.servoy.com/u/ngervasi)\
**Post date:** [October 19, 2010, 8:20pm UTC](https://forum.servoy.com/t/filtering-using-global-variable-relation-show-all-records/13067/2 "2010-10-19T20:20:32Z")

</div>

The easiest way of doing that is not by using a global relation and a portal but by using a form based on the table you are filtering on (in table view mode) and a custom valuelist like this attached to a form variable in the form header:

```auto
function onShow()
{
	if(arguments[0]) // only on first show
	{
		// Set up the selector valuelist
		var _sql = "SELECT descr, cdsta FROM notstt1l WHERE cdling = ?"
		var _ds = databaseManager.getDataSetByQuery('lfspa',_sql,[globals.gui_language],-1)
		_ds.addRow(1,['-',null])
		_ds.addRow(1,['Show All',99])
		application.setValueListItems('totalization_status_selector', _ds)
	}
}

```

and you have to trigger a method in onDataChange of the category selector to load the correct data in the tab panel form.

```auto
function onFilterChange()
{
	var _new = arguments[1]
	if(_new == 99)
	{
		// Load all the recs in the totalization table
		var _sql = "SELECT codprj \
			FROM nprevj0f \
			WHERE codpre = ? AND magpro = ?"
		controller.loadRecords(_sql,[forms.approvals_detail.codpre,forms.approvals_detail.magpro])
	}
	else
	{
		// Load all the recs in the totalization table with the selected status
		var _sql = "SELECT codprj \
			FROM nprevj0f \
			WHERE codpre = ? AND magpro = ? AND statot = ?"
		controller.loadRecords(_sql,[forms.approvals_detail.codpre,forms.approvals_detail.magpro,_new])
	}
}

```

Keep in mind that the above code is running on Servoy 4.1 so if you use Servoy 5.x you have to handle the Event passed to every method attached to the GUI as arguments[0].

Hope this will help you get going and, by the way, welcome to the Servoy community ![:wink:]( "Wink")

---

<div class="post-metadata">

**Author:** ![schoby](https://avatars.discourse-cdn.com/v4/letter/s/dc4da7/32.png) [@schoby](https://forum.servoy.com/u/schoby)\
**Post date:** [October 26, 2010, 10:04pm UTC](https://forum.servoy.com/t/filtering-using-global-variable-relation-show-all-records/13067/3 "2010-10-26T22:04:49Z")

</div>

Hi,

thanks for your provided solution. More or less I was able to reproduce your steps for filtering. But in my case there is still one problem.  
So let me think about a solution where I have two forms representing the data of the same table products.  
One form so called frmProducts which is used to manage the products.  
One called lstProducts which is used for selecting some products and doing some Drag and Drop stuff.

So my problem now is if I do something like:

```auto
function onDataChangecbo_Product_Filter(oldValue, newValue, event) {
if (newValue != -1) {
		var sqlstring = "SELECT product_id FROM products WHERE product_category = ?";
		forms.lstProducts.controller.loadRecords(sqlstring, [newValue]);
	} else {
		var sqlstring = "SELECT product_id FROM products";
		forms.lstProducts.controller.loadRecords(sqlstring, null);
	}
}

The data in the form lstProducts is filtered nice but unfortunately the data in the other form frmProducts is also filtered which is not as expected/desired.

Any solutions on this ?

Regards

Steffen

```

---

<div class="post-metadata">

**Author:** ![stefanoni](https://avatars.discourse-cdn.com/v4/letter/s/bb73d2/32.png) [@stefanoni](https://forum.servoy.com/u/stefanoni)\
**Post date:** [November 5, 2010, 4:42pm UTC](https://forum.servoy.com/t/filtering-using-global-variable-relation-show-all-records/13067/4 "2010-11-05T16:42:47Z")

</div>

Hi,  
If I understood your problem right, you could separate the foundset (probably actualy used in both forms) into two  
independent foundsets.

Watch the form propertie “namedFoundSet”, you can change it from “default” to “separate”

or have a look at  
[http://wiki.servoy.com/display/public/D … edFoundSet](http://wiki.servoy.com/display/public/DOCS/Form#Form-namedFoundSet)  
[http://wiki.servoy.com/display/public/D … edFoundSet](http://wiki.servoy.com/display/public/DOCS/JSForm#JSForm-namedFoundSet)

hope this helps  
Alessandro

---

<div class="post-metadata">

**Author:** ![schoby](https://avatars.discourse-cdn.com/v4/letter/s/dc4da7/32.png) [@schoby](https://forum.servoy.com/u/schoby)\
**Post date:** [November 6, 2010, 9:18pm UTC](https://forum.servoy.com/t/filtering-using-global-variable-relation-show-all-records/13067/5 "2010-11-06T21:18:30Z")

</div>

Hi Alessandro,

you made my day - that was exactly what I was looking for! Your proposed solution works fine.

Thanks a lot.

Schoby

---

<div class="post-metadata">

**Author:** ![stefanoni](https://avatars.discourse-cdn.com/v4/letter/s/bb73d2/32.png) [@stefanoni](https://forum.servoy.com/u/stefanoni)\
**Post date:** [November 8, 2010, 9:29am UTC](https://forum.servoy.com/t/filtering-using-global-variable-relation-show-all-records/13067/6 "2010-11-08T09:29:45Z")

</div>

> schoby:  
> Hi Alessandro,  
> you made my day - that was exactly what I was looking for! Your proposed solution works fine.  
> Thanks a lot.  
> Schoby

I started as a newbie a view month ago and I think we all are running into the same issues…  
regards