Hi Sam,
it is possible to link the same table to itself, you just need to add in two business rules to keep the records in sync (two records need to exist for each entry to enable users to see the many to many link from both records)
Here is an example of a Change M2M
- Navigate to sys_m2m.list and create a new table Many to Many table, referencing Change <> Change but do not save straight away. On creation
- From Table: Change Request
- To Table: Change Request
- Many to Many: creates u_m2m_change_reque_change_reque but rename
- M2M from field: change_request
- M2M from label: Change Requests
- M2M to field: change_request
- M2M to Label: Change Requests
- Then amend the Many to Many table name to less characters and more meaningful and easy to remember e.g.
- Many to Many: u_m2m_change_change
- M2M from field: change
- M2M from label: Change
- M2M to field: linked_change
- M2M to Label: Linked Change
Then Save Record / create new m2m table
- Navigate to new table u_m2m_change_change.do
- Both fields are named 'Change Request', so right hand click on Label and determine which is u_change and which is u_linked_change and amend labels accordingly
- Create a Business rule to insert a corresponding record e.g. When a Change is added to the Related List e.g. CHG0000020 is added to CHG0000004 a record is created Linked Change = CHG0000004 and Change = CHG0000020, this rule creates a corresponding record where Linked Change = CHG0000020 and Change = CHG0000004, to the Link can be viewed from either record
- Example:
- Name: Sync Linked Changes on Insert
- Active: true
- Advanced: true
- When to run:
- When: after
- Order: 100
- Insert: true
- Update: false
- Delete: false
- Query: false
- Actions:-
- None
- Advanced:
- Condition: none
- Script:
(function executeRule(current, previous /*null when async*/) {
InsertLinkedChg();
function InsertLinkedChg() {
//Check for an opposite corresponding record
var gr = new GlideRecord('u_m2m_change_change');
//Where Linked Change = Change
gr.addQuery('u_linked_change', current.u_change);
//Where Change Linked Change
gr.addQuery('u_change', current.u_linked_change);
gr.query();
if(!gr.next()){
//gs.log("Change is "+ current.u_change.getDisplayValue());
//gs.log("Linked is "+ current.u_linked_change.getDisplayValue());
//If no record is found, then create a new record
gr.initialize();
gr.u_linked_change = current.u_change;
gr.u_change = current.u_linked_change;
gr.insert();
}
}
})(current, previous);
- Create a Business rule to delete the corresponding record
- Example
- Name: Sync Linked Changes on Delete
- Active: true
- Advanced: true
- When to run:
- When: before
- Order: 100
- Insert: false
- Update: false
- Delete: true
- Query: false
- Actions:-
- None
- Advanced:
- Condition: none
- Script:
(function executeRule(current, previous /*null when async*/) {
DeleteLinkedChg();
//Delete the corresponding record for the 'Linked Change' where the link to the 'Change' has been removed or vice versa
function DeleteLinkedChg() {
//Check for the corresponding record which should already exist in the m2m table
var gr = new GlideRecord('u_m2m_change_change');
//Existing record Change = Linked Change Number e.g.CHG4
gr.addQuery('u_change', current.u_linked_change);
//Existing record Linked Change = Change Number e.g.CH20
gr.addQuery('u_linked_change', current.change);
gr.query();
if(gr.next()){
//Record found, REMOVE corresponding record from the M2M table
//gs.log("deletion for Linked " + linked + " & Change " + chg);
gr.deleteRecord();
//Record not found, do nothing
} else {
}
}
})(current, previous);
- On the Change form, add the related list 'Linked" and Configure List Layout adding the fields required
- Configure the List Control and add
- Label 'Linked Changes' and changed 'Omit new button' from false to true
Thanks GG, I have it working properly now. I noticed that in the first go around, I did have the ACL on the field and not the table. However, I tried again on the field and it still wasn't working. This morning I made new ACLs without any field specification:
Where it says "None" in the drop down, I had an asterisk. Not sure if that was contributing to my issue since I assumed that meant it would be for all fields within the table. I left it as none for this time and it worked.
In any case, thanks very much for helping with this, much appreciated?
