I have a form with a portal on it. The portal shows data using a simple relationship as follows:
Globals.gCustomerNumber = support.customerNumber
I have a field onDataChange event that is firing to call my method.
The method is trying to load some new data into the portal. It looks something like this:
varMaxRows = 1000;
var query = “SELECT * from support WHERE support.status = ‘Resolved’ ORDER BY support.date DESC”;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
controller.loadRecords(dataset);
I get “Error loading primary key data, 1”
I verified using application.output that the SQL query is fine and the dataset does contain the correct data.
I tried using “useSeparateFoundSet” on this form, but it made no difference.
I also tried a modified SELECT and only selecting the fields that are shown in my portal, but that produced a similar error that said "Error loading primary key data, For input string: “2007-07-12:00:00:00.0”, which is the first date field in my SELECT statement.
When using loadRecords(dataset) your dataset should be a pkdataset, that is a dataset with one column filled with primary keys. To get such a dataset your query should be:```
SELECT support.support_id from support WHERE support.status = ‘Resolved’ ORDER BY support.date DESC
(if support_id is indeed the name of your pk)
Servoy will take care of getting the right fields to place on your form.
But doing things this way is actually not efficient, because two queries get fired to the database this way. One to get your dataset and one under the hood by Servoy in the loadRecords-function. You could use controller.loadRecords(query) instead.
Note that this will only load records on your form and will not affect the records in your portal, because that is based on a global relation. The set of records in your portal will only be affected if you change Globals.gCustomerNumber.
If your portal should be related to the selected record in your form, it should use a relation based on a field of your form's table instead of a global.
I hope this clears things up a bit. <img src="{SMILIES_PATH}/icon_smile.gif" alt=":)" title="Smile" />
I changed the select to just include the primary key, and I used the controller.LoadRecords (query) function instead. Correct records are being loaded into the form, but not the portal.
Is there no way to use a query for loading the records into a portal? Allowing it to only use a relationship seems to be rather limiting.
Basically, what I am trying to do is have a couple of filters (combo boxes) that the user can apply to control what they see in the portal.
For example, the first filter would be for supportRep, the second for supportStatus, the third might be for supportTopic, etc.
In one case the user may just apply the supportRep filter, in another case he may apply all the filters.
I have no problem building the SQL SELECT query based on the selection of the filters, and load the proper dataset on the form, but do not see how to load the portal with data.
Do I have to use a relationship for the portal that is based on calc fields that computes an index from the filter settings like this:
return supportRep + ": " + supportStatus + ": " + supportTopic
But how would that work if any filter(s) where empty?
Sorry, this is probably a simple concept, but I am struggling to grasp how to control what the portal shows.
you need to make sure the form you are loading into has the ‘use separate foundset’ option ticket, otherwise you may accidentally change the foundset of other forms based on the same table.
when you place the subform in the tabpanel, make sure you do this without going through a relationship (unrelated).
Are you saying that will work with a portal on the subform in the tab panel?
I know I can do it by showing a subform in a tab panel that shows a list of records loaded based on the SQL SELECT generated by the filters, but I was wondering if specifically a portal can be used (in this case on the tab panel).
The reason I ask is because a portal always has a relationship defined when you place it on any form.
The reason for using portals is just because it has a few more features, like click the column to sort and reorder the columns. But, if you can not change the recordset dynamically using filters, then I will be forced to use a sub form showing a list in a tab panel.