Displaying Records from a Relational Field

In this blog, we'll discuss the use of the Relational Grid option of the Embedded Report widget in Composer that enables you to replace single and multi-relational field controls on custom state and transition forms.  We'll also show how to filter the list of candidates by binding query controls to the grid.  We recommend you use this functionality only for state forms if you are running on a version of SBM earlier than 10.1.4 because of problems when the report results were paged or filtered on transition forms.  These problems were addressed in SBM 10.1.4.

The Problem

When your users view or edit the value of a single or multi-relational field on a form, the values displayed and the choices offered are presented as a summary of the related record, using the Value Display Format (defined for the table) which combines static text and field values into a single string.  While this can be a handy summary of the record, sometimes you'd like to see and search against other information about the related record(s). 

For example, in a state form, you may wish to display something like this:

which specifically calls out the Item Id, Title, Summary, Severity and Item Type of the records referenced by the multi-relational field.  In a transition form, you may want the user to be able to select which records are related to the field like this:

The Relational Grid

The basic idea behind the relational grid is the use of an embedded report for the table referenced by a relational field.  For the state form, we highlight the related fields in the report results.  For transition forms, we let the user select the records to be related, then update the underlying relational field when the form is submitted.

Because the results are displayed using a report defined in Composer, the designer can choose which fields (columns) to display, what query to apply to limit candidate records, and query at runtime values to enable dynamic filtering of candidates at runtime.

Setting up a Relational Grid

The steps for setting up a relational grid are:

  1. Determine the table and associated application associated with the single or mulit-relational field you want to display.
  2. Write a report to define which columns to display and what filtering of candidates (the query) you'd like to use in that application.
  3. Configure an embedded report widget as a relational grid.
  4. If you want to dynamically filter the results, map the filter values into the query on the relational grid.

These steps are discussed further below.

Determine the Target Table and Application

In the Values section of the Options tab of properties of the relational field you want to display, look at the Application and Table properties and note the application and the table:

Write a report in Composer

Now, open the process app containing the application you identified in the previous section and select the target table in the application explorer.  Right click on that table and choose the Create new Report Definition for this table menu item.

This will create a new report in the Report Definition editor.  Drag the fields you would like to display to the columns group, drag fields to the sort order group and create a search filter to limit the records that are displayed. For example:

Note that this report restricts the records that will appear to items in states other than Completed.  The other criterion is a query at runtime parameter to the report.  This allows you to dynamically filter the records displayed by strings matching the title field as we'll see below.

Configure the Embedded Report Widget as a Relational Grid

Now go to the form editor and drop an embedded report widget on your form.  In the property editor for the embedded report, click the Configure Report button on the General tab to bring up the Embedded Report Configuration Dialog.

Pick the Relational grid radio button at the top, specify the relational field you're using and pick the report you just created from the Report drop down list box. If the target table is in another process app and the application has not been referenced, you will need to add the reference to make the report available. If you'd like the system to attempt to map query at runtime parameters for you, check the checkbox at the bottom, but in any case, you'll be able to manually map these parameters.  Click OK to accept the changes and dismiss the dialog.

Now click on the Query tab of the property pane for the widget.  Here you can specify any project constraints for the report (options are current project, base project, top level project and named project), and bind query at runtime parameters.  In the next section, we'll see how you can use query at runtime parameters in the report to give your users the ability to search and filter the records that appear in the UI.

Filter the Results

To leverage the capability of the report to dynamically filter the results it creates, you need to map a field or control value into the widget.  For our example, add EditBox and Button controls to the form.  Name the edit box TitleSearch and the button SearchButton.  On the Query tab of the relational widget properties, map the TitleSearch field to the Title query at runtime parameter for the report like this:

Finally, on the refresh tab, refresh the widget when the search button is pressed, like this:

Now, at runtime, if you enter some text in the TitleSearch textbox, then click on the Search button, the results will be filtered by that text, for example:

Using the Selected Values

In transition forms, you can map the value or values of selected records to populate other controls.  In the following example, a text control displays the currently selected Severity value in the target table:

For single relational fields, because the display text of the text control can contain multiple references, you can construct any string you'd like from the contents of the related fields.

If the relational grid is associated with a multi-relational field, {GridWidgetName.ColumnName} is replaced by a comma separated list of the values from the referenced field. 

Special Considerations for State Forms

The relational grid is especially useful for transition forms, since it gives you a powerful ability to search candidate records in the target table to find the items you'd like to associate with the relational field.   For multi-relational fields on state forms, it is usually not desirable to see the candidate records that are not in the relational field.  This is especially true if the selected related records are scattered among the candidate values.  You'd like to filter the results so that the grid displays only the records referenced by the relational field.

You can achieve this if you have a 'back pointer' from the related fields - that is, a single relational field that points from the child record to the parent record.  Then, you can write the report so that the relational grid displays only the selected records by using the following query:

Note that we've added the ParentItem in (Query at runtime) condition.  Now, in the relational widget properties Query tab, we can add the parent's record ID to restrict the results.

Now the relational grid will display only the records contained in the multi-relational field.

Conclusion

The embedded report's relational grid option provides a powerful and configurable alternative to the use of single and multi-relational fields in custom forms.  It provides a simple mechanism for displaying individual fields on the related table.

If you enjoyed reading this blog entry, please subscribe to my blog by clicking the 'subscribe to updates from author' link below.

Location (Map)

1850 Gateway Drive, San Mateo, CA 94404, USA
Serena Single Sign-On does Negotiate/NTLM (Windows...
How to Change an Item Type and Item Format for the...

Related Posts

Comments 25

 
Lynn Mattie on Tuesday, 21 April 2015 15:56

Tom,

It is not clear from your blog or the documentation I have read on whether the associated multi-relational field automatically updates based on the checked lines in the relational grid within a transition form. If it is not automatic, how can this be achieved?

Thanks,
Lynn

Tom, It is not clear from your blog or the documentation I have read on whether the associated multi-relational field automatically updates based on the checked lines in the relational grid within a transition form. If it is not automatic, how can this be achieved? Thanks, Lynn
Tom Clement on Tuesday, 21 April 2015 16:01

Hi Lynn, yes it does on a transition form (that's what the checkboxes above are used for). However, as I suggested in the blog, there's a problem pre-10.1.4, because the underlying multi-relational field is updated only with the visible selected items. So if you have filtered the results, or the results are paged and a selected value is not on the form, the result will be that only the currently visible selected items will end up selected.

This has been fixed in 10.1.4, and now the widget remembers all the selections, visible or not, including deselections you've made on other pages. And it stores all the selected values back into the multi-relational field when the transition form is submitted.

Hi Lynn, yes it does on a transition form (that's what the checkboxes above are used for). However, as I suggested in the blog, there's a problem pre-10.1.4, because the underlying multi-relational field is updated only with the visible selected items. So if you have filtered the results, or the results are paged and a selected value is not on the form, the result will be that only the currently visible selected items will end up selected. This has been fixed in 10.1.4, and now the widget remembers all the selections, visible or not, including deselections you've made on other pages. And it stores all the selected values back into the multi-relational field when the transition form is submitted.
Nicolas Georjon on Thursday, 30 April 2015 04:26

Hi Tom,
Thanks for all those informations.

In order to do this, the items need to be relationned in both ways ?
If Item A can have n item B, item B has a single relational to Item A. But in order to use this, we need to create a multi relational field in Item A to ItemB ?

Hi Tom, Thanks for all those informations. In order to do this, the items need to be relationned in both ways ? If Item A can have n item B, item B has a single relational to Item A. But in order to use this, we need to create a multi relational field in Item A to ItemB ?
Tom Clement on Thursday, 30 April 2015 09:03

Hi Nicolas,
Good question, since the answer is "yes and no". (I've found that any time that's the answer, it's a good question :)).

For transition forms, you typically want to see more records that the records that are currently selected. This is so that you can pick new records to be added to the multi-relational field. In this case, there's no reason for a 'back pointer' from the related table to the table with the multi-relational field.

The one case where you do probably want to have this parent-child / child-parent relationship is when you are trying to list only the selected records in the widget. You might want to do this on a state form (i.e. a read only display of the related records.) In this case, we are faced with the question of how to write a report against the table containing the related records that shows only those records that are pointed to by the multi-relational field in the current record. You can't write a report that does exactly that, but you can use the back-pointer from the target table to query only those records which have a single-relational field pointing back to the parent. So to write this report, you need that back pointer.

It would absolutely make sense to be able to do it the other way, where we could filter the results to see only those records in the child table which have one of the IDs in the multi-relational field in the parent table, but our reporting mechanisms don't support that (as far as I can tell). This is a good enhancement idea.

I'm not sure I've explained this well enough, so let me know if you'd like any additional clarification and I'll be happy to provide it.

Tom

Hi Nicolas, Good question, since the answer is "yes and no". (I've found that any time that's the answer, it's a good question :)). For transition forms, you typically want to see more records that the records that are currently selected. This is so that you can pick new records to be added to the multi-relational field. In this case, there's no reason for a 'back pointer' from the related table to the table with the multi-relational field. The one case where you do probably want to have this parent-child / child-parent relationship is when you are trying to list only the selected records in the widget. You might want to do this on a state form (i.e. a read only display of the related records.) In this case, we are faced with the question of how to write a report against the table containing the related records that shows only those records that are pointed to by the multi-relational field in the current record. You can't write a report that does exactly that, but you can use the back-pointer from the target table to query only those records which have a single-relational field pointing back to the parent. So to write this report, you need that back pointer. It would absolutely make sense to be able to do it the other way, where we could filter the results to see only those records in the child table which have one of the IDs in the multi-relational field in the parent table, but our reporting mechanisms don't support that (as far as I can tell). This is a good enhancement idea. I'm not sure I've explained this well enough, so let me know if you'd like any additional clarification and I'll be happy to provide it. Tom
Daniel Nolan on Thursday, 30 April 2015 10:43

Thanks for the reply, you're right I was talking of the state form.
I think I have understood, I'll test that asap

Nicolas

Thanks for the reply, you're right I was talking of the state form. I think I have understood, I'll test that asap Nicolas
Tom Clement on Thursday, 30 April 2015 11:38

Hi again Nicolas,
I should add that for the state form usage, since there's no selection or deselection involved, you really don't need to use the relational grid. Probably the simplest thing to do is just write the report in Composer to show the records you're interested in and embed it on the form.
Tom

Hi again Nicolas, I should add that for the state form usage, since there's no selection or deselection involved, you really don't need to use the relational grid. Probably the simplest thing to do is just write the report in Composer to show the records you're interested in and embed it on the form. Tom
Peter Wensel on Tuesday, 05 May 2015 09:04

Hey Tom,

I've followed your example exactly but I've experienced a couple of problems that I'm not sure how to work around.

1) In your "Using The Selected Values" section I've set up a text control like you have but the only value that will return for any column in the relational grid is "null." This is despite the fact that the relational grid is searching/displaying records correctly, and Composer does seem to recognize the column fields on the form (Also, I cannot get my JS to access the columns/fields despite following the proper syntax).

2) Setting up the Widget's refresh option (with a search button/edit box) will not cause a refresh. I had to manually add JS code to trigger the widget's refresh upon click of the search button.

Any reason for the above to occur?

Hey Tom, I've followed your example exactly but I've experienced a couple of problems that I'm not sure how to work around. 1) In your "Using The Selected Values" section I've set up a text control like you have but the only value that will return for any column in the relational grid is "null." This is despite the fact that the relational grid is searching/displaying records correctly, and Composer does seem to recognize the column fields on the form (Also, I cannot get my JS to access the columns/fields despite following the proper syntax). 2) Setting up the Widget's refresh option (with a search button/edit box) will not cause a refresh. I had to manually add JS code to trigger the widget's refresh upon click of the search button. Any reason for the above to occur?
Tom Clement on Tuesday, 05 May 2015 09:24

Hi Peter,
Sorry this is happening to you. From your description, nothing jumps out at me. Any chance you could share your process app with me? If so, I'd be happy to take a look and try to get to the bottom of it. Perhaps you could send me a message with it attached.
Tom

Hi Peter, Sorry this is happening to you. From your description, nothing jumps out at me. Any chance you could share your process app with me? If so, I'd be happy to take a look and try to get to the bottom of it. Perhaps you could send me a message with it attached. Tom
Tom Clement on Wednesday, 06 May 2015 21:37

Could you tell me what version of SBM you are using, Peter?

Could you tell me what version of SBM you are using, Peter?
Tom Clement on Monday, 11 May 2015 17:35

Hi again Peter,
I tested this against I.E. 11, Chrome and Firefox in 10.1.5 and couldn't get this to reproduce. I'm very interesting in understanding what went wrong here. Would you do me a favor and open a support case?
Thanks, Tom

Hi again Peter, I tested this against I.E. 11, Chrome and Firefox in 10.1.5 and couldn't get this to reproduce. I'm very interesting in understanding what went wrong here. Would you do me a favor and open a support case? Thanks, Tom
Mikaela Nilsson on Monday, 11 May 2015 17:18

Tom,

Is there a way to get this working also for transition forms on SBM 10.1.3.1? Anything I can do/add? I like the functionality and have a customer still on 10.1.3.1 and not ready to upgrade right now.

Thanks,
Mikaela

Tom, Is there a way to get this working also for transition forms on SBM 10.1.3.1? Anything I can do/add? I like the functionality and have a customer still on 10.1.3.1 and not ready to upgrade right now. Thanks, Mikaela
Tom Clement on Monday, 11 May 2015 17:31

Hi Mikaela,
The problem using this with transition forms pre-10.1.4 is that it doesn't keep a record of what all the selected records that are not currently visible are, so when you finish the transition, only the visible selected records are used to update the relational field. Of course, this is pretty terrible behavior, since the report may be either filtering (you may have a query at runtime value fed by an unbound field on the form) and excluding records that are actually selected in the field, or paging, which excludes the records not on the current page that should be selected.

When I found out that the widget was behaving this way, I jumped in and rewrote that code to store all the selected values in a hidden field, and pretty much had to rewrite a lot of the relational grid to get it to work with it. I honestly wouldn't recommend trying to get this to work with an earlier version. It might be possible to look at the "EmbeddedRelationalReportWidget()" class in aeplugin_2009_r2.js (which implements the correct functionality) and try to make it work with the 10.1.3.1 version, but it would be unsupported and I think pretty tricky.

I'm so sorry I can't offer you more here.
Tom

Hi Mikaela, The problem using this with transition forms pre-10.1.4 is that it doesn't keep a record of what all the selected records that are not currently visible are, so when you finish the transition, only the visible selected records are used to update the relational field. Of course, this is pretty terrible behavior, since the report may be either filtering (you may have a query at runtime value fed by an unbound field on the form) and excluding records that are actually selected in the field, or paging, which excludes the records not on the current page that should be selected. When I found out that the widget was behaving this way, I jumped in and rewrote that code to store all the selected values in a hidden field, and pretty much had to rewrite a lot of the relational grid to get it to work with it. I honestly wouldn't recommend trying to get this to work with an earlier version. It might be possible to look at the "EmbeddedRelationalReportWidget()" class in aeplugin_2009_r2.js (which implements the correct functionality) and try to make it work with the 10.1.3.1 version, but it would be unsupported and I think pretty tricky. I'm so sorry I can't offer you more here. Tom
Mikaela Nilsson on Wednesday, 13 May 2015 04:44

Ok, thanks for your reply. I think I will recommend the upgrade :)

A new question to see if I get this right or if I can get this the way I want.
I have a Multi-relational field, in this case 2 selections.

On transition form I did the relational grid with a report to search for the FFU no

I would like the 2 selections to be showing. Now it looks like no selections in the relational field.
Once searched and the selections are included you can see it “correct”

Ok, thanks for your reply. I think I will recommend the upgrade :) A new question to see if I get this right or if I can get this the way I want. I have a Multi-relational field, in this case 2 selections. On transition form I did the relational grid with a report to search for the FFU no I would like the 2 selections to be showing. Now it looks like no selections in the relational field. Once searched and the selections are included you can see it “correct”
Mikaela Nilsson on Wednesday, 13 May 2015 04:45

My pic not showing, how can I add my screen shots?

My pic not showing, how can I add my screen shots?
Tom Clement on Wednesday, 13 May 2015 12:51

Not sure Mikaela. I will report this to our site administrator.

Not sure Mikaela. I will report this to our site administrator.
Tom Clement on Wednesday, 13 May 2015 12:50

If I'm understanding this correctly, I think the fundamental issue here relates to the difference between the 'candidate items' and the 'selected items'. We have one list that is sorted however the report is set up to sort the items. The selected items appear wherever they naturally appear in the list as sorted, without regard to their selection status. In a transition form, because you want the user to be able to pick a new item, you want all candidate visible. When the number of candidates is high, this may obscure the current selections (e.g. children).

One thing you might want to do to solve this use case is to add a separate Embedded report ("Composer report definition") option, that displays the currently selected records. Assuming the target table (containing the children) has a single relational field (e.g. Parent Item) pointing back to the current item. You can then create a report with a search filter that looks like "ParentItem in (Query at runtime)". In the properties for this embedded report, on the Query tab, you'd set the ParentItem field to {_RecordID}.

That would display all the children of the current item as of the time the transition form was shown. It would not reflect any changes you were in the process of making in the separate relational grid control.

If I'm understanding this correctly, I think the fundamental issue here relates to the difference between the 'candidate items' and the 'selected items'. We have one list that is sorted however the report is set up to sort the items. The selected items appear wherever they naturally appear in the list as sorted, without regard to their selection status. In a transition form, because you want the user to be able to pick a new item, you want all candidate visible. When the number of candidates is high, this may obscure the current selections (e.g. children). One thing you might want to do to solve this use case is to add a separate Embedded report ("Composer report definition") option, that displays the currently selected records. Assuming the target table (containing the children) has a single relational field (e.g. Parent Item) pointing back to the current item. You can then create a report with a search filter that looks like "ParentItem in (Query at runtime)". In the properties for this embedded report, on the Query tab, you'd set the ParentItem field to {_RecordID}. That would display all the children of the current item as of the time the transition form was shown. It would not reflect any changes you were in the process of making in the separate relational grid control.
Scott Hofmann on Tuesday, 29 September 2015 11:05

Hi Tom,

Do you know the minimum permissions needed for a user to use a Relational Grid? I know it is more than just having the permission to run reports ( in either workflow).

Thanks,
Scott

Hi Tom, Do you know the minimum permissions needed for a user to use a Relational Grid? I know it is more than just having the permission to run reports ( in either workflow). Thanks, Scott
Tom Clement on Tuesday, 29 September 2015 14:28

Hi Scott, it should be the intersection between to the permissions required to view/edit the underlying relational field, and the permissions required to run the report.
Tom

Hi Scott, it should be the intersection between to the permissions required to view/edit the underlying relational field, and the permissions required to run the report. Tom
Lisa Bentley on Tuesday, 22 December 2015 13:33

Hi Tom,

I have walked through the process above and configured the transition form to Multi Relational Grid, however it does not change over to that type of report where the user can select items (checkboxes) that are present in the report. This report has many to one relationships, we have tried several ways to set this table up to support this project, with little success. One of your staff members suggested this method, however we cannot get the Multi relational grid to work properly. Running SBM v10.1.4.1. Any suggestions would be greatly appreciated!

Hi Tom, I have walked through the process above and configured the transition form to Multi Relational Grid, however it does not change over to that type of report where the user can select items (checkboxes) that are present in the report. This report has many to one relationships, we have tried several ways to set this table up to support this project, with little success. One of your staff members suggested this method, however we cannot get the Multi relational grid to work properly. Running SBM v10.1.4.1. Any suggestions would be greatly appreciated!
Tom Clement on Wednesday, 23 December 2015 13:56

Hi Lisa, I'll send you a private message so we can set up some time to go over what you're trying to achieve and to see if I can help.
Tom

Hi Lisa, I'll send you a private message so we can set up some time to go over what you're trying to achieve and to see if I can help. Tom

Recent Tweets