convertFoundSet

Classic Servoy
1

Hi all,

//for ALL customers//shows all the invoices of all customers in the current foundset
var allInvoices = forms.invoices.controller.showRecords(databaseManager.convertFoundSet(foundset, customers_to_invoices));

I want to do the opposite, on a many to one relation.

I have a foundset of invoices and I want to show all companies in my Companies form.

Is this possible?

TIA
Stef

2

As lon as you have a relation that goes from invoices to customers (called “invoices_to_customers”) you can just use:

var allInvoices = forms.invoices.controller.showRecords(databaseManager.convertFoundSet(foundset, invoices_to_customers));
3

Thanks Bob!

But the strange thing is that one company is displayed as many times that there invoices are.

For example, “Cusick” has 5 invoices in the foundset, he will be displayed 5 times in the company list.

Bug?

TIA,
Stef

4

No - not bug (this isn’t FileMaker). :D

You have to get all the unique customer ids:

//Returns a foundset dataprovider (normally a column) as JavaScript array
var myArray = databaseManager.getFoundSetDataProviderAsArray(foundset,'customerid').sort();

var ids = new Array()
var lastValue = ''

//loop through the array to get the unique customer id
for ( var i = 0 ; i < myArray.length ; i++ )
{
	if(myArray[i] != lastValue)
	{
		ids.concat(myArray[i])
		lastValue = myArray[i]
	}
}

forms.customers.controller.showRecords(ids)
5

Sorry, Bob! This does not work…

6

Sorry - here’s the right one:

//Returns a foundset dataprovider (normally a column) as JavaScript array
var myArray = databaseManager.getFoundSetDataProviderAsArray(foundset,'customerid').sort();

var ids = new Array()
var lastValue = ''

//loop through the array to get the unique customer id
for ( var i = 0 ; i < myArray.length ; i++ )
{
	if(myArray[i] != lastValue)
	{
		ids.unshift(myArray[i])
		lastValue = myArray[i]
	}
}

//Converts the argument to a JSDataSet with one column that can be used in controller.loadRecords(pkdataset)
var temp = databaseManager.convertToDataSet(ids)

forms.customers.controller.showRecords(temp)
7

THANK YOU Bob!

Works perfect!

8

You’re welcome! One important note: the controller.show() and controller.loadRecords() will only work with a maximum of 200 unique ids. This is due to the fact that many sql databases will only allow 200 items in an “in” query.

Just to be “safe” you might want to enhance the code to read:

//Returns a foundset dataprovider (normally a column) as JavaScript array
var myArray = databaseManager.getFoundSetDataProviderAsArray(foundset,'customerid').sort();

var ids = new Array()
var lastValue = ''

//loop through the array to get the unique customer id
for ( var i = 0 ; i < myArray.length ; i++ )
{
	if(myArray[i] != lastValue)
	{
		ids.unshift(myArray[i])
		lastValue = myArray[i]
	}
}

if(ids.length <= 200)
{
	//Converts the argument to a JSDataSet with one column that can be used in controller.loadRecords(pkdataset)
	var temp = databaseManager.convertToDataSet(ids)
	
	forms.customers.controller.showRecords(temp)
}
else
{
	plugins.dialogs.showErrorDialog('Error!', 'Too many unique customers found: ' + ids.length + '\n200 maximum allowed.', 'OK')
}