Archive for the ‘Relationship’ Category

Relationship

Posted: 2017-01-21 in Relationship

cascading deletes are handled automatically within the UI when set up, but are not handled for child (and grandchild, etc.) entities not present in the UI


the RI rules for deletes based on “restrict” (don’t allow a delete where a cascade would delete a child based on a given relationship) are not specified in the DDL, so these also would have to be handled elsewhere. Normally using triggers.


Q:
Is it possible to establish a child parent relationship between sections of two pages?
A:
No. Sections belong to one page.
Q2:
If my project has many apps and there are a number of common pages or sections, may I know the best practice to keep the common pages /sections in sync when a change is made in the base copy of the page /section?
A2:
Everything is data bound, when switching between pages the pages are refreshed unless the “Refresh when activated” attribute is unchecked.
When you have a page visible this has a data view which consists of all of the UI elements in the page.
When Navigating between pages the data view is refreshed so you see the latest data. This is fundamental to the Lianja meta framework for building apps.
Q3:
So If I want to use the same data on a new page, should I save the index field to a memory variable and filter the table on the new page/section to get the same record data or is there a better way to link two sections. Basically I am choosing an address record from one page and starting an invoice with the same record on a new page by saving the data memory variables.
A3:
If the sections are bound to the same data, navigating between pages will do that automatically. There is no need to save anything in any variables.
Q4:
I have both sections one on the first page and the other on the second page pointing to the same database and table. When I go to the second page, it is not point to the same record in the table. Almost like it needs a parent – child relationship.
A4:
parent child relationships are not done across pages which each have their own dataview.
You will need to position on the active record in the activate delegate.
Hint: Look at using

Code:
Lianja.showdocument("page2.section1?action=search& text=" + yourkeyexpression)

in the activate delegate for page2 to keep them in sync.

If you were really doing parent/child with grids, and they were on separated pages, there is a way to do that also.

If you are using views, you would set the WHERE of the child page to the PK value of the parent record, when Activating the page.

If you are using table, you would set the FILTER of the child page in the same way.

If both cases you would use a macro expression, like so:

parentfk = {parent.pk}

In desktop apps, even in JavaScript, resetting the WHERE in the Activate of the child section is all you need to do to refresh the child.

Code:
Lianja.get("pgChild.scChildGrid").where = Lianja.get("pgChild.scChildGrid").where

add the semi-colon for JavaScript.


Q:
In a Parent – child relation, do I need or can I use a subrogate key? How Lianja handles when a key like a Customer ID is change in the parent record? Will that field be changed in all child tables?
A:
When you connect sections together using the relationship builder, Lianja uses heuristics to join the parent and child tables. If the columns it chooses for the relationship do not have an index, it will create the index for you.
If that’s not what you want, you edit the section attributes and change the parent-child expressions.

Note that unlike VFP, Lianja indexes do not need converting to lower or upper case when looking up keys as this is all handled internally as is trimming of the keys to remove trailing white space.

If you want to be able to break the parent-child relationship by editing the parent key, and you want to reassociate all child records to that new key, this can be done in a delegate such as the “change” delegate.
Similarly, adding new records to a child section will fill the parent key automatically.


Q:
If I have a subrogate key that relates the tables, what happen when I create a record in a child table? ia the key value added automatically?
A:
Yes the key will be added automatically as long as the child key expression is a single column.
If it is an expression then you need to handle that inside validation or an oninsert trigger.



Relationship

Posted: 2017-01-21 in Relationship

Q:
As I try to placing field from another table into a grid section I get an error message “You cannot drop this column onto this section”.
Is it possible to construct a
grid from more tables? For example: from related code tables.
A:
If you want to display fields from more than one table use a “local” virtual table.



Relationship

Posted: 2017-01-21 in Relationship

I should also add to this that when a new record is “inserted” into the “suppliers” table, Lianja will automatically use the parent key and assign the primary key into the suppliers (child) to maintain the relationship.


Q:
How can I relate 2 sections on more than 1 field ?

orders!ordernumber+orders!subordernumber to financ!ordernumber+financ!subordernumber

i.o.w. can I change the relation that’s made automaticly or do I have to set relation by code?
A:
If you have composite keys such as in your example then Lianja is unable to insert a value in the child table to maintain the relationship when you add records.
There are a few ways round this.
If you look in the section attributes.
Scroll down to “Related data”
Uncheck the “Automatically relate” attribute.

You can then write your own “ParentDataChanged” delegate to perform your own relationship.
I would suggest using a “local” virtual table for the child as you can then issue a requery() on the section and specify a new SQL WHERE clause e.g.

Code:
// use . not ! to reference the fields in the tables.
// asumes section is bound to the financ table and that is a VT with WHERE !=0 specified initially
Lianja.get("page1.section2").requery("financ.ordernumber+financ.subordernumber='{orders.ordernumber+orders.subordernumber}'")
// notice how I use {...} macros as these are substituted into the SQL command as a constant which is much faster and helps the SQL optimizer

To solve the issue of inserting new records that maintain the parent->child relationship you need to write your own custom delegate for “Before data create“. This will allow you to insert a new record specifying the component parts of the composite key.

I would suggest looking into using Virtual Tables that have a “local” connection string which will handle these types of relationship much better. There is an example in the distribution.


Q:
Hi, how do I set up relations in tables with different field names e.g Supp_id and Supp_code.
I have added

tablename.fieldname

in the parent and child sections in the section attribute settings but it doesnt work.
A:
Try removing the tablename. and just specify the fieldnames.


Look at the grid filter example and the virtual tables example.
section relationships flow from parent to child no matter how the sections get populated.
any section on the same table to the grid should relate as you navigate within the grid.
remember, the relationship manager allows relating upwards in the sections as well as downwards. The order of the sections on the page is not important.