# Omit with loadRecords

**URL:** <https://forum.servoy.com/t/omit-with-loadrecords/14635>\
**Category:** Classic Servoy\
**Created:** [September 12, 2011, 11:17am UTC](https://forum.servoy.com/t/omit-with-loadrecords/14635 "2011-09-12T11:17:43Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![irenem](https://avatars.discourse-cdn.com/v4/letter/i/977dab/32.png) [@irenem](https://forum.servoy.com/u/irenem)\
**Post date:** [September 12, 2011, 11:17am UTC](https://forum.servoy.com/t/omit-with-loadrecords/14635/1 "2011-09-12T11:17:43Z")

</div>

Hi,

i try to omit records from a foundset that match a certain word. This ia a onSearch method.

```auto
var name = arguments[2].getSource().getName()
	var form = controller.getName()
	var frm = solutionModel.getForm(form)
	var field = frm.getField(name)
	var dataProvider = field.dataProviderID
	var data = forms[form].controller.getDataProviderValue(dataProvider)
	var searchData = utils.stringReplace('#%' + data + '%',"\n","")
	var jstable = databaseManager.getTable(controller.getDataSource());
	var tableSQLName = jstable.getSQLName();
	var columnNamesArray = jstable.getColumnNames();
	var firstColumnName = columnNamesArray[0];
	var jscolumn = jstable.getColumn(firstColumnName);
	var columnSQLName = jscolumn.getSQLName();
	var isPrimaryKey = jscolumn.isRowIdentifier();

	controller.loadAllRecords()
	var query = "SELECT " + firstColumnName + " FROM " + tableSQLName + " WHERE " + dataProvider + " NOT LIKE '" + searchData +"'"
	foundset.loadRecords(query)

```

But it doesn’t work. It finds that record instead of omit it. I also tried wit the != operator with the same result.

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [September 12, 2011, 6:01pm UTC](https://forum.servoy.com/t/omit-with-loadrecords/14635/2 "2011-09-12T18:01:14Z")

</div>

Hi Irene,

What is the actual SQL that it produces ?

---

<div class="post-metadata">

**Author:** ![irenem](https://avatars.discourse-cdn.com/v4/letter/i/977dab/32.png) [@irenem](https://forum.servoy.com/u/irenem)\
**Post date:** [September 12, 2011, 6:32pm UTC](https://forum.servoy.com/t/omit-with-loadrecords/14635/3 "2011-09-12T18:32:32Z")

</div>

Hi Robert,

Thanks for helping. This is the query:

> 

> “SELECT proj\_id FROM projects WHERE proj\_customer\_ref NOT LIKE ‘#%hello%’”

There is a record in the database with ‘hello folks’ in that field and when the method is fired it finds that record and omit all others.

What am I doing wrong?

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [September 12, 2011, 6:43pm UTC](https://forum.servoy.com/t/omit-with-loadrecords/14635/4 "2011-09-12T18:43:37Z")

</div>

Hi Irene,

> irenem:  
> “SELECT proj\_id FROM projects WHERE proj\_customer\_ref NOT LIKE ‘#%hello%’”

The # character is a Servoy control character to make the search case-insensitive. It’s however not a SQL control character.  
Your query should work with the following syntax:

```auto
SELECT proj_id FROM projects WHERE upper(proj_customer_ref) NOT LIKE upper('%hello%')

```

Hope this helps.

---

<div class="post-metadata">

**Author:** ![irenem](https://avatars.discourse-cdn.com/v4/letter/i/977dab/32.png) [@irenem](https://forum.servoy.com/u/irenem)\
**Post date:** [September 12, 2011, 7:16pm UTC](https://forum.servoy.com/t/omit-with-loadrecords/14635/5 "2011-09-12T19:16:04Z")

</div>

Hi Robert,

It still doesn’t work. Now it can’t find anything.

Maybe I should use an omit-loop, but I’m afraid that it will be very time expensive with the real data. There are 15000 records in it.

---

<div class="post-metadata">

**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\
**Post date:** [September 13, 2011, 10:00am UTC](https://forum.servoy.com/t/omit-with-loadrecords/14635/6 "2011-09-13T10:00:56Z")

</div>

Irene,

In your code you assume that the first column is the pk.  
Even if you use only single-pk tables in your solutions, it is not guaranteed that table.getColumnNames() returns the pk first (it returns the columns in the order as reported by the db).

You could also try a simple find.search:

```auto
var name = arguments[2].getSource().getName()
var form = controller.getName()
var frm = solutionModel.getForm(form)
var field = frm.getField(name)
var dataProvider = field.dataProviderID
var data = forms[form].controller.getDataProviderValue(dataProvider)
var searchData = utils.stringReplace('#!%' + data + '%',"\n","")
if (foundset.find())
{
      foundset[dataProvider] = searchData
      foundset.search()
}

```

Rob

---

<div class="post-metadata">

**Author:** ![irenem](https://avatars.discourse-cdn.com/v4/letter/i/977dab/32.png) [@irenem](https://forum.servoy.com/u/irenem)\
**Post date:** [September 13, 2011, 10:52am UTC](https://forum.servoy.com/t/omit-with-loadrecords/14635/7 "2011-09-13T10:52:41Z")

</div>

Hi Rob,

That was just what I needed! Thank you.  
I overlooked this little ‘!’.  
Sometimes life can be so simple ![:lol:]( "Laughing")