Reference Table foreign key

tkrishna29
Giga Guru

Hi,

I'm a newbie to ServiceNow and this might be of basics.

I have a monthly meeting form where we record multiple complaints.

I created 2 tables - MeetingInfo & ComplaintsInfo.

MeetingInfo has a column "Complaints Info" of Type List - Reference to ComplaintsInfo.

I'm able to record multiple complaints for 1 Meeting Info form.

Now, I have 2 challenges:

1. Once I have 100's of complaints, I'm unable to find from the Complaints record to which MeetingInfo it belongs to.

2. While creating a new meeting form or editing a existing form, when I click on the list, It should show only the complaints relavant to that MeetingInfo form.

Appreciate your help.

Thank you.

1 ACCEPTED SOLUTION

Joe McCarty1
ServiceNow Employee
ServiceNow Employee

It sounds like rather than a List type (which is a comma separated list of sys_ids in the referenced table), you need a related list.   How to go about doing that depends on whether it is a many to one relationship between complaints and meetings or many to many.



If it's many to one, just add a reference field on the complaint table back to the meeting it relates to.   Then on the Meeting Info form, Configure->Related Lists and there should now be a Complaints related list automatically available.



If it is many to many, you'll need to create another table to contain the relationship but this will probably help (Create a many-to-many relationship )



If you have to keep the list field, you can probably create a defined relationship, but this will be the most complex of the 3 and require some scripting (Create defined related lists )


View solution in original post

7 REPLIES 7

Joe McCarty1
ServiceNow Employee
ServiceNow Employee

It sounds like rather than a List type (which is a comma separated list of sys_ids in the referenced table), you need a related list.   How to go about doing that depends on whether it is a many to one relationship between complaints and meetings or many to many.



If it's many to one, just add a reference field on the complaint table back to the meeting it relates to.   Then on the Meeting Info form, Configure->Related Lists and there should now be a Complaints related list automatically available.



If it is many to many, you'll need to create another table to contain the relationship but this will probably help (Create a many-to-many relationship )



If you have to keep the list field, you can probably create a defined relationship, but this will be the most complex of the 3 and require some scripting (Create defined related lists )


HI Joe,


My requirement needs a list. I tried your 3rd option but I didn't get how I can map the Meeting Organizer Information record column to AIR Issues table column as there is no relation between those tables.



The meeting organizer adds the AIR issues from the form like below.



find_real_file.png


find_real_file.png



If I define related list, Since I cannot map any column, I'm not able to see any related records in AIR Issues table.



Thanks,


Krishna


Based on your form, I'm not sure why you require a list field as it looks like you are trying to accomplish the same thing with the related list on the same form as the list field.   There aren't really that many advantages to using a GlideList field over a related list (there are actually several factors in favor of the related list).   In that case, option #1 of created a Meeting Organization Information reference field on the AIR Issues table would be a simple way to relate multiple air issues to a meeting.   This option is the closest to actually having a foreign key into another table.



But if for historical reasons you need to, you can create the relationships on both sides.   Navigate to System Definition->Relationships and click new.   We are going to create 2 new relationships



Name the first Air Issues (or whatever), with the applies to table of Meeting Organization Information and a queries from table of Air Issues.



In the query with script field add the following:



(function refineQuery(current, parent) {


  // Add your code here, such as current.addQuery(field, value);


  current.addQuery('sys_id', 'IN', parent.u_air_issues_list_field_name)


})(current, parent);


Navigate to the Meeting Organization Information table, Configure->Related lists and you will see a new option available named 'Air Issues' (or whatever we named it).   It will not have the '->' as it is not a natural relationship from a reference(foreign key) field.   The result is a related list that matches what is in the list field.   But you can't edit this list directly as it is just reflecting what is in the field.   To add or remove items, you would need to edit the field.



On the other side, create another new relationship named 'Meeting Organization Informations' (or whatever) with the applies to table of Air Issues and a queries from table of Meeting Organization Information.



In the query with script field add the following:



(function refineQuery(current, parent) {


  // Add your code here, such as current.addQuery(field, value);


  current.addQuery('u_air_issues_list_field_name', 'CONTAINS', parent.sys_id);


})(current, parent);


Navigate to the Air Issues table, Configure->Related lists and you will see a new option available named 'Meeting Organization Informations' (or whatever we named it).   The result here will be a related list with all the meeting informations that reference this Air Issue.   Again, modifying this list requires modifying the underlying field that it uses in the query.



Hopefully, this helps.


I probably should have noted that you need to replace u_air_issues_list_field_name with the actual name of your Glide List field.