Unique values in an array

Classic Servoy
1

I am trying to link a foundset in child records back to a set of parent records in another table / form.

I think I need to put all my keys into an array, deduplicate it, and then convert the array to a dataset and load in my parent form

so far I have this:

function load_records_in_dh(event) {

var varray = databaseManager.getFoundSetDataProviderAsArray(foundset,'artwork_id_fk')

var sorted_arr = varray.sort();

var results = [];
for (var i = 0; i < varray.length - 1; i += 1) {
        if (sorted_arr[i + 1] == sorted_arr[i]) {
                results.push(sorted_arr[i]);
        		}
		}

var dataset = databaseManager.convertToDataSet(results);

forms.artwork.controller.loadRecords(dataset)

forms.artwork.controller.show()

My problem is that the var sorted_arr = varray.sort(); adds a ‘.0’ to the end of each of my values…

Is it possible to strip the ‘.0’ from each array value (with a loop perhaps) ?

or is it possible to get it to sort the array without adding the ‘.0’ to each value?

or is there a really simple way to load my foundset - as unique values - into another table / form?

Thanks all…

Bevil

2

Hi,

I use this, I think I got this from someone on this forum ( not sure)

// remove duplicate pk's 
//this link explains next code [url]http://www.planetpdf.com/developer/article.asp?ContentID=6389[/url] 
var unduped = new Object; 
for (var i = 0; i < pkArray.length; i++) 
{    
   unduped[pkArray[i]] = pkArray[i]; 
} 
var uniques = new Array; 
for (var k in unduped) 
{ 
   var x = unduped[k]; 
   if(x) 
   { 
         uniques.push(unduped[k]); 
   } 
}
3

Hi

Try applying the sort to the foundset on the column you need before creating the array using foundset.sort(‘field asc’)
Just tested and I think this should work.

to get the unique pks you could use getSql to grab the sql from the related foundset and then use it within a nested select statement

so get your sql and then do

‘select distinct artwork_id_fk from childTable where mainPKforChildTable IN (getsql code goes in here)’

then use foundset.loadrecords with the above query

4

Bevil,

Why don’t you skip the Array and DataSet altogether and just use SQL to load in the PK’s like so ?

var _sQuery = foundset.getSQL(),
    _aParam = foundset.getSQLparameters();

forms.parentForm.controller.loadRecords("SELECT DISTINCT pkColumn FROM parentTable JOIN childTable ON pkColumn=fkColumn WHERE pkChildTableColumn IN (" + _sQuery + ")", _aParam)

When Servoy is not using any temp tables this should work.

Hope this helps.

5

foundset.getSQL(), is not a function apparently…

databasemanager.getSQL is, but how do I set it to the current foundset?

:)

Bevil

6

Hans, that nailed it… Thank you :)

I’m still keen to know what getSQL does though, thanks McCourt and Robert… :)

7

Thunder:
foundset.getSQL(), is not a function apparently…

databasemanager.getSQL is, but how do I set it to the current foundset?

Ack! Yes, I meant databaseManager.getSQL().
So the code would be:

var _sQuery = databaseManager.getSQL(foundset),
    _aParam = databaseManager.getSQLparameters(foundset);

forms.parentForm.controller.loadRecords("SELECT DISTINCT pkColumn FROM parentTable JOIN childTable ON pkColumn=fkColumn WHERE pkChildTableColumn IN (" + _sQuery + ")", _aParam)

databaseManager.getSQL() gives you the actual SQL (prepared statement) used by Servoy for that foundset. Any passed parameters are returned using the databaseManager.getSQLparameters() function.

Hope this helps.