Reference Table foreign key

tkrishna29 · ‎02-03-2017

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.

Joe McCarty1 · ‎02-03-2017

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

varuntayal · ‎02-05-2017

I followed these steps and achieved something you are trying to do-

1. Created Table --> Meeting, AIR Issue

2. Created a Reference Field 'meeting' on table 'AIR Issue' that refers to 'Meeting' table.

3. Created a Relationship that Applies to 'Meeting' and Queries from 'AIR Issue' with script shown in screenshot-

This will result in a Related List shown on Meeting form. Like this-

Now every time you create a new AIR Issue from this form it will automatically link it to the source Meeting record. Like this-

And you will also know which AIR Issue belongs to which Meeting by the reference field.

Hope this helps.

Varun

Joe McCarty1 · ‎02-05-2017

Since you added the reference, there shouldn't be a need to also create a defined relationship to get a related list. There should already be one that looks like 'Air Issues->Meeting'. Option #1 is certainly the simplest, though existing data would need to be migrated/recreated.

tkrishna29 · ‎02-06-2017

Thank you Joe / Varun.

It was very detailed and I was able to convince my customer to use related list.