Hello Community,
A few days back I got an interesting business requirement. I did lots of research, though I didn't find any concrete solution for the same. Today, I'm going to share the solution for the same so that it can help you.
Use case: Generate and download a custom CSV file from multiple tables which have relationships between them.
In this article, I'm going to give you a simple solution to Generate and download a custom CSV file from multiple tables which have relationships between them. It is simple to implement because we are going to implement it with low-code and also we are not using any third-party tool.
There are 2 different ways to generate and download customize CSV files on the basis of the following 2 conditions:
- There is a relationship between tables.
- There is no relationship between tables. --> Find Solution Here.
Relationship between tables means what?
So, During college days we must have learned the concept of primary key and foreign key.
For example., consider 3 tables: 1. Incident(incident) 2. User(sys_user) and 3. Location(cmn_location)
We know that the Assigned to and Location fields on the incident form are reference fields that are behaving as a foreign key in the Incident table. Please refer below image for a better understanding:
The solution to Download the CSV file of Single Table:
We can easily export a single table by using the below link format which we are going to use in upcoming steps to embed it in the widget:
- https://instanceName.service-now.com/table_list.do?FILE_TYPE&sysparm_view=view
- Example: https://myinstance.service-now.com/incident_list.do?EXCEL&sysparm_view=portal
- You can embed this link in the widget with below HTML code.
<div class="form-group" style="text-align:center;"><a href="incident_list.do?EXCEL&sysparm_view=portal&sysparm_query=active%3Dtrue&sysparm_first_row=1">Download My CSV</a></div>
- FILE_TYPEs provided by ServiceNow are CSV, XML, EXCEL, and PDF.
- Note: For non-admins, if you don’t have access to the table you will not be able to download the file.
* * *
Steps to download a single CSV of multiple tables:
- Configure a Database View.
- Configure a UI view and List view for the database view which we created in step A.
- Configure and add a Widget on the Page.
Step A] Configure a Database View
Definition: A Database View is essentially a pseudo-table representing a join between several contributing tables. The rows consist of combinations of contributing source records that share values according to pre-indicated mappings. You can learn more about Database View on ServiceNow Product Documentation.
- Login into your ServiceNow instance.
- Search System Definition > Database Views in an application navigator. Then Click New.
- Define the Name(mandatory), Label, Plural, and Description. Label and Plural fields will be used to identify the database view within the platform.
- Select 'Save' from Additional actions to stay on the same page.
- Then we need to configure the multiple View tables by clicking on the New button in the right corner of the View Tables list.
- On the view table form, Select the table which you want to join with another and fill in the value of the Prefix, Order, and Where clause, and submit. Note:
- Here Prefix field will define a prefix for field names in the database view table i.e. pseudo-table. For e.g. Assigned To("assigned_to") field on the incident table will become "inc_assigned_to". The prefix will help us to differentiate between the same field's name from the multiple tables.
- Where clause is a filter that defines how the tables should be joined. Here In our example, Location is filtered as per the location field on the incident table. And User is filtered as per the assigned to field on the incident table.
- And repeat step 5 for multiple tables. The final Database view will look like this:
- You can see the pseudo-table(u_incident_user_location_database_view) by clicking on the related list 'Try it' on the Database View form.
- Done!
Step B] Configure a UI view and List view for the database view which we created in step A
On the pseudo-table, you must have noticed that all the columns are there from selected tables. If you want selective columns in your CSV file, you can configure a UI view and a ListView on the list of pseudo-table.
- Let's configure System UI > Views (sys_ui_view_list) in the filter navigator and click New. Provide a name and save the record. Now configure the List view.
- Click on the New button in the related list of Lists OR Search System UI > Lists in the filter navigator and click on New.
- Select the table i.e. pseudo-table: 'u_incident_user_location_database_view' which we created earlier. The view field will get auto-populate. Save the form.
- Now let's configure List Elements by clicking on New. List elements are basically columns that you want to see on the list view. Configure the Element and Position fields. Element is the name of the column which you want to add. You have to use prefixes of the table that we have configured while creating the Database view. For e.g. Incident number field from an incident table will be 'inc_number', User Name field from User Table will be 'usrr_user_name'. Look at the below screenshot.
Now test the view on the pseudo-table.
- Done!
Step C] Configure and add a Widget on the Page
- Search Service Portal -> Widgets in the filter navigator and click New. We are going to use the format of the link mentioned above. Provide Name, ID, and Body HTML template as given below:
<div class="form-group" style="text-align:center;">
<a href="u_incident_user_location_database_view.do?CSV&sysparm_view=DemoView2">Download My CSV</a>
</div>
- Add this widget to your page and Test it. When the User clicks on that link file downloading will get started.
- The downloaded file will look like this…
- You can Notice that we have successfully downloaded the CSV which has data from multiple tables.
- Congratulations you did it!
******************************************************************************
Thanks for visiting my article. If the article helped you in any way, please hit the like button/mark it helpful. So it will help others to get the correct solution quickly.
See you in the next Article,
Prasad.
