Looping through records and updating

Classic Servoy
1

Hi,

I’m having trouble with this method, and I think I have it pin-pointed down to the last line (not the commented out debugging line).

record.field always returns ‘undefined’.

var metrics = new Array('Pricing', 'Timeliness', 'Procedures', 'CustService','Quality','Billing','PO','Ontime');
var fields = new Array('pricingmetric','timelinessmetric','iwproceduresmetric','customerservicemetric','qualitymetric','billingaccuracymetric','purchaseordermetric','ontimedeliverymetric')
forms.VendorList.controller.loadAllRecords();
var foundsetsize = databaseManager.getFoundSetCount(forms.VendorList.foundset);

for(var i=1;i <=foundsetsize;i++)
{
	var record = forms.VendorList.foundset.getRecord(i);

	for(var j=0;j < metrics.length;j++)
	{
		var query = 'SELECT ROUND(AVG(CAST(rating as decimal(18,2))),2)*2 from surveyas where vendorid = ' + record.vendorid + ' and metric = \'' + metrics[j] + '\'';
		
		var result = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), query, null, 1);

		var field = fields[j];
		record.field = result.getValue(1,1);
		// application.output(record.vendorid + '\t ' + metrics[j] + '\t ' + fields[j] + '\t ' + result.getValue(1,1) + '\t ' + record.field);
	}
}

I thought I had this working a while ago, but it appears that it actually doesn’t do anything.

Thanks.

2

You might want to change this

var field = fields[j]; 
record.field = result.getValue(1,1);

into this

var field = fields[j]; 
record[field] = result.getValue(1,1);

Also ‘field’ might be a reserved word so maybe it’s better to use this

record[fields[j]] = result.getValue(1,1);
```It's less code anyway <img src="{SMILIES_PATH}/icon_wink.gif" alt=";)" title="Wink" />

Also you can optimize your whole code a bit like so:

var metrics = [‘Pricing’,‘Timeliness’,‘Procedures’,‘CustService’,‘Quality’,‘Billing’,‘PO’,‘Ontime’];
var fields = [‘pricingmetric’,‘timelinessmetric’,‘iwproceduresmetric’,‘customerservicemetric’,‘qualitymetric’,‘billingaccuracymetric’,‘purchaseordermetric’,‘ontimedeliverymetric’]

forms.VendorList.controller.loadAllRecords();

var foundsetsize = databaseManager.getFoundSetCount(forms.VendorList.foundset);

// lets declare the following variables only ONCE.
var query = ‘SELECT ROUND(AVG(CAST(rating as decimal(18,2))),2)*2 from surveyas where vendorid = ? and metric = ?;’;
var record = null;
var result = null;

for(var i=1; i<=foundsetsize; i++)
{
record = forms.VendorList.foundset.getRecord(i);

for(var j=0; j<metrics.length; j++)
{
result = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), query, [record.vendorid,metrics[j]], 1);
record[fields[j]] = result.getValue(1,1);
// application.output(record.vendorid + '\t ’ + metrics[j] + '\t ’ + fields[j] + '\t ’ + result.getValue(1,1) + '\t ’ + record[fields[j]]);
}
}


This way your query, record and result variable doesn't have to be declared every time. 
Also, if I am not mistaken, this way the query is send to the database engine as a prepared statement and will therefore only once decide the query-plan for this query. Instead of 8 times the amount of your records (!).

Hope this helps.