Servoy Forum

by **agis** » Sat Oct 25, 2003 12:29 pm

With the command: getDataSetByQuery we can run a SQL search and fill the result in a variable (resp. array).
Now my Question: How to process the result. For Example showing the dataset in a portal, combine it with other datasets, make it to the aktual selection and so on. No idea how to manage this, could you help us?

by **IT2Be** » Sat Oct 25, 2003 1:32 pm

I am not certaiin but I think you can only fill a portal through a relationship.

Filling fields etc. can be done like 'dataprovider = dataset.getValue(x,y)' and hooking up values to elements can be done by naming an element 'element.name.whatever = dataset.getValue(x,y)'.

Hope this is somewhat of an answer. I am pretty new to servoy so there could be lot's of other/better answers...

Marcel

by **Jan Aleman** » Sat Oct 25, 2003 6:21 pm

Click on move sample while highlighting the object in the method editor and you'll see some documented examples.

Additionally you can also load the results of a query into a form, in that case make sure your query is returning primary keys and load those into the form by using:

var query = "select orderid from orders where orderid = 10248";
var pkdataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 100);
controller.loadRecords(pkdataset);

Keep in mind that if you use the method above you can't print anymore or sort.

by **Harjo** » Sun Oct 26, 2003 12:51 am

jaleman wrote:Keep in mind that if you use the method above you can't print anymore or sort.

I thought the limititations where solved!?
see last reply of Jan Blok:

http://forum.servoy.com/viewtopic.php?t=281

by **Jan Aleman** » Sun Oct 26, 2003 11:35 am

I stand corrected!

by **pbakker** » Fri Jan 16, 2004 11:34 pm

Question about loading the result of a custom SQL statement (getdatasetbyquery) into a form: No problem of getting the result into the form as a foundset, but is there a way to display it on listview or a portal?

What I' trying is to get a listview of al the users in the system. The getasHTML doesn't fullfill my needs (I think), because I want the user to be able to select one of the found users and based on the selection trigger methods.

So far I have the following code:

var pkdataset = security.getUsers()
//elements.test.text = '<html>'+users.getAsHTML()+'</html>';
controller.loadRecords(pkdataset)

Any way of doing this? (without building a form based on the repository)

Paul

by **pbakker** » Fri Jan 16, 2004 11:45 pm

I see we cannot base forms on the Servoy system DB-tables, since they are not visible when creating a new form....

by **bcusick** » Sat Jan 17, 2004 12:08 pm

pbakker wrote:Question about loading the result of a custom SQL statement (getdatasetbyquery) into a form: No problem of getting the result into the form as a foundset, but is there a way to display it on listview or a portal?

What I' trying is to get a listview of al the users in the system. The getasHTML doesn't fullfill my needs (I think), because I want the user to be able to select one of the found users and based on the selection trigger methods.

So far I have the following code:

var pkdataset = security.getUsers()
//elements.test.text = '<html>'+users.getAsHTML()+'</html>';
controller.loadRecords(pkdataset)

Any way of doing this? (without building a form based on the repository)

Hi Paul,

Beginning in 2.0b1 you can execute a global method with INLINE HTML. So, if you displayed the list of users as HTML - you can add this line to your HTML - and have a "link" that the users can click on that execute a Servoy global method:

<a href="javascript:methodName">PersonName</a>

You can even pass parameters to the global method:

<a href="javascript:methodName('100','300')">PersonName</a>

IMPORTANT NOTE: If you pass parameters - they MUST be surrounded by SINGLE QUOTES.

Hope this helps,

Bob Cusick

by **pbakker** » Sat Jan 17, 2004 2:09 pm

Good thing I wrote '(I think)'....

all these new features, I cannot keep up with them.

Tnx for the help, I'll go and play around with it.

Two other questions though:
- Based on your answer: I asume that it is not possible to retrieve the result in a portal of normal listview?
- How can I alter the look of the HTML retrieval, to make it look more like a regular listview?

Paul

by **maarten** » Sat Jan 17, 2004 2:47 pm

- Based on your answer: I asume that it is not possible to retrieve the result in a portal of normal listview?

portal>no, listview>with controller.loadRecords(pkdataset)

How can I alter the look of the HTML retrieval, to make it look more like a regular listview?

you can do a loop through the dataset and combine html with the
dataset values:

Code: Select all: var HTML = '<html><table>' //var HTML receives all html code //get dataset by query for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ ) { dataset.rowIndex = i; HTML += '<tr><td>'+ dataset[1]+'</td><td>'+ dataset[2]+'</td></tr>'; } HTML += '</table>' elements.test.text = HTML; //set your label with the html code

by **pbakker** » Sat Jan 17, 2004 3:59 pm

Maarten,

"controller.loadRecords(pkdataset)": does this work with a dataset retrieved by getDatasetByQuery, especially if the Query runs over another table (or multiple tables) than the table the form, on which you want to display the dataset, is based?

As for the suggested loop through the data: I'll go and fiddle with it...

Paul

by **bcusick** » Sat Jan 17, 2004 4:19 pm

pbakker wrote:"controller.loadRecords(pkdataset)": does this work with a dataset retrieved by getDatasetByQuery, especially if the Query runs over another table (or multiple tables) than the table the form, on which you want to display the dataset, is based?

YES! Does work. That's sort of the whole point...

In your select statement returnONLY the primary key of table you're going to display in.

For example: customers is the base table for my listview (used in a tabpanel). The primary key is "custId".

Your query would look like:

SELECT custId from customers where ......

Bob Cusick

by **pbakker** » Sat Jan 17, 2004 5:19 pm

Well, I guess I have to try more.....

Last question then: If I use the security node to retrieve all user_id's and user_names, the PK is User_ID and I want to display the result using controller.loadrecords(pkdataset), I asume I must have a form based on the table where the user_ID's are pk as well, right?

As far as I know, I cannot build a form on that table, because that table (a Servoy table) does not show in the available tables when creating a new form....

by **jcompagner** » Mon Jan 19, 2004 1:36 pm

no you can't create a form based on a servoy table
The only thing to do is showing the user data in a value list

Code: Select all: var ds = security.getUsers(); var names = new Array(); var id = new Array(); for(var k=1;k<ds.getMaxRowIndex();k++) { id[k-1] = ds.getValue(k, 1); names[k-1] = ds.getValue(k, 2); } application.setValueListItems( 'users', names,id);

by **pbakker** » Wed Jan 28, 2004 11:55 pm

According to Bob, you could now have a link in HTML view launch a Servoy method. Based on the given examples, I created the following code. The HTML shows that it is a link, but the method is not launched.... where am I going wrong?

var pkdataset = security.getUsers()
var HTML = '<html><table>' //var HTML receives all html code
for( var i = 1 ; i <= pkdataset.getMaxRowIndex() ; i++ )
{
pkdataset.rowIndex = i;
HTML += '<tr><td>'+ pkdataset[1]+'</td><td><a href="javascript:test2()">'+ pkdataset[2]+'</a></td></tr>';
}
HTML += '</table>'
elements.test.text = HTML; //set your label with the html code

tnx in advance,

Paul

Servoy Forum

Manage Results from SQL Searches

Manage Results from SQL Searches

Who is online