Populate a form in list view with query result

Classic Servoy
1

I have the following fields in place on a form

fc_program_year_basic
bs_labour_cost
bs_insulation_time

i need to populate these fields using the code below

var server = controller.getServerName()
var query = "SELECT a.fc_program_year_basic as 'fc_program_year_basic', SUM( b.bs_labour_cost) as 'bs_labour_costs',sum( b.bs_insulation_time) as 'bs_insulation_time' from fabric_condition a, budget_schedules b where a.fc_id = b.fc_fabric_key group by a.fc_program_year_basic"
var vdat = databaseManager.getDataSetByQuery(server, query, null, -1);
controller.loadRecords(vdat)

At present the form loads the correct number of records in list view, however the values returned are actually completely different to what the sql query should return

We have tested using the solution model to create a list view form based on the same query and the data returned is correct.

Why is it that we cannot loadrecords correctly from a dataset within a normal form

any help would be much appreciated

2

This is an ongoing problem so hope you dont mind me re-stating it with a bit more info.

We are attempting to create a listview form based on a SQLQuery. The form is based on a table (budget_schedules) and the follwing fields are on the form:-

fc_program_year_basic (this is from the related form joined in the SQL)
bs_labour_cost
bs_insulation_time

Here is the code we use to populate the load records:-

var server = controller.getServerName()
var query = "SELECT a.fc_program_year_basic as 'fc_program_year_basic', SUM( b.bs_labour_cost) as 'bs_labour_costs',sum( b.bs_insulation_time) as 'bs_insulation_time' from fabric_condition a, budget_schedules b where a.fc_id = b.fc_fabric_key group by a.fc_program_year_basic"
var vdat = databaseManager.getDataSetByQuery(server, query, null, -1);
controller.loadRecords(vdat)

This code loads the correct number of records into the form, however they have incorrect data! At the same time we use a loop to output the rows to the console and they get the correct data? So we realise that its something to do with the way the dataproviders are shown on the form - in outputting to the console we use dataset.getValue(row,col), and that gives us the correct result.

We’ve built a form using the solution model and that produces the correct records and data - though obviously we create the datasource overtly in that instance.

Q. How should I structure the columns on the form to get a list view of the correct data from this SQL and controller.loadRecords(SQL) - they are currently placed on the form as data-providers from the table its based on?

Q. Why would we get the correct number of records but with incorrect data?

Q. We anticipated in List View we would have all of the SQL rows output - but it looks like the only way to display this info is by using form variables and an overt getValue(row,col) on every field on the form, using a loop to step through the rows. Is this the case???

I’m certain I must have this wrong as list view works in the solution model it must be similar in the basic form view???

3

Hi McCourt,

If you want to load a dataset into the controller you need to give it a dataset with only the PK’s and then it will still only show the (other) dataproviders of the table, not of your query.

You can however base a form (using the SolutionModel) on a dataset (converted to a DataSource) so then it IS your (virtual) table.
Jan Aleman posted an example on how to do that here.

Hope this helps.

4

Hi

Thanks for the reply, it looks like the solution model is the way to go then after all.

Do you know if it is at all possible to have a static form with a solution model form as a sub form.
We would greatly appreciate any further advice.

Thanks in advance.

5

a ‘semi’ static form will also work
only you have to set through the solution model the right datasource on that form
and then match up the dataproviders of all the fields to the datasource columns.

6

Brilliant thanks for this,

Would you have any sample code for this purpose I could take a look at?

Regards

7

Hi McCourt,

Zuke:
Would you have any sample code for this purpose I could take a look at?

I have an example. I was planning to put this (listpicker) code out there anyway and it uses exactly the same technique what you want to do (ex. dataset).
Will post that soon.

8

Excellent

I will look forward to it.

Regards

9

Hi all

I have another query,

I have a cloned form onto which i am adding fields at run time using the solution model, and populating those fields with the results of a query, all working fine so far!

var form = solutionModel.getForm("rpt_budget")
var clone = solutionModel.cloneForm("clonedForm", form)
clone.dataSource = $datasource
clone.view = SM_VIEW.LOCKED_TABLE_VIEW;
clone.newField('fc_program_year_basic',SM_DISPLAYTYPE.TEXT_FIELD,10,140,60,10).fontType = 'Tahoma,0,6';
clone.newField('bs_m2',SM_DISPLAYTYPE.TEXT_FIELD,70,140,60,10).fontType = 'Tahoma,0,6';
clone.newField('direct_paint_time',SM_DISPLAYTYPE.TEXT_FIELD,130,140,60,10).fontType = 'Tahoma,0,6';
clone.newField('direct_labour_time',SM_DISPLAYTYPE.TEXT_FIELD,180,140,60,10).fontType = 'Tahoma,0,6';
clone.newField('bs_ins_labour_time',SM_DISPLAYTYPE.TEXT_FIELD,240,140,60,10).fontType = 'Tahoma,0,6';
clone.newField('bs_labour_costs',SM_DISPLAYTYPE.TEXT_FIELD,300,140,60,10).fontType = 'Tahoma,0,6';
clone.newField('bs_insulation_time',SM_DISPLAYTYPE.TEXT_FIELD,360,140,60,10).fontType = 'Tahoma,0,6';
forms["clonedForm"].controller.showPrintPreview(null,null,null)

I need to set the border type of the new fields i am creating and also background colour, could anyone advise on the syntax to achieve this

Regards

10

Hi McCourt,

Why not simply use a stylesheet ?
You set the stylesheet name to the form and the stylesheet class on the field and your font, border and color properties are set.

But if you must you can do it directly on the fields too using the following syntax:

var form = solutionModel.getForm("rpt_budget")
var clone = solutionModel.cloneForm("clonedForm", form),
    field;
clone.dataSource = $datasource
clone.view = SM_VIEW.LOCKED_TABLE_VIEW;

field = clone.newField('fc_program_year_basic',SM_DISPLAYTYPE.TEXT_FIELD,10,140,60,10).fontType = 'Tahoma,0,6';
field.background = "#ff0000"; // red
field.borderType = "EmptyBorder, 0, 0, 0, 0"; // no border at all

// etc.

forms["clonedForm"].controller.showPrintPreview(null,null,null)

Hope this helps.

11

Hi all

I now have my report set up and successfully populated from my custom dataset,

How do i correctly add a trailing sub summary and grouping for the summary to my report via the solution model?

Regards

12

Hi McCourt,

I didn’t realized that you wanted to make a report with summaries. I somehow doubt that this will work when using a custom datasource since the form doesn’t know the actual query, only the result of it.
So you actually want to create report that has dynamic fields on it and (perhaps) summaries. I think you need to use Servoy objects (aggregates/relations/foundsets/etc) for this.
In a way I had the same issue with the ListPicker module that I send you because I couldn’t search in a custom datasource, only in a foundset. So I made it work with only Servoy objects instead.
You can still use your custom query to load the controller (loadRecords(query, [params]) but use all Servoy objects to create the report.

Hope this helps.

13

Yeah we have looked at this,

The issue we have is that the custom query contains SUM and GROUP BY statements which I believe aren’t allowed with loadrecords()

Is this not the case? Is there a workaround?

Regards

14

Hi McCourt,

The SUM value will have to be a Servoy aggregate object and the GROUP BY will be done by the sub-summary form part.
In the end Servoy will send (practically) the same query to the back-end as you do.