Any way to see if a record exists in a particular foundset?

Classic Servoy
1

I was wondering if there is some sort of call I could use to validate whether or not a record exists in a particular foundset, without having to call the select record method?

I have one form that is looking to add records to another form if it does not exist already.

Currently I utilize the ```
!forms.form.foundset.selectRecord(pks[0,0],pks[0,1],pks[0,2])


This is proving to be a little expensive as it is performing the select record functionality on each record I need to validate.

I've also looked at converting the foundset dataprovider as an array, but am not able to pass multiple column names into this function.

databaseManager.getFoundSetDataProviderAsArray(forms.form.foundset,new Array(‘pk1’,‘pk2’,‘pk3’));


Would anyone have any ideas on what I could do to validate whether or not a record exists?
2

What about duplicating the foundset and performing a find/search using the pks? This would not change the existing foundset.

3

Thats sort of an odd request. I’ve never needed to check if a PK was already loaded in the foundset, instead, if I want to load a record, I just use something like:

function selectRecord(_foundset, _pk){
   if(_foundset.selectRecord(_pk)) {
      return true
   }
   else{
      if(_foundset.find() || _foundset.find()){  //if foundset hasn't been loaded yet, first find fails
         var pkName = databaseManager.getTable(_foundset).getRowIdentifierColumnNames()[0] //only works with single PK tables
         _foundset[pkName] = _pk
         _foundset.search()
         return _foundset.selectRecord(_pk)
      }
      else{
         return false
      }
   }
}
4

Why not just do a custom query to perform the check.
This could be as easy as select count(*) from x where y etc.
This’ll always return a 1 row, 1col dataset with an integer.

Any further actions could then depend on:

if(!_dataset.getValue(1,1))
{
// no records exist, do the job
}
else
{
// do something else or just nothing
}

Only thing to be aware of using this code that it’ll only work correctly when records have been saved to the database already.
Any outstanding changes on record(s) which might have effect on the result won’t be taken into account.

5

Hi All,

Thank you for the responses. The select record method, depending on our foundset size, can be fairly lengthly. I mean it is still usable, I was just wondering if there might be a faster way to achieve this.

Thomas:
I think duplicating a large foundset and then performing the find would end up taking longer than performing the selectRecord method. But I could give that a try.

goldcougar:

goldcougar:
Thats sort of an odd request. I’ve never needed to check if a PK was already loaded in the foundset, instead, if I want to load a record, I just use something like:

I thought this would be something that would sort of be basic functionality, didn’t really consider this to be an odd request. The reason for this is that we have a search screen. The user will utilize the search and then move the items they want to another list. SImilar to functionality you would see in a lot of systems where a user can move single/multiple/all items back and forth between two lists. I have to check to see if an item exists in one list because I don’t want the user to have duplicated values, and therefore I wouldn’t add a new row to the dataset if it is a duplicate.

The select record method you have provide won’t work in our situation because it is still doing the selectRecord. I can see this working for most cases, but because our foundset may be shared across a few other screens, which may have many related tabs, the select record may not be the most efficient method for us.

I hope that made sense…

mboegem:
I will try the custom query and see if that speeds things up a bit.

Thank you all again for the suggestions.