I have a products form that has sub products in a portal (also tried in tabs) and then the sub products pull their name from a related table. To display the team names I would typically write an SQL statement that returns the CONCAT’ed name lickity split. Heres the example:
SELECT
ps.id,
CONCAT(n.first," ",n.last) AS name
FROM
products AS p,
product_sub AS ps,
names AS n
WHERE ps.id_product = p.id
AND ps.id_name = n.id
So first I tried to use a calculation field, but that made fetching and scrolling slow. So then I tried writing my own SQL statement as above which worked but the alias column “name” does not show up in labels or dataproviders with the same name.
First I was trying to use loadrecords() but more recently tried the following setup…
var sql_query = 'SELECT......';
var max_returned = 30;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),sql_query,null,max_returned);
for (var i = 1; i <= dataset.getMaxRowIndex(); i++)
{
dataset.rowindex = i;
id = dataset.id;
detail = dataset.name;
}
and what would that do?
are id/detail variables of the current record?
Because then you are just setting those variables to the values over and over again of the dataset you have there.
That doesn’t do anything. Except that the last values in the dataset are stored in the current record.
For now there is no way way to directly select related values in one select and display them into a form other then showing them as html in a html_area.
If you want to display the list as fast as possible you should use a tableview because a tableview does lazy loading of related data. So that the client does see the screen as fast as possible.
Well - I am new to Servoy and was taking the example from the manual so I guess I overlooked the fact that I needed to append object/element syntax to the front of those fields (they are not variables). I was using the example on page 292 of the Dev Ref Guide and assumed that the firstname and lastname elements were columns - like I said I am still learning servoy. Here is the manual code I was refering to :
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.rowIndex = i; //this sets the current working row in the dataset
controller.newRecord();
firstname = dataset.firstname;
lastname = dataset.lastname;
}
It would work to assign my “name” column values to a label for each record and I did try that but no luck. The issue is that I am trying to CONCAT several related fields of the found set but the JS functions take way to long when loading even 30 records in any type of view - not to mention that scrolling is a slow as it seems the calculation is calculated upon veiw request. I am on OSX and so I understand that this is probably some limitation to the latest Java Release for OSX but it makes it disheartening.
I was trying to avoid using a stored calculation as this seems like an FMP type of work around that defeats the purpose of RDBM. The conctactination is a very simple one from the stand point of an SQL query or a scripting language like Lasso or PHP but takes a long time in Servoy with JS.
Don’t get me wrong - I am blown away by what I can do with Servoy and am compeltely sold - will be buying my developer version this week. Just wanted to make sure I was not missing anything. For now the solution may just be to display the related fields I was wanting to CONCAT next to each other and live with it which will be just fine.
it isn’t the concat method.
For example just display the related records you want to concat on that form instead of the concatted value.
It is the query for the related data.
What you could do is make that calculation stored in he primary table. Then it will store that value and use that one. And when something in the related set is changed it will recalc itself.
Great - thanks for the clarification. I appreciate your help.
For example just display the related records you want to concat on that form instead of the concatted value.
Yes I will but I was hoping in this instance to make it more readable by presenting this data together instead of in their own columns as the records are showing in a portal and thus quick reading is easier when it is…
Dallas Cowboys
instead of
Dallas Cowboys
See what I am saying? The concat gives me a single space nice and neat where separate fields separate the two values that need to be read together. Like I said this will still work though. I will probably end up using the stored calc - as it seems like the best solution.
It is the query for the related data.
So does this mean that if a record has a related value on a form that a seperate SQL statement is issued just for that related value? Or does Servoy correctly write SQL statements with joins between related tables? Just curios as this has to do with DB overhead and so forth
setting the related column in the form was an example!
You see if you do that then it is also ‘slow’.
I just wanted to say that it is not the calculation. It is the related query..
Yes at the moment servoy doesn’t do joins on those relations. That is something we see if we can do that later on to improve performance.
Thanks for the many good responses. It is actually a bit of a relief as now I can go on using calculation fields without fear and I know what to avoid in terms of Portals/List View/Table View and related fields.
I wanted to suggest one thing for future feature request while I have your attention. For increased performance and greater advanced user flexibility it would be great if we could assign the results of a custom SQL query to our dataproviders as we see fit. This would allow advanced users to…
Write more efficient, highly complex queries
Grab related values with way less overhead.
Thanks again - I know you guys are working hard to include new features and I appreciate the attention and support you provide to your user base.
what you want won’t really be possible for normal forms
Because how do we update the value if you are typing into that field?
So for this we need something as non updateable forms…
What i am planning, this will be a special bean, where i will work on after 2.1.1 is finished, is that you got a table component (just as a portal) where you can drop in anything you want. Of course it is non updatable But you can show X columns and have actions (onClick) ect.
So for a listview with related data, this bean will maybe something for tableview replacements.
Yes exactly - any field you would fetch with a CONCAT() statement in SQL would never be updatable anyway - it is only to present visual data. So yes, non-updatable forms or even better non-updatable fields would be the trick - I thought labels would do this to present the CONCAT() value. Maybe an additional column type in the dataproviders window for non-updatable columns that pull such values from relationships but that do use JOIN statements to fetch the values.
I think I am going in circles here but I can see how calculated columns should do this - so if it is non-stored you should be able to click “fetch with join” to retrieve “display only” columns and thus speed up queries. Even related pk’s could be used and have actions so you can go to the related form and record. Just getting that type of info to a label in a portal or any other multi record view would be awesome.
Another fast way to display results of a query (until Johan can write thata excellent bean!) is to transform the dataset to HTML and display it. That way it’s flexible, readonly and you can use basic CSS formatting for fonts.
thanks for the tip - sounds perfect for my needs. Can I still assign an action/method to a button in the HTML on a per record basis? That would do the trick.
Johan… If it’s possible to write that bean so that the fields are addressable objects (rather than just part of a larger addressable object ala tables), it would be great if one could set field–as well as row–color by calculation.
i don’t know yet. Because at design time there aren’t any fields in it. It will be completely dynamic.
I am still looking what how it will look and what kind of things would be nice to have.