Calculations using Solution Model?

Classic Servoy
1

Hi…

I’m trying to figure out how to dynamically create an un-stored calculation using the new solution model in v4.1. I’m dynamically creating a form based on a query (as described here and want to add a couple of calculated fields but I’m at a loss as to how I can create them.

Here’s sample code:

var sql = "...big long complex sql query here...";
var dataset = databaseManager.getDataSetByQuery('db', sql, null, -1);
var uri = dataset.createDataSource(
      'mydata', [DM_COLUMNTYPE.TEXT, DM_COLUMNTYPE.INTEGER, DM_COLUMNTYPE.INTEGER,
                 DM_COLUMNTYPE.INTEGER, DM_COLUMNTYPE.INTEGER, DM_COLUMNTYPE.INTEGER, DM_COLUMNTYPE.INTEGER]);

// code to create calculated fields goes here???

// create the form...
var fname = "MyForm";
var jsform = solutionModel.newForm(fname, uri, null, true, 300, 700);
jsform.view = SM_VIEW.LOCKED_TABLE_VIEW;

// add the fields...
var field = form.newTextField("column_1", 1, 1, width, 20);
field.text = "Column Label";
field.editable = false;
// and so on for the other fields...

// finally show the form...
forms[fname].controller.show();

Can someone fill in the blanks?

Thanks,
Corey

2

You can do two things…

  1. add columns in your query to create the dataset, Ex: “SELECT id, name, 0 as ‘my_col’ FROM…”

  2. call dataset.addColumn() after you have created the dataset from the query.

You need to do either of these before you call “createDataSource” on the dataset, and the “createDataSource” call should reflect the columns and types you have added to the dataset.

g.

3

Thanks. I had seen the addColumn() method but I’m not sure how I create and pass in a calculation to that method.

4

You have to maintain it with form events – onDataChange or whatever.

You are really just creating a placeholder to set values, you can’t actually define calculations.

The solutionModel is for the creation and manipulation of forms, not dataproviders. There is not way to programmatically alter dataproviders at this time, that I know of.

greg.

5

Greg,

Thanks for your help. Unfortunately I don’t see an appropriate form event. I need something that fires when every record is populated. The onDataChange event doesn’t seem to do that unless I’m doing something wrong. I just added a simple global method to my field but it’s not being called.

Really what I’d like to accomplish is setting the background colour on the certain cells in the table based on the values in that row. Maybe I’m trying to accomplish this the wrong way. Any ideas?

Thanks again,
Corey

6

Set the rowBGColorCalculation for the form to a global. One of the arguments passed to the global method will be the record object begin displayed, and you can make decisions based on that.

greg.

7

Thanks. I’ve tried that but for some reason my rowBGColorCalculation function doesn’t seem to be called.

Here’s the code:

    var sql = "...big long complex sql query here...";
    var dataset = databaseManager.getDataSetByQuery('db', sql, null, -1);
    var uri = dataset.createDataSource(
          'mydata', [DM_COLUMNTYPE.TEXT, DM_COLUMNTYPE.INTEGER, DM_COLUMNTYPE.INTEGER,
                     DM_COLUMNTYPE.INTEGER, DM_COLUMNTYPE.INTEGER, DM_COLUMNTYPE.INTEGER, DM_COLUMNTYPE.INTEGER]);

    // create the form...
    var fname = "MyForm";
    var jsform = solutionModel.newForm(fname, uri, null, true, 300, 700);
    jsform.view = SM_VIEW.LOCKED_TABLE_VIEW;
    jsform.rowBGColorCalculation = globals.calcMediaStatus;

    // add the fields...
    var field = form.newTextField("column_1", 1, 1, width, 20);
    field.text = "Column Label";
    field.editable = false;
    // and so on for the other fields...

    // finally show the form...
    forms[fname].controller.show();

And then my (very simple) global method:

function calcMediaStatus()
{
  application.output("rowBgColorCalc Called!");
  for (var i = 0; arguments.length; i++)
  {
    application.output("arg[" + i + "]: " + arguments[i]);  
  }
}

Any ideas what I’m doing wrong? I’m not seeing anything on the console nor am I getting any errors.

Thanks again,
Corey

8

Corey,

rowBGColorCalculation is a string property

jsform.rowBGColorCalculation = 'globals.calcMediaStatus';

Rob

9

Thanks a lot Rob that fixed it. Well kind of… :? I got my function to change the background colour of the cells appropriately and it all works great in the Smart Client but when I test in the Web Client the colours aren’t changing at all! Since we’re deploying the Web Client only I need to get this working. Any ideas what I might be doing wrong here? I’m running the exact same code in both clients.

It would be nice if a future version of Servoy could generate an error message when we assign the wrong type of variable.

Also, has any thought been given to having a more flexible method that would allow us to modify more properties of the field so that we could change the style etc. of the field/cell rather than just changing the background colour? This would allow for some nice visual indicators to be used.

Thanks for your help.
Corey

10

servoy/js engine will generate a error/warning if you set the wrong type
Problem is that in your case the js engine could probably convert your function object to a string and set that. (because almost everything can be converted to a string representation)
And we cant test the string at that time you set it because maybe you will create that real method after that.