« [Commentary] Servoy 4.0 (Eclipse based) Impressions | Main | [Challenge] Difference between controller.newRecord() and foundset.newRecord() »

January 25, 2008

[TIP] Get record data by PK ID without executing a search

by Karel Broer

Is it possible to get all info from a record without doing any search? The answer is yes! For this trick you only need to know the record primary key and Servoy 3.5.

So now you get a record without any searching and no complicated SQL. You might doublecheck if vSet.loadRecords() get's a record by performing:

Posted by David Workman on January 25, 2008 at 04:31 PM in Tips | Permalink


HA! I had a forum post asking just how to do this.

This just became a new global method in my solution :-)

Posted by: Ryan | Jan 25, 2008 4:53:21 PM

Great tip, Karel!

Posted by: Ben Savignac | Jan 26, 2008 7:06:14 AM

Great TIP... :)

Posted by: Arup Ranjan Sahoo | Jan 29, 2008 2:47:00 AM

Does this approach work when the pk that I want to find is at the very end of a large foundset (i.e. > 200 magic number?)

Posted by: Tom Parry | Mar 4, 2008 1:31:20 PM

This approach will allways work, because it uses it's 'own' foundset; you'll actually create a new 'foundset' by doing:
var vSet = databaseManager.getFoundSet(vServer, vTable)
and then load your record in there. You only need to know the primairy key to load that record.

How you 'retrieve' that pk, that's totally up to you. Could be by selecting the record, or just grap ID's from an array, whatever...
I might post an extended version of this tip, because it's also possible to load and grab 'related' records this way.

Posted by: Karel Broer | Mar 11, 2008 2:06:44 PM

Are you able to post the update for a related set?

Posted by: Tom Parry | Apr 9, 2008 3:36:35 PM

Allright then.... there are a couple of ways to get related records from records in a foundset.

1. One of these ways is to get one to one related records without executing a search by doing this:
For example: you have a foundset of company records, and now you want to get the related address records in a separate foundset.

//set company tablename
var vCompTable = 'company'

//set sk field from address
var vAddressIDField = 'address_id'

//get address id's as array from company foundset
var vAddressArray = databaseManager.getFoundSetDataProviderAsArray(vCompTable, vAddressIDField)

//set servername
var vServer = 'example'

//set address tablename
var vAddrTable = 'address'

//create an address foundset
var vSet = databaseManager.getFoundSet(vServer, vAddrTable)

//load address record by vAddressArray in foundset

2. Another way is get one to many related records. This time we need to use 1 query statement, but it's a very simple to copy and paste this example into your own situation :-).

In this example we have multiple address records for one company. That means that the company record has a relation to address records by having a company id in the address table. Make sense? I hope so...

//set company tablename
var vCompTable = 'company'

//set sk field from address
var vCompIDField = 'company_id'

//get company id's as array from company foundset
var vCompanyIDArray = databaseManager.getFoundSetDataProviderAsArray(vCompTable, vCompIDField)

//convert array to comma separated string
var vCompanyIDs = vCompanyIDArray.join(',')

//set the SQL
var vSQL = 'SELECT address_id FROM address WHERE company_id IN (' + vCompanyIDs + ')'

//load the address records on a form or in a created foundset (see example in this tip)

That's it! So now you have 2 examples of how to load related records from a foundset. I hope this will be usefull for y'all.

Posted by: Karel Broer | Apr 25, 2008 4:09:00 PM

Post a comment