Custom Reporting -- Creating Form Relationships
From IHRIS Wiki
This is primarily intended for a developer or administrator of the system who has a good idea of how all the forms are related. The possible connections between the different forms are already mapped out in the system. A relationship is the description of a path between the various forms in the system.
A form relationship is used to build the SQL queries used in a Custom Report. Although you do not need to know any SQL to create a report, a passing familiarity with relational databases will help.
You may wish to create a form relationship in iHRIS Manage that describes all current employees, their salaries, and their supervisors. Here is an outline of how to define this relationship:
- Start with the primary form 'person_position' and limit to those positions which the 'being_date' field is not null and the 'end_date' field is null.
- Join to the primary form the 'person' form where the 'person' form is a parent of the 'person_position' form
- Join to the primary form the 'salary' form where 'salary' is a child of 'person_position' and where the 'start_date' field for 'salary' is maximal
- Join to the primary form the 'position' form where the field 'position' of the primary 'person_position' form maps to that position. Call this the 'employee_position' form.
- Join to the 'position' form the 'position' form where the 'supervisor' field of the 'position' form maps to that position. Call this joined form the 'supervisor_position' to distinguish it from the 'employee_position' form.
- Join to the 'supervisor_position' the 'person_position' form whose 'position' field is the value of 'supervisor_position' form. Call this the 'supervisor_person_position'
- Join to the 'supervisor_person_position' form the 'person' form which is a parent of the form. Call is the 'supervisor'
- A relationship is some times referred to as a "form relationship" or a "report relationship"
- The "primary_form" is the starting point in describing the form in your relationship.
- A form can be referenced several times in a relationship. In the example above, the person_position form was referenced twice. Once we gave it the "report form name" 'person_position' and once we gave it the "report form name" 'supervisor_person_position'. Similarly, the person form was referenced twice and given the two distinct "report form names" of 'person' and 'supervisor'
- Some of the terminology, such as 'join,' is borrowed from SQL.
A form relationship can be created following the "Configure System" and then the "Edit Form Relationship" linka. The first steps are to:
- select a "Display Name" for the relationship, the name of the relationship for the end user.
- select a "Short Name" for the report, which is a way to reference the relationship internally and can only contain alpha-numeric characters and some limited punctuation such as _ and -.
- A description of the relationship.
- The you must do one of the following:
- Choose the "primary_form" for the relationship
- Copy the details existing form relationship to modify
Joining a Form
Once a form, formA, is in a relationship, you can join to it any of its related forms. You must ensure, by adding a limit, that at most one instance of the formB is joined to another an instance of formA. There are four possible ways to join:
- (An instance of) formA is a parent of (an instance of) formB.
- formA may have several child instances of formB. For example, a 'person' form may have may child 'salary' forms.
- (An instance of) formA is a child of (an instance of) formB.
- Note that a form, if it has a parent form, is unique, so no limits are needed when joining in this manner.
- (An instance of) formA contains a mapped field whose value maps to (an instance of) formB.
- (An instance of) formB contains a mapped field whose value maps to (an instance of) formA.
Limiting a Form
The primary form and any joined forms in a relationship may be limited by using the limiting forms structure. The form relationship provides a nice interface to construct form limits.
Adding in a SQL Function
In addition to linking in forms to a report, we can define SQL functions that can be run on the data in the forms. To reference the field named $fieldName in the form named $reportFormName in the relationship you use:
CONACT (SUBSTR(`supervisor+name`,1,1), '. ', SUBSTR(`supervisor+surname`,1,1) , '.')
would return the initials of the supervisor.
To define a sql function, you need to define:
- A (short) name use to reference the function. For example, 'supervisor_initials.'
- A description of the function. For example, "The Initials of the Supervisor."
- The form field that the result of the SQL function should take values in. For example, "STRING_LINE"