I am a noobie with Servoy and have successfully created a form that makes links to tables that are 3 and 4 deep. Changes made to the child tables will change values in the parent tables. However in my solution I do not believe I am using the full potential of the Servoy software.
Most of the samples that Servoy presents seem to be one dimensional. In other words, all links are made one table away. Table A to Table B; Table A to Table C; Table A to Table D. When in many cases for me tables are related: Table A to Table B; Table B to Table C; Table C to Table D.
The closest example I have found is the Tutorial for Advanced ValueLists ( Ref: http://www.servoy.com/docs/servoy_4/tut … elists.htm ). And it works just fine but I must be missing something very fundamental. The programmed ValueLists does a great job of presenting data two or more tables away but there does not seem to be any built-in means to change the values of its parent table. If you link A to B; then B to C; Changing a value in C does not update the corresponding values in B.
I can do it and what follows is how I did it. And please tell me if I am not utilizing Servoy correctly.
[attachment=0]servoy_link_3.jpg[/attachment]
(Please note that there are more fields on the working form than are required. They are just shown for easier understanding of the table relationships and such.)
The required puzzle pieces:
1 Relation: Orders.OrderID = Order_Details.OrderID
2 Global variables: varProdDesc → The dropdown that holds all the product names; txtProdID → text field that forces a page refresh
1 ValueList: vlProdDesc → Blank value list that calls the (PopOrderDetail) Method below.
2 Methods: PopOrderDetail → Populates the dropdown and identifies the current value for the dropdown; ProdNameChange → changes the values in the parent table from changes made in the child table
function PopOrderDetail()
{
// VALUE LIST -> two parts: Populates the dropdown and sets the current product name
// attach to the form.onRecordSelection event
// +++ get all values to populate the dropdown for: Products Description
var maxRows = 10000;
var sqlProducts = 'SELECT DISTINCT productName, productID ' +
' FROM products ' +
' ORDER BY productName '
dsProducts = databaseManager.getDataSetByQuery(controller.getServerName(), sqlProducts, null, maxRows)
// populate form if values exist
if(dsProducts.getMaxRowIndex() > 0)
{
application.setValueListItems('vlProdDesc', dsProducts.getColumnAsArray(1))
}
// =========================================
// +++ find single value to id the current dropdown value
var sqlName = 'SELECT productName ' +
' FROM products ' +
' WHERE productID = ' + forms.frmMain.orders_to_order_details.productid
var dsName = databaseManager.getDataSetByQuery(controller.getServerName(), sqlName, null, 1)
// populate form if values exist (note the test is not for the single value)
if(dsName.getMaxRowIndex() > 0)
{
globals.varProdDesc = dsName.getValue(1, 1)
}
}
function ProdNameChg()
{
// +++ changes the parent table when a child value is changed
// attach to the onDataChange event of the dropdown
//databaseManager.setAutoSave(false)
var sqlProdID = 'SELECT productID, unitprice ' +
' FROM products ' +
' WHERE productName = ' + globals.varProdDesc
var dsProdID = databaseManager.getDataSetByQuery(controller.getServerName(), sqlProdID, null, 1)
forms.frmMain.orders_to_order_details.productid = dsProdID.getValue(1,1)
forms.frmMain.orders_to_order_details.unitprice = dsProdID.getValue(1,2)
globals.txtProdID = dsProdID.getValue(1,1)
}
Now the questions:
-
Is there a non-progamatic way of doing this? Updating multiple fields of the parent table when a child value changes. I just want to make sure I am not missing some fundemental tools that I should be using with Servoy.
-
Why do I have to populate an extra textbox (txtProdID) to force a refresh of the form? If I do not populate the txtProdID textbox then the order_details.productID and order_details.unitPrice textboxes do not update.
-
Is there a way to set the visible property for objects from the Properties window instead of progamatically?
