CA Service Management

 View Only

How to create custom BREL/QREL/LREL attribute 

Jul 21, 2015 08:54 AM

    Introduction

In continuation of CA Service Desk - Create a new table - out-of-box by TMACUL,

I wish to explore one of common questions : how to create many-to-many relation in CA SDM.

We have possibility to use 3 kind of relations :

  • BREL
  • LREL
  • QREL

All of them are declared as:

This field type should be selected only on direction from a CA employee.

@ Web Screen Painter Help

       

Which don't provide any useful information for us.

( except that incorrect definition can crash the entire system )

 

But sometimes business don't give us another choice than use that kind of relations.

 

Before we start, I wish to apologize that some of paragraphs were written carelessly,

so if you are ready to publish list relations you should know schema modification basics.

 

    BREL

Many to many relation, which designed to create each object manually.

Attribute is used to define relation between compound objects,

where one or more attribute could be assigned while creation.

 

Example:

We want to add additional workflow tasks to Change Order Category.

(this example is a part of our environment, where CO WF automatically adds to CO if some conditions are met)

 

Schema (WSP)

    z_ext_wf (TABLE)

          task SREL tsksty;

          sequence INT;

          chgcat SREL chgcat;

          org SREL org; // here is condition, if CO requestor's organization equals this, wf will be inserted;

 

    chgcat (OOTB TABLE)

          z_ext_wfs BREL z_ext_wf;

          BREL Query Information (Advanced tab):

              chgcat DYNAMIC {

                    DOMSET MLIST_DYNAMIC;

              }

 

How it looks:

BREL.png

 

Forms (HTMPL)

To operate new table, 2 form are required:

  • list - to perform search;
  • detail - to display details;

 

I recommend to use OOTB templates, could be found in $NX_ROOT\bopcfg\www\htmpl\web\analyst\.

Also have attached them to this doc.

 

Rename files to list_z_ext_wf.htmpl and detail_z_ext_wf.htmpl;

 

Modify list file:

  1.     Open list_z_ext_wf.htmpl
  2.     Go to line 26 : <PDM_MACRO NAME=sfStart factory="&{factory}">
  3.     Modify line 26 to <PDM_MACRO NAME=sfStart button=false factory="z_ext_wf">
  4.     Add js function to head area:
var enableExtraBtn = true;
cfgExtraButton = new Array("Create New", "zCreateNew()", enableExtraBtn);
function zCreateNew() {
  create_new('z_ext_wf',0,0,0,'PRESET=chgcat:'+ahdframe.argCode,'INITFROM=frm002');
}
      

    5.    Add columns to the list:

<PDM_MACRO name=lsCol hdr="Sequence" attr="sequence" link=yes sort="isscat ASC">
<PDM_MACRO name=lsCol hdr="Task" attr="task">
<PDM_MACRO name=lsCol hdr="Organization" attr="org">
      

 

Modify detail file:

    1.    Open detail_z_ext_wf.htmpl

    2.    Define factory as z_ext_wf in dtlForm macro

    3.    Add decided attrs between dtlStart and dtlEndTable tags

 

Modify detail_chgcat.htmpl:

    1.    Open detail_chgcat.htmpl for decided form group

    2.    Add new tab between startNotebook and endNotebook tabs:

<PDM_MACRO name=TAB title="Additional workflows" height=600 id=ext_wfs src="OP=SEARCH+FACTORY=z_ext_wf+QBE.EQ.chgcat=$args.code">
      

    3.    Add category code to head area (to make zCreateNew function preset work):

var argCode = "$args.code";
      

 

Finalization

Now publish schema.

Apply changes.

Do other stuff.

 

 

    LREL

List Relation is very similiar to BREL but it's used only to create relation between two factories.

Sure it relation could have any kind of options but all of them will be lost if relation will changed.

 

Example:

We make the same example, we want to use extended workflow tasks, but now all of them are predefined and not linked to any Change Category.

 

Schema (WSP):

    z_ext_wf (TABLE)

          task SREL tsksty;

          sequence INT;

          org SREL org; // here is condition, if CO requestor's organization equals this, wf will be inserted;

          chgcats BREL z_ext_wf_lrel;

          BREL Query Information (Advanced tab):

              wf {

                    LREL chgcat;

              }

 

    z_ext_wf_lrel (TABLE)

          wf SREL z_ext_wf;

          chgcat SREL chgcat;

 

    chgcat (OOTB TABLE)

          z_ext_wfs_lrel BREL z_ext_wf_lrel;

          BREL Query Information (Advanced tab):

              chgcat {

                    LREL wf;

              }

 

Forms (HTMPL):

Forms will be the same as BREL, we only need to replace Creation function with Update lrel one. (in list form)

var enableExtraBtn = true;
cfgExtraButton = new Array("Update LREL", "zCreateNew()", enableExtraBtn);
function zCreateNew() {
  update_with_lrel_style('chgcat', ahdframe.argPersistentID, 'z_ext_wf', 'z_ext_wfs_lrel',  'Found WFs', 'Assigned WFs', '');
}
  

Oh, tab on deatil_chgcat should be modified too:

<PDM_MACRO name=TAB title="Additional workflows" height=600 id=ext_wfs src="OP=SEARCH+FACTORY=z_ext_wf+KEEP.where_clause=chgcats.chgcat IN '$args.code'">
  

 

Please notice: If you're using custom factory you need to manually define correct form to perform and show search results as LREL.

Add followed code to list which is used to search data for LREL, this could should be pasted between sfStart and sfEnd tags.

<PDM_IF "$args.KEEP.forLREL" == "1">
document.writeln('<INPUT TYPE=hidden NAME=HTMPL VALUE=update_lrel_z_factory.htmpl>');
</PDM_IF>

also you need to pass that attribute over your update lrel button:

update_with_lrel_style('chgcat', ahdframe.argPersistentID, 'z_ext_wf', 'z_ext_wfs_lrel',  'Found WFs', 'Assigned WFs', '', 'KEEP.forLREL=1');

update_lrel form is attached to this, it could be used without any modifications, simply define correct name, my suggestion is to call it update_lrel_<custom_factory>.htmpl.

 

 

    QREL

I really don't know why this type of attr is exists,

as I found it can be used only in SPEL macros and easily can be replaced via this code:

send_wait(0, top_object(), "call_attr", factory, "sync_fetch", "STATIC", format("HERE IS THE SAME QUERY"), -1, 0);
msg[1]; // array length
 

 

Schema (WSP)

     cnt (TABLE)

          z_QREL QREL cr;

          Active QREL Query Information:

               DYNAMIC {

                    WHERE customer = ?;

                    PARAM_NAMES id;

               }

 

Usage

create spel macro with followed code:

logf(SIGNIFICANT, "%d", z_QREL.length); // will display amount of Requests where current contact is a customer.

 

Also could be used on List forms to display amount of matching records:

<PDM_MACRO name=lsCol hdr="Some attr count" attr="some_obj.childs" display_attr=length>

Statistics
0 Favorited
85 Views
3 Files
0 Shares
3 Downloads
Attachment(s)
zip file
detail.template.zip   770 B   1 version
Uploaded - May 29, 2019
zip file
list.template.zip   880 B   1 version
Uploaded - May 29, 2019
zip file
update_lrel_z_factory.htmpl.zip   2 KB   1 version
Uploaded - May 29, 2019

Tags and Keywords

Comments

Sep 22, 2017 01:26 AM

Thank you, I will have look at this, thank you. I see what we missed may we build it wrong from start.

 

Thanks CDTJ.

Mar 09, 2016 05:05 AM

Great Article!

Mar 09, 2016 03:46 AM

Updated information how to update LREL for custom defined factories, update_lrel template is also attached. Generally it was described by Gutis in previous post.

Dec 02, 2015 12:32 PM

This was one of the most useful posts I have found in this community!

 

We had a hard time figuring out the parameters passed to update_with_lrel_style. In the example above you used the same name to the attribute name and factory (eg. chgcat SREL chgcat; z_ext_wfs_lrel BREL z_ext_wf_lrel; )

 

After reading the comments on the function header, we figured out what to do. In our case, we related organizations to Change and CR categories, and made some customization to only allow users of those organizations to select those categories. This provided us a smaller catalog for each user.

Aug 28, 2015 08:27 AM

it's a good idea but first I need to figure out why chg lists wrong.

for example cr lists correctly without any HTMPL definitions.

edited: it seems like problem needs a deeper analyse because update_lrel_<FACTORY> should exist.

Aug 28, 2015 08:00 AM

Thank you!!! Found that.

update_with_lrel_style('chg', '$args.persistent_id', 'chg', 'zblockers_list',  'Changes1', 'Changes2', '', 'KEEP.ForChild=1+KEEP.exclude_chg_ref_num=1')

 

It may be good point to improve this document. For other objects you may even need to add the following statement in list form

<PDM_IF "$args.KEEP.ForChild" == "1">

document.writeln('<INPUT TYPE=hidden NAME=HTMPL VALUE=update_lrel_chg.htmpl>');

</PDM_IF>

 

You may also add clarification for update_with_lrel_style parameters e.g.

 

        update_with_lrel_style( ParentFactory,    //= "factory of the parent form e.g. if you in organization tab of the change form it will be change"

                             ParentPersId,     //= "persistent_id of the object e.g. ahdframe.argPersistentID "

                             Factory,          //= "factory from which objects will be taken"

                             LrelName,       //= "lrel attribute of the parent object"

                             Label1,           //= "Label of the first column of lrel form"

                             Label2,           //= "bel of the second column of lrel form"

                             ParentHtmpl,      //= ""

                             Extra );          //= "'KEEP.ForChild=1+KEEP.exclude_chg_ref_num=1'"

Aug 28, 2015 07:05 AM

hmm.. list_chg didn't uderstood that you're trying to search for LREL,

add extra attr to your function : ahdframe.update_with_lrel_style("chg", ahdframe.argPersistentID, "chg", "z_chg", "1", "2", "", "KEEP.ForChild=1");

Aug 28, 2015 06:02 AM

I would like to create lrel between change orders, but it seems that update_with_lrel_style function does not work properly. After performing search Instead of two selection boxes where I can select change order once again I am getting standard list_chg

Aug 27, 2015 03:13 PM

Thank You cdtj for this document. For me it is one of the most useful documents in community. I very often use it as the reference when working with lrels and brels, it is very good to have such information in one place.

Aug 06, 2015 11:51 PM

Thanks for the information. Good write up, and every bit helps.

 

Please only implement this if you know what you are doing with customisations. Be honest. :-)

 

Test any customisations that you do with many-to-many relationships carefully. Think not just about the use case that you have in mind, but other uses cases that might arise in the future.

 

For example, suppose that you have a many-to-many relationship set up, and you plan for there to be only ten entries in each table as a maximum plausible limit for the very well defined and bullet-proof use case. All accessed from drop-downs on one data entry form.

 

* Test with ten items in each table.

* Test with 1,000 items in each table.

* Examine for "millisecond" long running queries in the stdlogs.

* Examine for "dotted attribute" errors in the stdlogs. Or any errors.

* Test from your form.

* Test from other parts of the system - Scoreboard nodes, reports, Search screens, Export, pdm tools. etc.

* Make sure that the key lookup columns are indexed in your table.

 

Many-to-many relationships are prone to unexpected blow-outs in performance, due to unexpected circumstances. Many dotted attributes were blocked from being allowed on earlier versions to protect against cartesian joins due to performance reasons.

 

A problem could be when the part of the system that you throught was self-contained gets accessed by a larger part of the system. Then imagining the worst case, there are two large many to many relationships comparing against tables with no indexes and things fall apart rather rapidly - but only in that rare case when a user chooses an Export of a particular Scoreboard stored query node that they wrote.

 

Thanks, Kyle_R.

Jul 30, 2015 03:34 AM

Hi,

I'm sorry but I can't be sure, because I have started my journey from 12.7.

Refering to this page:

Implementation Guide r12.5 - CA

LRELs are already implemented in 12.5, so this should work properly.

Regards,

cdtj

Jul 29, 2015 10:58 AM

Thanks cdtj!

 

Please let me know if this will work on CA SDM r12.5

 

Regards,

Ranjani

Jul 21, 2015 11:22 AM

Thanks for sharing this cdtj!!!

 

Very helpful.

Jul 21, 2015 09:11 AM

HI cdtj,

 

  Thank you for sharing this information, i updated CA Service Desk - Create a new table - out-of-box including link for this doc.

 

Regards

Tiago Macul

Related Entries and Links

No Related Resource entered.