Hi,
I have a tableview in which I want to show some products from a shoplist with some extra value.
I have a table shoplist_product, this is the datasource off the form.
shoplist_product is linked to user_product, since our clients have there own code for certain product, we store them here.
The relation also uses a global variable
and user_product is linked to product, in another database, so we can link the clients data to our internal product codes.
I think I did all this as I should. The data is present in the database. But my tableview does not give any data from either user_product or product. Only from it’s foundset shoplist_product.
application.output(foundset.shoplist_product_id);
application.output(foundset.shoplist_product_to_user_product.user_product_id);
application.output(foundset.shoplist_product_to_user_product.user_product_to_product.name);
64BB8F1F-B6DD-470C-8A94-74F3D3966E73
<null>
TypeError: Cannot read property "name" from (C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js#123)
TypeError: Cannot read property "name" from (C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js#123)
at C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js:123 (onRecordSelection)
What am I doing wrong?
Hi Nick,
I would start by adding:
application.output("User id: [" + globals.svy_sec_lgn_user_id.toString() + "]");
to the debug in the onRecord selection to ensure that the user ID is what you expect it to be and that a record truly does exist for the relation the way it is defined.
Also the errors relating to being unable to read the property “name” can be ignored for now. As there is no record returned from the first relation () it cannot possibly then traverse to the second one and so you are trying to output a property called “name” of a null object.
From the code you have pasted it looks like you are using the Servoy frameworks. If this is the case then you must ensure that the records you are trying to join belong to the same organisation (if there is an organisation_id field on the table) otherwise the framework code will exclude them using the table filter that is set up as standard to ensure that tenants see only their own records. You could add a further line to the debug output to see the organisation id being used:
application.output("Org id: [" + globals.svy_sec_lgn_organization_id.toString() + "]");
Also check if you have any other table filters defined that would prevent the record from being included in the results.
Hope this helps.
Steve
thank you for your reply, I shall check this.
We are using servoy framework, but only partially, and multiple databases, this data is stored in another database, not the framework database. Anything special I need to look out for in that case?
Main one is if you have the organisation_id column in the other databases (or whatever you have specified as the organisation filter field):
[attachment=0]2016-11-17_09-11-11.png[/attachment]
The Servoy framework adds a table filter on this column whenever a user logs in (unless the user has the Application Manager or Developer security level).
Our application uses the frameworks and we keep the frameworks data in a svy_framework database and all our application data is in a separate database. We add the organisation_id column to those tables that contain tenant specific data and Servoy takes care of the rest. The only thing to be aware of with this approach is that there was a bug in version 7 of Servoy whereby any data retrieved by using the Query Builder (getDataSetByQuery) was ignoring the table filters and you had to add the filter manually to your queries (SVY-7300). In version 8 this has been changed.
I think your best option for finding the source of your problem would be to log the actual SQL queries being sent to the DB. If you are using PostgreSQL then you can edit the postgresql.conf file and match the following settings:
log_directory = 'pg_log'
log_filename = 'servoy-debug.log' (or whatever you choose)
log_statement = 'all'
logging_collector = on
If you change any of these parameters you will need to reload the config using pg_ctl, unless you have changed the logging_collector and this will then require a full restart of the PostgreSQL server. Once the queries are being logged then you can ensure that the query that is being issued to load the related foundset is actually what you think it is.
Steve
But we do not have a filter field set. Doesn’t that mean this should not be an issue?
If the field is not set then this will not be a problem. In which case the best way to diagnose the issue is to enable the DB query logging and see the exact query that is being issued to retrieve the data and ensure that it matches what you are expecting.
I changed the settings to the ones you provided, but I’m not getting the proper logs in the file. I get some logs when it’s starting but it does not give me the queries send to the database.
EDIT: o no my mistake, we are using an extrenal DB, was configuring the wrong conf file.
The logs don’t even show the query of the error. If I view the page, I just get these logs:
2016-11-23 11:08:06 CET [2972]: [54-1] user=DBA,db=rbs,app=[unknown],client=10.32.9.82 LOG: execute <unnamed>: select shoplist_id from shoplist where user_id = $1 order by shoplist_id asc limit $2
2016-11-23 11:08:06 CET [2972]: [55-1] user=DBA,db=rbs,app=[unknown],client=10.32.9.82 DETAIL: parameters: $1 = '6B384E12-29BB-4035-9838-580D5E72F938', $2 = '201'
2016-11-23 11:08:08 CET [2972]: [56-1] user=DBA,db=rbs,app=[unknown],client=10.32.9.82 LOG: execute <unnamed>: select shoplist_product_id from shoplist_product where shoplist_id = $1 order by shoplist_product_id asc limit $2
2016-11-23 11:08:08 CET [2972]: [57-1] user=DBA,db=rbs,app=[unknown],client=10.32.9.82 DETAIL: parameters: $1 = '69E8FD93-4FFF-41A4-9912-CA0DA17F7AD0', $2 = '201'
It doesn’t seem to get to the line where the error occurs.
application.output("click");
application.output("id "+ scopes.globals.svy_sec_lgn_user_id);
application.output("shoplist product id " + foundset.product_id);
application.output("user code " + foundset.shoplist_product_to_user_product.code);
application.output("product name " + foundset.shoplist_product_to_user_product.user_product_to_product.name);
click
id 6B384E12-29BB-4035-9838-580D5E72F938
shoplist product id 07DB4258-C450-42B4-8B23-FFCF08B839F0
TypeError: Cannot read property "code" from (C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js#120)
TypeError: Cannot read property "code" from (C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js#120)
at C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js:120 (onRecordSelection)
at C:\Users\Nick.RECYCA\servoy8_Recycom\rec_rbs\forms\frm_shoplist_product_list.js:21 (onShow)
I found the solution.
In one of the tables I forgot to check the “UUID” flag for a column. And this prevented it from working.
Glad you found the solution. I too have forgotten to tick the UUID box on occasion but I’m reasonably sure I got a warning build marker for the relation in the problems tab of Eclipse. Something about both sides of the relation needing to be UUIDs or similar if I remember correctly.
Can’t check this at the moment as I am not in the office but will do it when I get back and let you know.
