[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.

//set your pk
var vCompID = 8

//set servername from record table
var vServer = controller.getServerName()

//set tablename
var vTable = 'companies'

//create a foundset
var vSet = databaseManager.getFoundSet(vServer, vTable)

//load record by ID in foundset
vSet.loadRecords(databaseManager.convertToDataSet([vCompID]))

//get record data
var vName = vSet.company_name

//or retrieve record data from the record object
var vRecord = vSet.getRecord(1)
var Mail = vRecord.company_email

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:

var vCount = databaseManager.getFoundSetCount(vSet)

Comments (7)

Ryan 2008-01-25

HA! I had a forum post asking just how to do this. This just became a new global method in my solution :-)

Ben Savignac 2008-01-26

Great tip, Karel!

Arup Ranjan Sahoo 2008-01-29

Great TIP... :)

Tom Parry 2008-03-04

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?)

Karel Broer 2008-03-11

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.

Tom Parry 2008-04-09

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

Karel Broer 2008-04-25

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 vSet.loadRecords(databaseManager.convertToDataSet(vAddressArray)) 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) forms.adresslist.controller.loadRecords(vSQL) 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.