Relation maintenance

Using defined relations Dynamic AI offers the possibility to integrate and relate information across several defined Dynamic AI database connections.

Relations can be defined e.g. between a customer table in an Oracle Database located on a UNIX database server and a file-based Microsoft Access Query with order data located on a shared local area network drive.

Dynamic AI doesn’t relate or merge the data as part of background processing, but offers the possibility to combine information as part of the front-end interface presented to end-users.

Relations are available to end-users that have access to Reports defined on data-sources that are part of centrally defined relations. Relations can be used in different ways:

·         A related link on a form based on the presented record

·         A sub-form /sub-list presentation on a form

·         An automatic related drill down to another report to either a form or a list

For example would it be possible to show related sales for a specific customer. Based on a relation between a customer table/view and a sales transaction table/view, the form presenting customer information could include either a link to a sales report automatically filtered on that specific customer or the actual report showing the filtered sales directly in the form.

Relations as part of sub-form/sub-list presentations

During customization of Form layout it is possible to insert sub-forms or sub-listings on a form presentation.

When specifying a Report to be inserted on the Form, relations will be available at the end of the Report drop-down box specifying the Report name e.g. “Realized sales 2007” and the relation condition e.g. “(using EmployeeID)”:

In the specific example there is two reports.

One report holds a scrollable list of employees working in the Sales department:

And a sales report showing realized sales for the year 2007:

A normal drilldown from the list of employees would look like:

If you want to combine the information in the reports you will have to set up a relation between the two data sources that the reports are based on. Please see “Relationship definition” to learn more about how to set up the relation.

When the relation has been set up every report based on these data sources can potentially be set up to interact with reports from the other data source.

That means that you for example can include the sales report on the form presentation of an employee as described above. The form could now look like:

And when the user scrolls to the next record the inserted sub list ill automatically change into a specification of that particular employee:

Another way of relating information is to include a link on the form. And the form could look like this:

Where the link will open the report with sales filtered for that specific EmployeeID.

Drill-down to related Report

Another way to force the use of a relation is by specifying a Report to use a certain relationship and Report when presenting drill-downed specifications.

This is set on the General tab in the Design module:

A drill down on the Employee list will now have the following result:

Advantages of using Dynamic AI relations

In Dynamic AI relations are defined centrally and are available across the system during customization and use.

Performance gains during use of Dynamic AI

In some cases when Dynamic AI use a relation as condition for a drill-down, the actual work that the background data-engine must do in order to find the related records will be much less than a generic drill-down to form operation.

Development and maintenance advantages of using relations

If a number of reports and listings are created on the same data-source, it will in many cases be the most efficient solution to define a relation between the data-source and the data-source itself e.g.:

In this way the developer can define list and report Reports to drill down to the same central Report displaying the form presentation of the underlying and related record information. In most cases a unique key ID will be necessary for this to be a good solution, e.g. a order number, an auto-generated unique record ID, a unique customer number etc.

Relationship definition

Relationships can be defined by Dynamic AI Users having the user right:

Relation maintenance

Users with relation maintenance permissions will have the menu option Dynamic Relation available in the Databases menu under “New”.

And If any relations are defined the can be browsed and edited via the Explore Databases/Dynamic Relations.

If you select to create a new Relation by clicking “Dynamic Relation” under “New” in the Databases menu the following screen will appear:

From where new relations can be added or existing relations can be modified or deleted.

A relation definition includes the following fields:

In addition the following options are set for automatic relation behavior:

·         No automatic relate

·         Auto Fieldname match. Include above fields (defined fields are in addition to automatically discovered fields)

·         Auto Fieldname match. Exclude above fields (defined fields are excluded from the automatically discovered fields)

Adding a relation

Click on “Dynamic relation” in the Databases menu to add a new relation and input the relevant information in the blank form.

Start by selecting the Connection A and B, in order for Dynamic AI to resolve available tables, views, synonyms, Dynamic Views and Dynamic JOINS for that connection.

Specify the table / view participating in the relationship definition.

Dynamic AI automatically lookup available fields for the selected data-sources and build drop-down boxes of valid selections.

Having completed the relationship (must be defined in pairs i.e. if 2 fields are specified for “Data Source A”, 2 fields must be specified for “Data Source B” in order for the relationship to function). Click the Save button store the new relation definition and make it available to end-users and developers.

Autorelate

Automatic relation is a feature that automatically enables relationships between tables with matching field names. When enabled, a relationship between Table A and Table B will automatically include fields from Table A having the same name as fields from Table B.

Select the appropriate Autorelate option:

No Autorelate

The chosen fields are the only relationships used between the two data sources

Auto Fieldname match. Include above fields.

Automatic relation enabled, the manually entered fields are also included in the relationship

Auto Fieldname match. Exclude above fields.

Automatic relation enabled, the manually entered fields are not included in the relation. Meaning that if the tables have the fields CustID and ZIPcode in common and ZipCode is listed in the manually selected fields, only CustID is included in the relation.

Modifying or deleting a relation

If a relation needs modification select “Edit relation” in the drop down showing when clicking next to the name of the relation in the Databases menu.

The definition will then be available for modification and holding buttons for Save, Save as, Delete and Cancel

When the modification is complete, click the “Save” button to update and save the modifications.

If a relation should be removed click the “Delete” to remove the relation.

New relations can be added based on existing relations. Change to the desired relation definition based on an existing definition and click the “Save As…” button to add a new modified copy of the relation definition.

To abort the modification of the relation click “Cancel”.

Impact on Report definitions when modifying or deleting relations

Be aware that Reports using a relation will not function if the relationship that the Report drill-down or sub-form presentations are based on is removed. Also be aware that changes to existing relations may have impact on existing Reports.