another load records by query problem

Classic Servoy
1

I have a table form-guides which has a related table called questions. The questions table has a primary key with three fields (id_questions, id_form_guides, and id_projects).

When a user clicks on a button on the form_guides form, I want to display all of the related questions in a dialog window.

The code I am using is:

var myMain = currentcontroller.getName()
var myForm = application.getMethodTriggerFormName();
//plugins.dialogs.showInfoDialog( "myForm", myForm)

var myGuide = forms[myForm].id_form_guides
var myProj = forms[myMain].id_projects
var query = 'SELECT questions.id_questions, questions.id_form_guides, questions.id_projects FROM questions WHERE questions.id_form_guides = ' +  myGuide + ' and questions.id_projects = ' + myProj + ' order by questions.id_questions, questions.id_form_guides, questions.id_projects';
//application.output(query)
forms.questions.controller.loadRecords(query)
application.showFormInDialog(forms.questions,5,130, 710, 400,  " Questions for Project: " + forms[myMain].name_long + ", Form Guide: " + forms[myForm].name)

When this runs, I get the right number of records but the wrong data. The first record in the foundset is repeated in all of the records.

Interestingly, I have another set of nearly identical tables called question_groups and project_questions which are setup virtually identical. When I do the exact same thing with these forms everything works fine.

Here is the code that works fine:

var myMain = currentcontroller.getName()
var myForm = application.getMethodTriggerFormName();
//plugins.dialogs.showInfoDialog( "myForm", myForm)

var myGroup = forms[myForm].id_question_groups
var myProj = forms[myMain].id_projects
var query = 'SELECT project_questions.id_project_questions, project_questions.id_question_groups, project_questions.id_projects FROM project_questions WHERE project_questions.id_question_groups = ' +  myGroup + ' and project_questions.id_projects = ' + myProj + ' order by project_questions.id_project_questions, project_questions.id_question_groups, project_questions.id_projects';
//application.output(query)
forms.project_questions.controller.loadRecords(query)
application.showFormInDialog(forms.project_questions,5,130, 710, 400,  " Questions for Project: " + forms[myMain].name_long + ", Question Group: " + forms[myForm].name)

Can you see what I am doing wrong???

I made a sample of the problem if you want to look at it.

Thanks for your help.

2

What looks completely weird is this

forms.questions.controller.loadRecords(query)

The variable query contains a String. If using loadRecords you need a dataset. I think it should look like this:

var query = 'SELECT questions.id_questions, questions.id_form_guides, questions.id_projects FROM questions WHERE questions.id_form_guides = ' +  myGuide + ' and questions.id_projects = ' + myProj + ' order by questions.id_questions, questions.id_form_guides, questions.id_projects'; 
var dataset = databaseManager.getDataSetByQuery(servername, String query, null, 1000)
forms.questions.controller.loadRecords(dataset)

You construct a query but you never send it to the database. I am very astonished that you don’t get errors and really get to see data…

3

No. It also works with a query string. We do it all the time.

Below is a relevant portion from the Servoy Developer Reference manual:

200 Servoy Developer Volume ll: Reference Guide
Function loadRecords(sqlstring)
Description Loads records into the specified form based on a SQL query; a SELECT
statement that returns one or more primary keys (pk) for the specified form’s
table; also known as Form by query.
Syntax
controller.loadRecords(query,[queryArgumentsArray])
Parameters
query - qualified SQL SELECT statement that:

  • Must start with ‘SELECT’.
  • Must contain ‘FROM’ and ‘ORDER BY’ keywords.
  • Must select from the specified forms’s table.
  • Can contain ‘?’ which are replaced with values from the array
    supplied to parameters function argument.
  • Cannot contain ‘GROUP BY’ or ‘HAVING’.
  • All columns must be fully qualified like ‘orders.order_id’.
  • The selected columns must be the specified form’s table
    primarykey columns (alphabetically ordered like ‘select a_id,
    b_id,c_id …’).
    queryArgumentsArray - values from an array.
    NOTE: This function can be used with any number of rows returned in the
    dataset.
    Example controller.loadRecords("SELECTFROM);
4

cool feature. didn’t know… :lol:

5

gstein:
Can you see what I am doing wrong???

I made a sample of the problem if you want to look at it.

Thanks for your help.

It seems to me you did not comply to: primarykey columns (alphabetically ordered like ‘select a_id, b_id,c_id …’) as you quoted yourself from the manual.

6

Oh. Right. Sorry about that.

I had thought it needed to be in the table column order, not alphabetical order.

Thanks!